Dataset Refresh Timeout Error

Question

Dataset Refresh Timeout Error

rlavarn

SSC Enthusiast

Points: 120
More actions
September 20, 2010 at 9:20 am

#227029

I am working in SSRS 2008 R2 and I continue to receive this message below when trying to refresh fields, calling a stored procedure that takes 1 minute and 1 second to run.
/******************************/
Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
/******************************/
First attempt (Failed):
I have tried modifying the data source time out connection from 15 to 120 seconds by doing:
> Datasource Properties
> Edit
> Advanced
> Initialization (Connect Timeout, default 15 seconds)
Second attempt (Failed):
Changed the options of the report project to over ride long queries or execution times by:
> Tools
> Options
> Database Tools
> Query and View Designers
> Unchecked "Cancel long running query"
> Tools
> Options
> Database Tools
> Table and Database Designers
> Unchecked "Override connection string time-out value for table designer updates:"
I'm out of ideas by now as implementing all of these ideas has not affected the timeout error I continue to receive. Please help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Doug Lane SSCrazy Points: 2118 More actions · Answer 1

Try running the stored proc in SSMS using WITH RECOMPILE. Sometimes RS gets hung because it's trying to use an old execution plan after the output of a proc has changed.

rlavarn SSC Enthusiast Points: 120 More actions · Answer 2

I just tried the WITH RECOMPILE feature on the Stored Proc via SMSS then re-attempted the refresh via SSRS, no luck.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

I have had a similar issue due to parameter sniffing.

In your stored procedure declare local variables and then set these local variables to the values of your input parameters and try again.

For example

Your input parameters are something like:

@Input1 INT,

@Input2 INT

Down in the body of the SP.

DECLARE @Local1 INT

DECLARE @Local2 INT

SET @Local1 = @Input1

SET @Local2 = @Input2

Then through out the rest of the body of your SP, use the local variables rather than the input parameters.

I have had an SSRS query time out after about 15 minutes that ran in about 1 second after making this change. I now make a point to always make my SP's use local variables when they are going to produce a report.

Hope this helps.

veeren4urs SSCrazy Points: 2763 More actions · Answer 4

Hi,

Increase timeout in your reportserverDB.as per your need.

Thanks,

Veeren

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉