Solved

SQL Timeout error

Posted on 2011-09-14
7
569 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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