Report Failure when rendering - SQL SERVER 2005 - Report manager
Hi,
i am using SQL SERVER 2005
I have a report that sits on the report manager and exports nightly to a named drive.
this has been working fine until this week
one night it works the next night it does not
when i check the subscription to see if it has saved down it says
''Failure writing file ReportName : An error occurred during rendering of the report.''
When i checked out the log files for this it says:
''e ERROR: Throwing Microsoft.ReportingService
Info: Microsoft.ReportingService
The report is huge
its well over 160k rows
I can run the report but can no longer export it on the rpeort manager using the Export Tool
I had been able to ftp or set up a subscription to save the file to a drive but now thats not working.
Can anyone give any advise on what to do please?
Kind Regards,
Putoch snapshot-of-report-not-rendering.txt snapshot-of-report-not-rendering.txt
i am using SQL SERVER 2005
I have a report that sits on the report manager and exports nightly to a named drive.
this has been working fine until this week
one night it works the next night it does not
when i check the subscription to see if it has saved down it says
''Failure writing file ReportName : An error occurred during rendering of the report.''
When i checked out the log files for this it says:
''e ERROR: Throwing Microsoft.ReportingService
Info: Microsoft.ReportingService
The report is huge
its well over 160k rows
I can run the report but can no longer export it on the rpeort manager using the Export Tool
I had been able to ftp or set up a subscription to save the file to a drive but now thats not working.
Can anyone give any advise on what to do please?
Kind Regards,
Putoch snapshot-of-report-not-rendering.txt snapshot-of-report-not-rendering.txt
ASKER
i have 7.7GB of memory on the server.
That is i checked on Management studio
Right clicked on the Server name there and hit properties and it said on the General tab that there was 7935 (MB)
So would that mean thats how much memory is on the server?
min memory in use = 0
max is set to 2147483647
How much should one have on their server?
Thank you,
Putoch
That is i checked on Management studio
Right clicked on the Server name there and hit properties and it said on the General tab that there was 7935 (MB)
So would that mean thats how much memory is on the server?
min memory in use = 0
max is set to 2147483647
How much should one have on their server?
Thank you,
Putoch
The message you posted is obvious plus you mentioned that "The report is huge its well over 160k rows" and is failing ocasionaly so at that time the server is runnig out of memory. Not sure what answer you expect that you need more RAM on that server OR split the SSRS on another box (computer) of its own.
ASKER
Ok thanks Icohan.
I guess i'm wondering why with 7GB of Ram that its causing this issue
When i have another server with 4GB of ram that has never had any issues.
I know they run different load items so i suppose you can't compair
I suppose the answer i was expecting was
find out how much ram is running at a perticular time see if there is any free space at that time to run your report , check the packages that are running at that time is their any other way to optimize them.
i'm not sure how to do all of that but i'll start to research that too.
Thanks a mill for the advise
Regards,
Putoch
I guess i'm wondering why with 7GB of Ram that its causing this issue
When i have another server with 4GB of ram that has never had any issues.
I know they run different load items so i suppose you can't compair
I suppose the answer i was expecting was
find out how much ram is running at a perticular time see if there is any free space at that time to run your report , check the packages that are running at that time is their any other way to optimize them.
i'm not sure how to do all of that but i'll start to research that too.
Thanks a mill for the advise
Regards,
Putoch
ASKER
Hi,
I was looking at this http://msdn.microsoft.com/en-us/library/aa337266.aspx
and it says that this message:
'' An error occurred during rendering of the report.'' -- which is what the results of my subscription says when i try to save the report to the defined drive.
As i had mentioned that there are over 160k rows in this report
and then on the link above it says reeasons for getting this message:
''A message that indicates that the number of rows exceeds the maximum possible rows per sheet is typically caused when the number of rows in an Excel worksheet is exceeded. Excel supports up to 65,000 rows.''
Or ''A message that indicates that the size is not supported is typically caused when the specified RDL page size is not valid. Specify a valid RDL page size and then try again.
''
So i export the files as CSV so it would avoid the limitations on EXCLE.
So is there a way does anyone know as to how to change the properties on my configuration files to allow larger size reports to be saved
The files is able to generate on the rpeort manager its just getting the inforamtion from the report manager thats the problem.
Thanks ,
Putoch
I was looking at this http://msdn.microsoft.com/en-us/library/aa337266.aspx
and it says that this message:
'' An error occurred during rendering of the report.'' -- which is what the results of my subscription says when i try to save the report to the defined drive.
As i had mentioned that there are over 160k rows in this report
and then on the link above it says reeasons for getting this message:
''A message that indicates that the number of rows exceeds the maximum possible rows per sheet is typically caused when the number of rows in an Excel worksheet is exceeded. Excel supports up to 65,000 rows.''
Or ''A message that indicates that the size is not supported is typically caused when the specified RDL page size is not valid. Specify a valid RDL page size and then try again.
''
So i export the files as CSV so it would avoid the limitations on EXCLE.
So is there a way does anyone know as to how to change the properties on my configuration files to allow larger size reports to be saved
The files is able to generate on the rpeort manager its just getting the inforamtion from the report manager thats the problem.
Thanks ,
Putoch
So what is exactly the subscription doing? Is it sending mail on snapshot updated with the report attached to it in Excel format?
I suggest you send only the link and let the user access it and download it by themselfs if they need. The link will be pointing to the history so the report is not generated again especialy is huge.
I suggest you send only the link and let the user access it and download it by themselfs if they need. The link will be pointing to the history so the report is not generated again especialy is huge.
ASKER
I set the report up in the subsccription to
Deliver by Windows File Share
the report Render Format is CSV
this used to work fine, so i would like to know why it does not work now,
perhaps it is to do with the RAM being limited on the systems, but i want to make sure that it is not something else either.
as i am able to run the query for the rpeort on Management studios
and then select the results and save as to the drive and save the results down in CSV.
I can't send them the link to the report, as they can not export the report from the report manager.
It causes an error when trying to do this. Even when exporting to csv.
this is why i started to save it down to the drive in the first place.
So i'm thinking, is there something limiting the files to save when it hits a certain amount of rows?
is this something i can change?
as if this is the case perhaps getting more memory will not help the issue.
thanks
Putoch
Deliver by Windows File Share
the report Render Format is CSV
this used to work fine, so i would like to know why it does not work now,
perhaps it is to do with the RAM being limited on the systems, but i want to make sure that it is not something else either.
as i am able to run the query for the rpeort on Management studios
and then select the results and save as to the drive and save the results down in CSV.
I can't send them the link to the report, as they can not export the report from the report manager.
It causes an error when trying to do this. Even when exporting to csv.
this is why i started to save it down to the drive in the first place.
So i'm thinking, is there something limiting the files to save when it hits a certain amount of rows?
is this something i can change?
as if this is the case perhaps getting more memory will not help the issue.
thanks
Putoch
Obviously you don't want to give users access to Report Manager but SSRS has two default websites for that matter. One is Report Server that could/should be accessed by Users and not SSRS admins in IE or some other browser available on the users machines.
The error is clear text and there's no hidden issue that when these reports need to be created the serevr runs SOMETIMES not always out of memory and this depends on many factors. The issue is that even CSV file gets created not EXCEL I believe the backgound proccess to be executed is still the same and this needs lots of memore for your large reports.
As mentioned to fix this:
Add more RAM to the box running SQL and SSRS or
Split SSRS from SQL box or
Split the report in smaller reports.
The error is clear text and there's no hidden issue that when these reports need to be created the serevr runs SOMETIMES not always out of memory and this depends on many factors. The issue is that even CSV file gets created not EXCEL I believe the backgound proccess to be executed is still the same and this needs lots of memore for your large reports.
As mentioned to fix this:
Add more RAM to the box running SQL and SSRS or
Split SSRS from SQL box or
Split the report in smaller reports.
ASKER
Ok thanks for your advise Icohan.
I'll try doing this and will let you know how this has helped.
Regards,
putoch
I'll try doing this and will let you know how this has helped.
Regards,
putoch
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you icohan.
One last question for you, the AWE settings
On this site http://msdn.microsoft.com/en-us/library/ms190673.aspx
it says ''This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.''
So i was afraid to enable it on SQL SERVER 2005.
now i believe this is refering to SQL SERVER 2008, but i wasn't sure if with new patches being added to SQL SERVER 2005 it would cause issues enabling AWE
I do not currently have this enabled, and i can see from our CPE monitoring tool that the server only reaches 4GB of RAM usage at any time even though there is 16GB of RAM available. So i think perhaps by enabling the AWE and setting the ini file with the 3GB/PAE additions
have you any thoughts on this?
thank you,
Putoch.
One last question for you, the AWE settings
On this site http://msdn.microsoft.com/en-us/library/ms190673.aspx
it says ''This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.''
So i was afraid to enable it on SQL SERVER 2005.
now i believe this is refering to SQL SERVER 2008, but i wasn't sure if with new patches being added to SQL SERVER 2005 it would cause issues enabling AWE
I do not currently have this enabled, and i can see from our CPE monitoring tool that the server only reaches 4GB of RAM usage at any time even though there is 16GB of RAM available. So i think perhaps by enabling the AWE and setting the ini file with the 3GB/PAE additions
have you any thoughts on this?
thank you,
Putoch.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
<<< System.OutOfMemoryExceptio