SSRS Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Question

I have a reporting solution with several reports. Up to now, I have been able to add a dataset based on a SPROC with no problems. However, when I try to add the lastest dataset, and use a SPROC for its query type, when I click on Refresh Fields I get the following error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I have tested the database connection in Data Source properties>Edit>Test Connection, and it's working fine.

I have increased the timeout to 100 in the following areas:

1. The connection string for the datasource, which is - Connect Timeout=100
2. Tools>Options>Database Tools>Query and View Designers. Cancel long running query is set to 100.
3. Tools>Options>Database Tools>Table and Database Designers>Checked 'Override Connection String time-out value for table designer updates. Transaction time-out after is set to 100

The SPROC runs fine in the SQL database. It takes about 55 seconds.

Any other ideas? Thanks.

UPDATE: I now can't add any dataset with a SPROC. Even thought the SPROCs are all working fine in SQL!!!!!!

Solution 1

If you are using Report Builder, you can increase timeout also in your DataSet.

Solution 2

I have also face same problem for adding the newly added column in stored procedure.

From the following way overcome this issue.

Alter the stored procedure as comment all the query except that final select command.

Now that new column has been added, then un-comment that quires in sp.

Solution 3

The thing to remember with your report is that when it is ran, it will attempt to run ALL the datasets just to make sure the are runnable, and the data they are requesting can be returned. So by running the each proc seperately you are in fact not duplicating with SSRS is trying to do...and to be honest don't bother.

What you could try is running sp_who while the report is running, or even just manually go through the procedures to see what table they have in common. Since your proc takes 52 seconds to return its dataset I'm going to assume its doing some heavy lifting. Without the queries nobody will be able to tell what the exact problem is.

I suggest using NO LOCK to see if that resolves your issue. If it does then your procs are fighting for data and blocking each other...possibly in a endless loop. Using NO LOCK is NOT a fix. Read what it does and judge for yourself however.

Comments

[Jackson]

I must have accidentally changed the default timeout and it was driving me crazy trying to figure out why I kept getting errors. I just fixed it thanks to you.

[Nick A]

This is what I had to do. Changing the timeout parameter did nothing.