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

x
?
Solved

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

Posted on 2011-09-09
10
Medium Priority
?
473 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
[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
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1400 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 40

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
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…

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