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?
dakota5Asked:
Who is Participating?
 
dakota5Connect With a Mentor Author Commented:
Tiagosalgado:

I think the problem is that these are views being opened in SQL Server Mgmt Studio.
One view creates a list of patient IDs for which data should be accumulated.  The second view uses this list of patient IDs to select lots of data from a large table.
If I run the second view as a standard select statement within Mgmt studio, it takes 38s to run, and I get all the data. (No timeout after 30s)

If I access the second view from outside Mgmt studio, (odbc from Access, for example) I  also get all the data with no timeout- still takes about 38 seconds.

But if I open the second view from within Mgmt Studio-- it times out after 30 seconds.  It is apparently being limited by a setting that affects the displaying of views within management studio (not the underlying queries running on the server).

Any ideas?


0
 
tiagosalgadoCommented:
In MS SQL Management Studio go to menu Tools > Options > Query Execution > SQL Server > General
And set the "Execution time-out" to 0.
0
 
dakota5Author Commented:
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tiagosalgadoCommented:
Try to add this line to your query (at begin)
SET LOCK_TIMEOUT <milisecounds>
0
 
dakota5Author Commented:
Tiagosalgado:

That didn't work either.  Timed out at 30 seconds, even thought first statement was
SET LOCK_TIMEOUT 40000;
0
 
tiagosalgadoCommented:
Hum, that's strange. Try this instead
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
0
 
tiagosalgadoConnect With a Mentor Commented:
I don't know if the "Transaction time-out" value  in Tools > Options > Desingers > Table and Database Designer ... is applied to View too. Can you check ?
0
 
tiagosalgadoCommented:
In same place, try to uncheck the option "Override connection string time-out .... ".
0
 
dakota5Author Commented:
No, apparently not.  I changed this value to 60 (default was 30).  The view still timed out at 30s
0
 
tiagosalgadoCommented:
One more chance. Right click on your server > Properties. Go to Advanced and change Query Wait property.
0
 
tiagosalgadoCommented:
Have you open new connection after change that values? Or re-open management studio ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.