Solved

Timeout error in sql server 2005 management studio express,is there a solution?

Posted on 2011-09-09
10
444 Views
Last Modified: 2012-05-12
When I create a view such as 'select count(*) from sometable where....' the query times out after a minute. However, if I run the same query from FILE>NEW>QUERY WITH CURRENT CONNECTION it displays the count after 3 minutes. OR, if I modify the query to say, select * from sometable where... It runs fine.

THe error is, '.net sql data provider timeout expired'.  Is there a solution? I've set all the timeouts available from the menu to infinite or a high number. I've opened c# and used the following code: SqlCommand cmd = new SqlCommand();
            //cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 1200;
Nothing works.
0
Comment
Question by:OutOnALimbAlways
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 350 total points
ID: 36513050
besides those there are ConnectionTimeouts and SQL Server Query timeout you need to check to make sure are I'd say at least 600 seconds.
0
 

Author Comment

by:OutOnALimbAlways
ID: 36513519
Ok, here's what I've done so far in the express mgmt studio:
Lock timeout = -1 Set in tools, options, query execution, sql server, advanced.

Execution time out  = 0 (unlimited) Set in tools, options, query execution, sql server, general

Connection time out, Set while connecting, clicked options button- 600 secs.

I can't find anything specifically labeled 'SQL SERVER QUERY TIMEOUT'. Maybe that's where I'm going wrong. I only have the 2005 express edition, maybe it's not here?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36513625
You should be able to find that in SQL properties as "Remote Query Timeout" value - just right click your server name in SSMS and select properties or run command below against your SQL:


exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure
0
 

Author Comment

by:OutOnALimbAlways
ID: 36514541
Thanks, but still no good. It must be something difficult. I've googled the error message and apparently the problem still persists.
0
Backup Your Microsoft Windows Server®

Backup 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.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 36514797
>>It must be something difficult. <<
It is not.  It is actually quite simple you need to execute your VIEW from the query window and not from the Designer.  That last will timeout with any query that takes longer than 30 seconds.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36514801
On the other hand, your question is confusing you title refers to SSMSE, but then you refer to .NET code.  Which is it?  The answer is different in each case.  If it an application using .NET than post all the relevant code.
0
 

Accepted Solution

by:
OutOnALimbAlways earned 0 total points
ID: 36514895
acperkins-- I'm new to sql server. Though I was aware there is a 'good' sql pane and a 'bad' sql pane (re-read my question) I just assumed that if you right-click the view and click open view it would open it in the best manner possible. I see now you have to click 'edit'  in your view to get to the 'good' sql pane.

I'm doing everything from the sql server management studio. The only reason for the net code was because I read somewhere that setting the command timeout in this manner would solve the  problem, but as I say, it didn't. This method of solving the problem made sense to me since the error message mentioned .net. But the only relevant code is the few lines I posted in the question.

The problem is solved, however. I was not able to set the 'query timeout' option mentioned by icohan in the ssms express. I did google a solution  where you actually have to hack the registry. Go to
Hkcu\software\microsoft\microsoft sql server\90\tools\shellsem\dataproject\sqlQueryTimeOut, and modify it.
0
 

Expert Comment

by:WhackAMod
ID: 36534829
Starting closing process on behalf of the asker.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36516421
>>Though I was aware there is a 'good' sql pane and a 'bad' sql pane <<
I have no idea what you are referring to.  In my view, the only window you should be using in SSMS is the one you get when you click "New Query".  This gives you full access to all T-SQL functionality without all the stupid limitations in the Designer.

>>I see now you have to click 'edit'  in your view to get to the 'good' sql pane.<<
And you should know that this has changed in SQL Server 2008 and SQL Server 2008 R2.

>>I was not able to set the 'query timeout' option mentioned by icohan in the ssms express<<
That has no bearing on your problem whatsoever.  Your timeout error is a client setting and not a server configuration.

>>I did google a solution  where you actually have to hack the registry. <<
Don't waste your time (see above).
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL joining table to itself to combine rows 5 30
SQL Query 34 79
SQL Server Question 5 25
SQL - How to list all tables participating in a query 7 37
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now