[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

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?
0
dakota5
Asked:
dakota5
  • 7
  • 4
2 Solutions
 
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
 
tiagosalgadoCommented:
Try to add this line to your query (at begin)
SET LOCK_TIMEOUT <milisecounds>
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
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
 
dakota5Author 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:
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now