Timeout not being honoured in connection string

Question

I have a long running SQL statement that I want to run, and no matter what I put in the "timeout=" clause of my connection string, it always seems to end after 30 seconds. I'm just using SqlHelper.ExecuteNonQuery() to execute it, and letting it take care of opening connections, etc.

Is there something else that could be overriding my timeout, or causing sql server to ignore it? I have run profiler over the query, and the trace doesn't look any different when I run it in management studio, versus in my code.

Management studio completes the query in roughly a minute, but even with a timeout set to 300, or 30000, my code still times out after 30 seconds.

Answer 1

What are you using to set the timeout in your connection string? From memory that's "ConnectionTimeout" and only affects the time it takes to actually connect to the server.

Each individual command has a separate "CommandTimeout" which would be what you're looking for. Not sure how SqlHelper implements that though.

Answer 2

In addition to timeout in connection string, try using the timeout property of the SQL command. Below is a C# sample, using the SqlCommand class. Its equivalent should be applicable to what you are using.

SqlCommand command = new SqlCommand(sqlQuery, _Database.Connection);
command.CommandTimeout = 0;
int rows = command.ExecuteNonQuery();

Answer 3

Thank you both. Setting CommandTimeout solved the problem. Now we have the problem of re-engineering our ORM to expose that property :)

Its a bit strange though, as changing for the connection timeout, i'm sure has worked before, for certain long-running selects and stored procedures. According to what you are saying, that should have almost no affect.