dakota5
asked on
How to Extend Timeout Period for a View in SQL Server Mgmt Studio
We are creating fairly complex views in MS SQL Management Studio. When I go over a certain number of fields requested or a certain number of rows, I get the Timeout Expired error after 30 seconds.
I'd like to let it run a little longer. Yes I can probably tweak it to make it more efficient, but for now, I'd like to give it more time.
Is there a way to do this just for this view? If not, how do I change the timeout period setting for the database in general?
I'd like to let it run a little longer. Yes I can probably tweak it to make it more efficient, but for now, I'd like to give it more time.
Is there a way to do this just for this view? If not, how do I change the timeout period setting for the database in general?
ASKER
Tiagosalgado:
Per your suggestion I checked this, but it is already set to 0.
The error includes the following message--
Error Source: .Net SQL Client Data Provider
Perhaps there is a separate setting for the Client Data Provider
Per your suggestion I checked this, but it is already set to 0.
The error includes the following message--
Error Source: .Net SQL Client Data Provider
Perhaps there is a separate setting for the Client Data Provider
Try to add this line to your query (at begin)
SET LOCK_TIMEOUT <milisecounds>
SET LOCK_TIMEOUT <milisecounds>
ASKER
Tiagosalgado:
That didn't work either. Timed out at 30 seconds, even thought first statement was
SET LOCK_TIMEOUT 40000;
That didn't work either. Timed out at 30 seconds, even thought first statement was
SET LOCK_TIMEOUT 40000;
Hum, that's strange. Try this instead
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In same place, try to uncheck the option "Override connection string time-out .... ".
ASKER
No, apparently not. I changed this value to 60 (default was 30). The view still timed out at 30s
One more chance. Right click on your server > Properties. Go to Advanced and change Query Wait property.
Have you open new connection after change that values? Or re-open management studio ?
And set the "Execution time-out" to 0.