| Author |
Topic |
|
rkosuri2002
Starting Member
5 Posts |
 Posted - 2008-09-05 : 17:07:48
|
| 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 hostStack Trace: [SocketException (0x2746): An existing connection was forcibly closed by the remote host]System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult)+1034959System.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)+205System.Net.PooledStream.EndRead(IAsyncResult asyncResult) +12System.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) +553Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +941Microsoft.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() +303System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-05 : 17:09:55
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-07 : 17:25:41
|
| 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. |
|
|
|
rkosuri2002
Starting Member
5 Posts |
Posted - 2008-09-08 : 11:29:03
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rkosuri2002
Starting Member
5 Posts |
Posted - 2008-09-08 : 14:13:55
|
| 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. |
|
|
|
rkosuri2002
Starting Member
5 Posts |
Posted - 2008-09-09 : 10:41:53
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-09 : 13:09:19
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
rkosuri2002
Starting Member
5 Posts |
Posted - 2008-09-10 : 22:30:54
|
| 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). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rlevine
Starting Member
2 Posts |
Posted - 2011-07-25 : 18:18:31
|
| 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). |
|
|
|
navya krishna
Starting Member
39 Posts |
Posted - 2013-01-23 : 01:49:57
|
| 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-13Customer 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 |
|
|
|
|
SSRS - Issue with large reports