4

I've ran into a Timeout issue while executing a stored procedure for a SSRS Report I've created in Business Intelligence Development studio (BIDS). My stored procedure is pretty large and on average takes nearly 4 minutes to execute in SQL Server Management Studio. So i've accomidated for this by increasing the "Time out (in seconds)" to 600 seconds (10 mins). I've also increased the query timeout in the Tools->Options->Business Intelligence Designers-->Query Timeout AND Connection Timeout to 600 seconds as well.

Lastly, I've since created two other reports that use stored procedures with no problems. (they are a lot smaller and take roughly 30 seconds to execute). For my dataset properties, I always use Query type: "Text", and call the stored procedure with the EXEC command.

Any ideas as to why my stored procedure of interest is still timing out?

Below is the error message I receive after clicking "Refresh Fields":

"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

Details

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated."

Thank You for your time.

BrianJB10412
  • 43
  • 1
  • 3
  • 1
    First, if your procedure is returning a large amount of fields, you may need to filter it into smaller chunks because of memory limitations. Second, if you have too many complex expressions/grouping in the report, it may be timing out during report processing even though the procedure is done. See if that helps at all. – StevenWhite Apr 24 '13 at 16:09

1 Answers1

1

Check the Add Key="DatabaseQueryTimeout" Value="120" value in your rsreportserver.config file. You may need to increase it there also.

More info on that file:

http://msdn.microsoft.com/en-us/library/ms157273.aspx

Also, in addition to what the first commenter on your post stated, in my experience if you are rendering to PDF, those can time out also. Your large dataset is returned w/i a reasonable amount of time, however the rendering of the PDF can take forever. Try rendering to Excel. The BIDs results will render rather quickly, but exporting the results are what can cause an issue.

D.S.
  • 1,413
  • 2
  • 16
  • 26
  • I should also add, that If you are using an older version of SSRS, the Excel 2005 render is limited to 65,000 rows per sheet. You will need to dynamically add a page break which forces a new sheet in the rendered excel file. Check out this link, scroll down to the Page Break section http://msdn.microsoft.com/en-us/library/ms157328.aspx – D.S. Apr 28 '14 at 19:06