Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-15
11
Medium Priority
?
807 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 800 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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