Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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

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
OutOnALimbAlways
Asked:
OutOnALimbAlways
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
lcohanDatabase AnalystCommented:
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
 
OutOnALimbAlwaysAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
OutOnALimbAlwaysAuthor Commented:
Thanks, but still no good. It must be something difficult. I've googled the error message and apparently the problem still persists.
0
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
OutOnALimbAlwaysAuthor Commented:
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
 
WhackAModCommented:
Starting closing process on behalf of the asker.
0
 
Anthony PerkinsCommented:
>>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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now