Solved

How to Extend Timeout Period for a View in SQL Server Mgmt Studio

Posted on 2008-10-15
11
800 Views
Last Modified: 2012-06-21
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
Comment
Question by:dakota5
  • 7
  • 4
11 Comments
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22726745
In MS SQL Management Studio go to menu Tools > Options > Query Execution > SQL Server > General
And set the "Execution time-out" to 0.
0
 

Author Comment

by:dakota5
ID: 22727213
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
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22729697
Try to add this line to your query (at begin)
SET LOCK_TIMEOUT <milisecounds>
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:dakota5
ID: 22737481
Tiagosalgado:

That didn't work either.  Timed out at 30 seconds, even thought first statement was
SET LOCK_TIMEOUT 40000;
0
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22738533
Hum, that's strange. Try this instead
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
0
 

Accepted Solution

by:
dakota5 earned 0 total points
ID: 22740520
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
 
LVL 8

Assisted Solution

by:tiagosalgado
tiagosalgado earned 200 total points
ID: 22740822
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
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22740891
In same place, try to uncheck the option "Override connection string time-out .... ".
0
 

Author Comment

by:dakota5
ID: 22740922
No, apparently not.  I changed this value to 60 (default was 30).  The view still timed out at 30s
0
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22740945
One more chance. Right click on your server > Properties. Go to Advanced and change Query Wait property.
0
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22740980
Have you open new connection after change that values? Or re-open management studio ?
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question