asked on

SQL Timeout error

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.

Kevin Cross

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.

Rimvis

Hi CTOSian,

You should change CommandTimeout, not ConnectionTimeout, like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT ...";
cmd.CommandTimeout = 60;

Open in new window

Kevin Cross

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.

CTOSian

ASKER

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.

Kevin Cross

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.

ASKER CERTIFIED SOLUTION

Rimvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CTOSian

ASKER

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!