Solved

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

Posted on 2011-09-09
10
442 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, but still no good. It must be something difficult. I've googled the error message and apparently the problem still persists.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
Starting closing process on behalf of the asker.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

16 Experts available now in Live!

Get 1:1 Help Now