Multi-TIFF generation slows down server

We have reports that render as tifs. The reports are called in batch
rendering any where from a few times to a few hundred times. The report was
on server A and got its data from server A and server B. After repeated
complaints and slowness, we moved the reports to server C. Server C is now
slow. The slowness starts when reports are rendered and then continues on
long past the end of the batch, in fact it requires a reboot to fix. This
behaviour seems to follow the report. Are there any known issues causing a
memory leak? Servetr performance seems low in terms of disk utilization and
processor utilization.On Mar 27, 5:45 pm, Jeff Ericson
<JeffEric...@.discussions.microsoft.com> wrote:
> We have reports that render as tifs. The reports are called in batch
> rendering any where from a few times to a few hundred times. The report was
> on server A and got its data from server A and server B. After repeated
> complaints and slowness, we moved the reports to server C. Server C is now
> slow. The slowness starts when reports are rendered and then continues on
> long past the end of the batch, in fact it requires a reboot to fix. This
> behaviour seems to follow the report. Are there any known issues causing a
> memory leak? Servetr performance seems low in terms of disk utilization and
> processor utilization.
Known issue fixes for SP 2 are listed here: http://support.microsoft.com/kb/921896.
It seems that the majority of the recent issues w/performance are w/
Analysis Services (though there are a couple others mentioned). I
would think that accessing outside applications in bulk would have
this type of affect in general though. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||All rendering is done in RAM. I am not familiar either with the TIFF
rendering but PDF is much slower than HTML so it doesn't surprise me that
TIFF is. Have you tried using PDF instead (or even better, html). PDF
rendering has improved in 2005 versus that in 2000 product.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:B7379ECE-65A1-41C2-8C85-DB7F8DEDCCA7@.microsoft.com...
> We have reports that render as tifs. The reports are called in batch
> rendering any where from a few times to a few hundred times. The report
> was
> on server A and got its data from server A and server B. After repeated
> complaints and slowness, we moved the reports to server C. Server C is
> now
> slow. The slowness starts when reports are rendered and then continues on
> long past the end of the batch, in fact it requires a reboot to fix. This
> behaviour seems to follow the report. Are there any known issues causing
> a
> memory leak? Servetr performance seems low in terms of disk utilization
> and
> processor utilization.

Multi-Step Transaction, fails but reports success

I have for example the following SQL:
BEGIN TRAN
Delete from users where userid = 102
Update mainSettings set userCnt = UserCnt -1
COMMIT TRAN
If for example the "update" statement fails, but the "delete" statement
is successful - the overall transaction reports no error, and thus, my
appliction thinks everything was a success.
Any ideas?
VB 6.0
MDAC ADO 2.7
JasonAnswered in microsoft.public.sqlserver.programming
Please do not multi-post.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||You have to check for @.@.error after every statement that insert / delete or
update
the db. Also, you have to check the return value of the sp.
create procedure proc1
@.userid
as
set nocount on
declare @.error int
BEGIN TRAN
Delete from users where userid = @.userid
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('Error deleting from table users.', 16, 1)
return 1
end
Update mainSettings set userCnt = UserCnt -1
if @.error != 0
begin
rollback transaction
raiserror('Error updating from table [mainSettings].', 16, 1)
return 1
end
COMMIT TRAN
return @.@.error
go
declare @.rv int
declare @.error int
declare @.tc int
set @.tc = @.@.trancount
exec @.rv = proc1 102
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.error != 0
begin
print 'there was an error.'
end
if @.@.trancount != @.tc
rollback transaction
go
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"jroozee@.gmail.com" wrote:
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
server return a error?
Jason|||Read the articles. There you will find the answer.
AMB
"jroozee@.gmail.com" wrote:
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Same reason, last statement in the batch was successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112043813.998362.180420@.g14g2000cwa.googlegroups.com...
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Additionally, the reason you must check for the error, is that the SQL
programmers can not make the decision for you as to whether or not the error
is sufficient to roll back the transaction. That is for you to decide, so
you much catch the errors and rollback the transaction when necessary.. (
There are types of errors which will automatically roll back the transaction
without your intervention. These are generally more severe errors which
cause batch abort, or you have been chosen as a deadlock victim.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
>I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>

Multiple-page reports

Please help! I've been banging my head on this for too long...

I have an invoice report, and I need it to print 4 copies of itself. It's not as simple as increasing the printer copy count, as each copy needs a label ("Customer Copy", "Remit Copy", etc.).

There's just one another limitation: I do not own VB studio. I'm hoping there is a solution within CR itself. If not, is there any app (not expensive, of course) out there that can do this for me? Or can I use VBA from MS Office?

I've tried using a subreport as well, but the application that calls on CR apparently doesn't like subreports.

Any feedback would be greatly appreciated! Thank you fellow gurus!Hi

There are 2 options for you,

1. You can hve 4 different reports with different labels and you can print these 4 reports. (Not recommended)

2. You can have only 1 report and pass the value of the label when u are calling the report. Here u need to call the report 4 times with different label values. (Even I m using the same logic)|||Thanks for the feedback, but I still have a problem...

1. Multiple files, as you say, would not be recommended. I agree.

2. Thus I started something as you alternatively suggest. But I need to know... How can I automate the passing of the label? The end-user should not be incumbent to enter these values. How can I get record 1 to print 4 times (that is, with each corresponding label), and then proceed likewise through the remainder of the recordset? Is this possible using only CR?

Please advise! Thank you!
Manuelsql

Multiple values in a parameter

Hi
I have an input parameter 'AccountNumber' in one of my
reports. I would like to make it possible for the user to
put in multiple accountnumbers in this parameter.
I was on a reporting server courser and we did this by
creating a function that accepts the list of
accountnumbers and (comma delimited) and then transforms
it into a table with eacj accountnumber as a row in the
table. The report will then read from the table as a data
driven report.
For the life of me I cannot get this right though. Could
someone please send me the code for the function to do
this and how to code the report to look at the table.
Thanks,
TheresaTake a look at
http://www.gotdotnet.com/Community/Resources/Default.aspx?AFXPath=/Resource%5b@.ResourceId='2E882C0A-8D2B-4EAD-81BE-8E66C0941A18'%5d.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa" <theresa@.discussions.microsoft.com> wrote in message
news:20bd01c4a6e0$e409f740$a601280a@.phx.gbl...
> Hi
> I have an input parameter 'AccountNumber' in one of my
> reports. I would like to make it possible for the user to
> put in multiple accountnumbers in this parameter.
> I was on a reporting server courser and we did this by
> creating a function that accepts the list of
> accountnumbers and (comma delimited) and then transforms
> it into a table with eacj accountnumber as a row in the
> table. The report will then read from the table as a data
> driven report.
> For the life of me I cannot get this right though. Could
> someone please send me the code for the function to do
> this and how to code the report to look at the table.
> Thanks,
> Theresa

Multiple tables from within a stored procedure - how?

Hi,
I'm currently analyzing SSRS as a potential replacement for Crystal Reports
(and yes, it's definitely going to replace it). I do have one issue,
however... I'm attempting to point SSRS to a stored procedure that returns
four separate tables, but only the first one seems to be returned.
Is there a way to have access to each of the tables within the sproc?
Regards,
ScottNo there is not and this has not changed in RS 2008.
The most you can do is to have a parameter that selects which one you want
and then when calling the stored procedure set that parameter to a constant
value. If used in a report and all four are desired you have to call it four
times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott McNair" <smcnair@.beachexpress.takethispartout.com> wrote in message
news:Xns9A218ECAC3E7Adonquixote235gmailco@.207.46.248.16...
> Hi,
> I'm currently analyzing SSRS as a potential replacement for Crystal
> Reports
> (and yes, it's definitely going to replace it). I do have one issue,
> however... I'm attempting to point SSRS to a stored procedure that returns
> four separate tables, but only the first one seems to be returned.
> Is there a way to have access to each of the tables within the sproc?
> Regards,
> Scott

Multiple subreports in a report

I'm trying to generate two reports from one reporting services URL, so
I created a "master report" that simploy contains two sub-reports.
When I run it in the preview browser, one subreport runs, and not the
2nd.
In the RS Manager, both subreports say nothing but "Error: Subreport
could not be shown".
I wonder if it's a datasource problem, or something else?
Thanks,
JackI imagine that it is a datasource issue, if it runs fine in report designer
preview.
I have a report that has 5 subreports and the only problem/change that I had
to make was to make sure that the data source is set properly for all the
subreports. For some reason it looses it occasionally when the reports are
deployed.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>|||Just had similar issue. Make sure the subreport operates standalone, with the
correct datasource, and proper parameters are passed to the sub report
property on the main report.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>

multiple select in the parameter field in Crystal reports 8.5

I have a multiple select in the parameter field in Crystal reports 8.5 how do i check against that value in my SP?

Suppose I select 2 user from Drop down say

Mary
John

Are they comma seperated the way they r in Web based apps?
how do i build a SQL String based on the User criteria?Where is the dropdown - in Crystal (as a defined range of inputs) or in an application devloped from Vb or C++ or similar?
To use the VB example, when you populate a combo box, you can assign a value to the .ItemData property of the combo, and then return that to the report via the .ParameterFields collection exposed via the CR Active X control.
Say you populate your combo with data from a table:
RecordID Name
1 Fred
2 Mary
3 Joan
4 John
etc

then in VB code would be
...open recordset
...start looping thru records
cboTest.AddItem value_to_appear ' eg Fred
cboTest.ItemData(cboTest.NewIndex) = id 'eg 1
next

when you click on the combo, you can get the index and pass it to crystal
lngID = cboTest.ItemData(cboTest.ListIndex)

then in your code you can pass that to report when you open it, eg
Dim objPrintApp As CRAXDRT.Application
Dim objReport As CRAXDRT.Report
Dim objParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim objParamDef As CRAXDRT.ParameterFieldDefinition

' code presumes you have an input parameter to your report named pID
' Set the report source
Set objPrintApp = New CRAXDRT.Application
Set objReport = objPrintApp.OpenReport(strReportSourcePath & strSource)
objPrintApp.LogOnServerEx put logon parameters here

Set objParamDefs = objReport.ParameterFields
For Each objParamDef In objParamDefs
With objParamDef
Select Case .ParameterFieldName
'It finds and sets the appropriate Crystal parameters.
Case "pID"
.SetCurrentValue lngID

' add other Case conditions here to handle other parameter names.

' Catch-all for others
Case Else
strInput = InputBox("Enter a value for '" & .ParameterFieldName & "'")
If strInput <> "" Then
.SetCurrentValue strInput
Else
Exit Sub
End If
End Select
End With
Next

objReport.PrintOut

hope this helps

dave|||Thks for ur prompt reply...but the drop down is in crystal reports 8.5

I am using Crystal reports 8.5 stand alone with enterprise server......i mean i not using VB or anything else as App dev.
I have SP in SQL server 2000

I need to select multiple values from the drop down of the parameter field.
how do I put those values in the drop down of the parameter field which needs to be passed to the SP?

How do I build the Sql query once the multiple values r selected? are the multiple values comma seperated??

Multiple Sections in a Report

Hi
I was wondering, I previously worked with Crystal Reports and in this
offered the functionality of Multiple Sections, in the Report Header/Footer,
Page Header/Footer, Group Header/Footer and multiple Detail Sections, I
cannot seem to work out how to do this in SSRS, whilst building reports?
--
Kind regards
Ricky
(SS2005/SSRS2005)On Jun 23, 10:57 am, "Ricky" <r...@.msn.com> wrote:
> Hi
> I was wondering, I previously worked with Crystal Reports and in this
> offered the functionality of Multiple Sections, in the Report Header/Footer,
> Page Header/Footer, Group Header/Footer and multiple Detail Sections, I
> cannot seem to work out how to do this in SSRS, whilst building reports?
> --
> Kind regards
> Ricky
> (SS2005/SSRS2005)
As far as I know, in SSRS, you can only create a single header and
single footer (via the Layout view -> Report drop-down -> Page Header/
Page Footer). If you are using table/matrix controls, you are able to
use multiple group headers and footers; and using a table/matrix
control, you can have multiple detail rows. While this is not exactly
the same as in CR, it should get you by. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique
Shame that this is the case, do you not think this is somewhat restricting,
I thought that MS would have been able to implement something like this
quite easily...
Perhaps SS2008, may bring some enhancements?
Kind regards
Ricky
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182645968.089622.279980@.p77g2000hsh.googlegroups.com...
> On Jun 23, 10:57 am, "Ricky" <r...@.msn.com> wrote:
>> Hi
>> I was wondering, I previously worked with Crystal Reports and in this
>> offered the functionality of Multiple Sections, in the Report
>> Header/Footer,
>> Page Header/Footer, Group Header/Footer and multiple Detail Sections, I
>> cannot seem to work out how to do this in SSRS, whilst building reports?
>> --
>> Kind regards
>> Ricky
>> (SS2005/SSRS2005)
>
> As far as I know, in SSRS, you can only create a single header and
> single footer (via the Layout view -> Report drop-down -> Page Header/
> Page Footer). If you are using table/matrix controls, you are able to
> use multiple group headers and footers; and using a table/matrix
> control, you can have multiple detail rows. While this is not exactly
> the same as in CR, it should get you by. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 24, 8:00 am, "Ricky" <r...@.msn.com> wrote:
> Hi Enrique
> Shame that this is the case, do you not think this is somewhat restricting,
> I thought that MS would have been able to implement something like this
> quite easily...
> Perhaps SS2008, may bring some enhancements?
> Kind regards
> Ricky
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1182645968.089622.279980@.p77g2000hsh.googlegroups.com...
> > On Jun 23, 10:57 am, "Ricky" <r...@.msn.com> wrote:
> >> Hi
> >> I was wondering, I previously worked with Crystal Reports and in this
> >> offered the functionality of Multiple Sections, in the Report
> >> Header/Footer,
> >> Page Header/Footer, Group Header/Footer and multiple Detail Sections, I
> >> cannot seem to work out how to do this in SSRS, whilst building reports?
> >> --
> >> Kind regards
> >> Ricky
> >> (SS2005/SSRS2005)
> > As far as I know, in SSRS, you can only create a single header and
> > single footer (via the Layout view -> Report drop-down -> Page Header/
> > Page Footer). If you are using table/matrix controls, you are able to
> > use multiple group headers and footers; and using a table/matrix
> > control, you can have multiple detail rows. While this is not exactly
> > the same as in CR, it should get you by. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
I checked out the SQL Server 2008 White Papers and I haven't seen
mention of it so far; of course, it is still in Beta 1 at this point
so you never know.
Regards,
Enrique Martinez
Sr. Software Consultant
`

Multiple rows into single column

I'm working on a Reports and I need to combine data from multiple
rows (with the same EventDate) into one comma separated string. This is how the data is at the moment:

EmployeeID EventDate
2309 2005-10-01 00:00:00.000
2309 2005-10-01 03:44:50.000
2309 2005-10-01 08:59:00.000
2309 2005-10-01 09:29:44.000

I need it in the following format:

EmpID EventDate EventTime
2309 2005-10-01 00:00:00,03:44:50,08:59:00,09:29:44

There is no definite number of EventTime per Employee for a particular EventDate. And i don't want to use Iterative Methods.

Thanks & Regards
Rajan

Hi Rajan,

If you have access to sql server magazine take a look at this article, http://www.sqlmag.com/Article/ArticleID/93907/sql_server_93907.html

You can see the code even if you don't have membership so should still be useful. Listing 3 will probably be the most useful, although it is not particularly efficient.

Hope this helps.

Chris

|||

Another alternative might be something like:

declare @.mockup table
( EmployeeId integer,
EventDate datetime
)
insert into @.mockup values (2308, '1/1/2005')
insert into @.mockup values (2309, '10/1/2005')
insert into @.mockup values (2309, '10/1/2005 3:44:50')
insert into @.mockup values (2309, '10/1/2005 8:59')
insert into @.mockup values (2309, '10/1/2005 9:29:44')
insert into @.mockup values (2310, '11/1/2005')

select employeeId,
replace(replace(
( select e.eventDate as [data()]
from @.mockup e
where a.employeeId = e.employeeId
order by e.eventDate
for xml path ('')
), ' ', ','), 'T', ' ')
as EventDates
from @.mockup a
group by employeeId
order by employeeId

-- employeeId EventDates
-- -- -
-- 2308 2005-01-01 00:00:00
-- 2309 2005-10-01 00:00:00,2005-10-01 03:44:50,2005-10-01 08:59:00,2005-10-01 09:29:44
-- 2310 2005-11-01 00:00:00