I'm running on SQL 2000 with SRS. Standard install (I think, it was all set up by IT)
I have reports that run fine in the Report Manager but when they are run through a schedule (email) some of them (albeit most) "rsProcessingAborted" because of a report error.
Looking at the logs it seems generally based around comparison failure because of datatypes, eg (from the logs):
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of sort expression for the list 'List1' cannot be performed. The comparison failed. Please check the data type returned by sort expression. Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of group expression for the table 'tblGroupTable' cannot be performed. The comparison failed. Please check the data type returned by group expression.Now, when I remove sorting/grouping the reports run through the schedule OK; but that is obviously NOT what I want the report to look like. But it proves that the sorting/grouping has something to do with the problem.
I'm pretty sure that they are NOT the problem though, as the reports render in Report Manager fine with all the original sorting/grouping in place (ie no problems during development, just when we wanted them served to the employees. Isn't that always the way it goes?). We've even had some reports which scheduled OK previously, but throw errors now.
I'm at a loss!!
Could it be a time-out issue somewhere? If so: where? Looking at the Execution Logs, the reports that fail have unusually long Process Times, with a Data Retrieval Time of 0. I guess that the process time is longer because of processing the error, but does the Data Retrieval time of 0 mean that no data was delivered to the report? How can that be? IT tell me it can't be memory issues as it's sitting on a 2GB machine, but could there be something else with SQL set-up? I googled the error and found a few pages where people said that puting SRS in its own application pool fixed their problem. When I passed this on to IT they said that would only help memory... Any ideas?Any suggestions greatfully appreciated
Do the report subscriptions ALWAYS fail, or intermittently?
Do those same reports ALWAYS succeed when executed live?
How much RAM is on the Dev machine, and what OS?
What OS is on the productio machine?
What changed between the time that some scheduled reports worked fine, and when they started erroring?
Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.
|||Also, what are the types and #'s of CPU's on the Dev and Production machines?|||Mike Schetterer -- MSFT wrote:
Do the report subscriptions ALWAYS fail, or intermittently?
The reports seem to fail always when they contain some data. Maybe the oposite is better: The reports don't fail when they do not contain data. Of the report I can remember off the top of my head: the schedule didn't fail on 4 occasions, three of those didn't have any data, one had one record.
For example, for one of our reports that is meant to run this morning:
Time Start
Time End
Data Retrieval
Process
Render
Status
User Name
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=3
6/02/2006 7:00:08 AM
6/02/2006 7:00:09 AM
0
112
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=8
6/02/2006 7:00:08 AM
6/02/2006 7:00:12 AM
2979
359
255
rsSuccess
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=6
6/02/2006 7:00:57 AM
6/02/2006 7:00:58 AM
0
47
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=11
6/02/2006 7:00:57 AM
6/02/2006 7:00:58 AM
0
60
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=13
6/02/2006 7:00:58 AM
6/02/2006 7:00:58 AM
0
51
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=1
6/02/2006 7:00:58 AM
6/02/2006 7:00:58 AM
0
72
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=9
6/02/2006 7:00:59 AM
6/02/2006 7:00:59 AM
0
63
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=10
6/02/2006 7:00:59 AM
6/02/2006 7:00:59 AM
0
48
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
Do those same reports ALWAYS succeed when executed live?
YES!
How much RAM is on the Dev machine, and what OS?
Intel Xeon 2.4Gb x 2, 2Gb Ram
The Dev machine and the production machine are the same. I use a Remote Desktop into the production machine from my desktop, I guess the documents are stored on my workstation and deployed to the production machine.
What OS is on the productio machine?
Windows Server 2003, SP1
What changed between the time that some scheduled reports worked fine, and when they started erroring?
Nothing that I am aware of... but I'll ask the IT people...
Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.
The schedules fail regardless of the time of day they run (I wondered if the server got overloaded whilst trying to do other things): but there is no difference.
Another interesting fact: whilst investigating I discovered that one of the reports that ran through a schedule after all grouping and sorting was removed displayed "#error" in a cell that contained the result from a Library I'd written. Though this report runs and displays fine through the Report Manager! (this field was not used in the grouping or sorting either).
Yes, I've read somewhere else that how you've configured the Application Pool can make a difference (our IT said it wouldn't here). But I've definitely read in a different forum that that fixed their problem.
At the moment the Report Server Interface and the Report Server are in the DefaultAppPool.
Thanks for your interest!
Perry
|||Do your reports have parameters?
When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?
Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?
Thanks,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Do your reports have parameters?
Some do, others don't, but it doesn't seem to be a problem.
When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?
Both, or sometimes all three (included type in)
Yes, ALL the reports run fine through the Report Manager.
Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?
No: they fail most of the time. In the section of the ExecutionLog in a previous post you can see that for that report it ran once (with data) and failed the rest (they would have had data too.
Thanks,
Perry
|||What are the group and sort expressions from the report that you find is failing?
-Lukasz
|||What are the group and sort expressions from the report that you find is failing?
In every case it's "=Fields!FieldName.value"
Perry
