SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 SSRS - Issue with large reports
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rkosuri2002
Starting Member

5 Posts

Posted - 09/05/2008 :  17:07:48  Show Profile  Reply with Quote
Hi,

Here's the issue. I'm hitting a SSRS 2005 report server from an ASP.NET application using a ReportViewer control. It seems to work fine for reports with less than around 200,000 records but breaks for anything over(it breaks after waiting for 4-5 minutes, see below for details). This behavior is consistent across multiple calls to the report.

It looks to me like some kind of a timeout is forcing the processing to be aborted for large datasets. I tried increasing the ConnectionTimeout for the website, increased 'SQLCommandTimeoutSeconds'
and 'DatabaseQueryTimeout' in rsreportserver.config, but no luck so far. Anyone have any ideas?

Thanks in advance for any help.

--------------------

Error Details:

Message: An existing connection was forcibly closed by the remote host
Exception Details: System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

Stack Trace:

[SocketException (0x2746): An existing connection was forcibly closed by the remote host]
System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult)+1034959
System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult) +51

[IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.]
System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)+205
System.Net.PooledStream.EndRead(IAsyncResult asyncResult) +12
System.Net.Connection.ReadCallback(IAsyncResult asyncResult) +39

[WebException: The underlying connection was closed: An unexpected error occurred on a receive.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +553
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +941
Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +97
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +126
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +202
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +303
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64





tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 09/05/2008 :  17:09:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why would anyone want to run a report that returns this much data? Why not filter the result set down?

No query that returns that many records is going to be efficient. Try running the query in Management Studio or Query Analyzer to see how long it is going to take to render that much data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dexter.knudson
Constraint Violating Yak Guru

Australia
260 Posts

Posted - 09/07/2008 :  17:25:41  Show Profile  Reply with Quote
Yes, you need to add parameters to the report so criteria is passed to the server & you get a reduced result set. Think about how the users want to access the data. They will come with a question on their mind to the report. If you need totals & summaries of that much data, then Analysis Services is probably a better tool.
Go to Top of Page

rkosuri2002
Starting Member

5 Posts

Posted - 09/08/2008 :  11:29:03  Show Profile  Reply with Quote
Hello all,

Thanks for the reply. We do have filters and it works very well for most of the scenarios. And we do have reports that will generate totals and summaries. But there are instances when the users would want large amounts of data returned. They usually export this data to excel or csv. From what I'm hearing, no one will probably ever read this data but they need it to generate and file it anyway for compliance purposes.

The report I mentioned takes about 3-4 minutes to run on our dev environment, but doesn't break (breaks in our stage environment). Takes about 2 mins in Management Studio to get the results. So, I guess my question is: Is there a timeout setting or any other way to get this report working? Failing with the exception I mentioned doesn't look too graceful even if SSRS can't handle large amounts of data. I'm also not sure if there is a limit to the amount of data that can be rendered by SSRS.. could not find anything on it.


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 09/08/2008 :  12:24:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
Modify the timeout setting in the data source. You can do this in Report Manager.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

rkosuri2002
Starting Member

5 Posts

Posted - 09/08/2008 :  14:13:55  Show Profile  Reply with Quote
Thanks for pointing me in that direction, Tara. I was running out of ideas on where to set the timeout. I will let you know how it goes. I'm currently going through the usual big company red tape to get access to the Stage Report Manager. I also found a way to set this directly in the Report Server database but will refrain from touching it for now.
Go to Top of Page

rkosuri2002
Starting Member

5 Posts

Posted - 09/09/2008 :  10:41:53  Show Profile  Reply with Quote
Still no luck. I set the option "Do not timeout report execution" for the report in Report Manager (I could not find any timeout options for the data source itself). Same error. Any other ideas?

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 09/09/2008 :  13:09:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try running the report through Report Manager to see if it times out there. This will tell us where we'll need to set the timeout value. Let us know how it goes in Report Manager.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

rkosuri2002
Starting Member

5 Posts

Posted - 09/10/2008 :  22:30:54  Show Profile  Reply with Quote
Same issue when I run it in Report Manager as well. There is a change in behavior when I set the execution timeout to never expire in Report Manager - it gives me a IE style Page Not Found display after a while (in the space where the report should show up, not the whole page).

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 09/11/2008 :  12:58:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://msdn.microsoft.com/en-us/library/ms155782(SQL.90).aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

rlevine
Starting Member

USA
2 Posts

Posted - 07/25/2011 :  18:18:31  Show Profile  Reply with Quote
Have you ever figured this out. I am having the same issue. I have a report which allows user to view a summarized data of our financials. However the Comptroller needs to be able to view (and save) the details that make up this summarized report for compliance reasons. THAT IS THE BUSINESS REQUIREMENT.
This query runs for about 5 minutes in our production environment using SSMS, however when running from SSRS, report doesn't return when trying to retrieve details. The details dataset at times returns up to 1.5 million rows of data. (This is all summarized and queried in a stored proceudre).
Go to Top of Page

navya krishna
Starting Member

India
39 Posts

Posted - 01/23/2013 :  01:49:57  Show Profile  Reply with Quote
Dear all,

i have one problem in ssrs.i need to display sales report customer wise [sales and budget and variance and variance percentage] from different databases but in single server.it will be there detailes in multiple databases.i need to display detailes in single report.could you please anyone help me.for example we are maintaining 2010,2011 databases so i need to display customer no,sales,budget,variance,variance percentage in single report ...

year from 2012 year to 2013 date filter 01-01-12..31-12-13

Customer No. Sales -2012 Budget-2012 Variance-2012 Variance %-2012 Sales -2013 Budget-2013 Variance-2013 Variance %-2013

i need details like this in one report using multiple databases.please anyone help me.


navya krishna katta
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000