Thursday, May 15, 2008

SharePoint, SQL Server Reporting Services and Long-Running Reports

I have had the pleasure of working with SharePoint and SSRS in Integrated mode a LOT lately, both SQL 2K5 and Katmai. One thing that has been a troublesome annoyance is timeouts that occur for long-running reports. (Long-running in this context being defined as anything longer than 2 minutes) I have checked every setting in SharePoint that I could think of, set all data source timeout values that I could find, and even resorted to trying to restructure queries so that the reports could render in less than 2 minutes. (Thought about configuring snapshot execution as well, but even that doesn't help because the snapshot takes longer than 2 minutes to run and thus times out in SharePoint when trying to configure it)

In typical SharePoint fashion, when a report would time out, you'd get the basic EFAIL message which real doesn't tell you anything.

image

Well, after banging my head on the table for a good part of the day, and whining to my coworkers about how this thing just doesn't work, I came across the answer. Of course this was one of those answers that really bugged me when I found it...

Configuring SSRS Report Timeout in SharePoint

In Native mode, SSRS uses a default timeout of 9000 seconds (which if you do the math is 2.5 hours). In SharePoint integrated mode the timeout is reduced to 120 seconds (2 minutes). The timeout in SharePoint is actually related to the fact that reports are rendered using the http runtime. Any operation that is launched from within SharePoint (including Report Snapshot generation) using the httpRuntime object has to complete within the timeout parameters. If you plan on using SharePoint as a reporting services launching platform (i.e, you're using Integrated SSRS and you have your reports deployed to a report library) then you'll want to change the default. You do this by directly editing web.config for the application. (There is no GUI for this setting within SharePoint) The steps are as follows:

  1. Locate web.config for the SharePoint site you have configured for SSRS integration (in my case I used the default site, so the file is located at C:\inetpub\wwwroot\wss\VirtualDirectories\80)
  2. Open the file in an XML or text editor
  3. Look for "httpRuntime" (easiest thing to do is use the FIND functionality of your editor - ctrl F in most cases)
  4. Inside the tag, add executionTimeout="9000" (the end result will look like this: <httpRuntime maxRequestLength=”51200” executionTimeout=”9000” />)
  5. Close and save the file

  6. Reset IIS
  7. Run the report and notice that you don't get the EFAIL message shown above

Hopefully this helps alleviate some of the headache I had to go through. (And by the way, this is all documented in books online, it's just sort of buried)

10 comments:

Amit Bajaj said...

Thanks Ted. I was facing the same problem. This was really very helpful and to the point.

Amit Bajaj said...
This comment has been removed by the author.
Anonymous said...

Dead on for my problem. thanks a bunch for taking the time to publish this.

Rick Spicknall said...

Ah, relief... Thanks a bunch, wish I had found earlier.

Nenya said...

Hi! I did this and now I get a new error:
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host

Any idea?

Thanks in advance!

Anonymous said...

Even 2 years later, same problem, same solution thank you for the help and keeping this blog open!

Anonymous said...

Thank you! When I read your post my coworker thought I had written it! We had the exact same problem.
You're a lifesaver.

Adnan13 said...

Hi, this works like a marvel when running the report from Sharepoint.

However, my report still times out when running it via a script using the web service and Sharepoint:

ReportExecutionService

Is there a different timeout setting for that?

Anonymous said...

Super!

Darkos said...

Hey Ted,

Thanks for the resolution steps....

after bandaging my head (due to banging my head against the wall) and re-applying the bandage (due to banging my head for such a simple resolution...) it worked like a charm...

Its definitely good to have the documentation... :)

Thanks a bunch mate