Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Timeout error

Posted on 2011-09-14
7
Medium Priority
?
576 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
[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
  • 2
  • 2
7 Comments
 
LVL 60

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 60

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 60

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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