BIDS SSRS Specifies a request for a request timeout when using a stored procedure with timeout parameters set

Question

I encountered a Timeout problem when executing a stored procedure for an SSRS report that I created in the Business Intelligence Development (BIDS) development studio. My stored procedure is quite large and takes about 4 minutes on average to execute in SQL Server Management Studio. So I took advantage of this by increasing the "Timeout (in seconds)" to 600 seconds (10 minutes). I also increased the request timeout in Tools-> Options-> Business Intelligence Designers → Time Query Timeout and the connection timeout to 600 seconds.

Finally, since then I have created two other reports in which stored procedures are stored without problems. (they are much smaller and take about 30 seconds to complete). For the properties of my dataset, I always use the query type: "Text" and call the stored procedure using the EXEC command.

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

The following is the error message after clicking the Refresh Fields button:

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

More details

"The timeout period has elapsed. The timeout period expires before the operation is completed or the server does not respond. The application is completed.

Thank you for your time.

Answer 1

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

Additional information on this file:

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

Also, in addition to what the first commenter of your post said, in my experience, if you are rendering in PDF format, this may also happen from time to time. Your large dataset returns w / i a reasonable amount of time, however, rendering a PDF file can take forever. Try rendering in Excel. BID results will display fairly quickly, but exporting the results may cause problems.