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.

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” />)

Close and save the file

5. Reset IIS
6. 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)