Solved

SQL Timeout error

Posted on 2011-09-14
7
571 Views
Last Modified: 2012-05-12
I have a query that takes about 35 seconds to return. The same query returning in 15-20 seconds works fine. The problem occurs both inside my website and through my remoterly connected Visual Studio.
I have already changed the 'Connection Timeout' value to no effect [I'm aware that the default is 15 seconds - I changed it to 100, to 200 and 0 [in case that was a 'wait forever' flag !]
I CAN extract the data directly by going onto to my server and running the query from within the SQL Studio on that server and also remotely using my SQL Studio from here.
The issue doesn't appear to be the connection string

what else??
D.

0
Comment
Question by:CTOSian
  • 3
  • 2
  • 2
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36540030
You indicate this is a web application, have you checked the executionTimeout setting within Web.config or the session timeout of the application within IIS? I would suspect the former more so than the latter since this is happening on your VS also.
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 36540907
Hi CTOSian,

You should change CommandTimeout, not ConnectionTimeout, like this:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT ...";
cmd.CommandTimeout = 60;

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36541053
Good point. That is likely the culprit. Try that. You just have to ensure that your executionTimeout is set higher than whatever you end up needing for CommandTimeout.
0
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.

 

Author Comment

by:CTOSian
ID: 36541595
To be clear, I get this problem when running the Stored Procedure against the database in the VisualStrudio Server Explorer.
I see no opportunity to change the command Timeout either in the properties of the Connection string or the properties of the Stored Procedure....

In my code, I am using the Microsoft SqlHelper.ExecuteReader method to read data from the database and again, I see no opportunity to set the command timeout.

I have the following code :
Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SPName", Param1, Param2), IDataReader)

Which I suspect I may need to change to include a command timeout value...
... Some hgelp here would be appreciated.

but this doesn't apply to the VisualStudio part where I run the SP directly...
help needed here too

D.

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36541668
This might sound crazy, but check out: http://msdn.microsoft.com/en-us/library/ms190181.aspx
puzsol posted a root cause of 'SQL 2008 SP1' which was confirmed and posted to connect.

The workaround is illustrated here -- http://cushen.wordpress.com/2011/04/06/sql-server-2008-timeout-expired-error-and-sp1/ (turn off "Allow Remote server connections" then turn back on. I know. Try it, though. Apparently it works. I haven't noticed this issue myself, but I have SSMS installed locally so don't tend to query SQL from VS.
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
ID: 36541677
1) Visual Studio Server Explorer

Go to Tools->Options->Database Tools->Query and View Designers
If you do not want to have timeout at all, uncheck "Cancel long running query", otherwise set appropriate value at "Cancel after:"


2) SqlHelper.ExecuteReader

I'm afraid you will have to modify SqlHelper class or use another way to access your data


3) Are you sure you procedure is optimized enough? Maybe you should make it run faster (if possible) instead of using longer timeout?
0
 

Author Closing Comment

by:CTOSian
ID: 36541979
This answers the Visual Studio issue - Thanks.
I'm going re-issue the coding problem [after more research] as I think that I need to issue a more discrete reader command [one that allows me to add the commandtimeout attribute - SQLHelper.ExecuteReader doesn't do that....]

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

772 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