Solved

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

Posted on 2011-09-09
10
448 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 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