connection timeout

[C# beginner]

I'm struggling with a timeout problem today.
ConnectionString = "Integrated
Security=true;Database=MyDatabase;Data Source=(local);
Connection Timeout = 180;";

My connection string is set as above. A stored proc in my
code takes about 50 secs. to execute and return. But, I
get a connection time out error as follows.

SqlException occurred when trying to execute stored proc
sp_DataSubs_NOxFlowParams. Timeout expired. The timeout
period elapsed prior to completion of the operation or the
server is not responding.

Can someone help me with this? Thanks a lot in advance.
P.S. I am positive that the same piece of code worked
correctly a couple weeks back in my box. What could have
changed? I'm puzzled.

 

[AlexS]

Hi, C# beginner

such questions are having better chance to be answered in
microsoft.public.sqlserver.programming or some similar group.

Anyway, you might need to check if there is timeout defined on your sql
server - if there is one set, it will override connection specified.

HTH
Alex

 

[Guest]

Set the timeout property of your command object (thats excuting the stored procedure) ,not the timeout property of the connection string(which specify how long you want to try to establish a database connection)

----- C# beginner wrote: ----

I'm struggling with a timeout problem today.
ConnectionString = "Integrated
Security=true;Database=MyDatabase;Data Source=(local);
Connection Timeout = 180;"

My connection string is set as above. A stored proc in my
code takes about 50 secs. to execute and return. But, I
get a connection time out error as follows

SqlException occurred when trying to execute stored proc
sp_DataSubs_NOxFlowParams. Timeout expired. The timeout
period elapsed prior to completion of the operation or the
server is not responding

Can someone help me with this? Thanks a lot in advance
P.S. I am positive that the same piece of code worked
correctly a couple weeks back in my box. What could have
changed? I'm puzzled

 

[William Ryan eMVP]

I'd bet it's the command that's timing out not the connection. Have you
verfied that it's definitely a connection timeout? If it was it will happen
on Open vs. ExcecuteNonQuery or whatever else.

You can specify the command timeout too but if it's taking that long, I'd
really look to tune that query and fix that problem, 50 seconds is a good
bit of time.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

 

[Guest]

Thanks a lot Vinny, Alex and William. I understand now
that the problem is really in the CommandTimeout which
defaults to 30 seconds. I am using SqlHelper object from
Microsoft Data Access Application blocks.NET where there
is no easy way of overriding the CommandTimeout value. I
appreciate all your suggestions.

 

[William Ryan eMVP]

I'm not familiar with all of the DAAB but you have the code so you could
change it if you wanted to or just create an overloaded method and recompile
it.

Nonetheless the 'real' solution usually entails tuning those queries and
getting down with the execution plan.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

 

[Kevin C]

There is a known bug with the Data access block. In the PrepareCommand
function of the SqlHelper class you have to set the timeout on the command
object. With the version out of the box, the settings are not "passed down"
correctly.
private static void PrepareCommand(SqlCommand command, SqlConnection
connection, SqlTransaction transaction, CommandType commandType, string
commandText, SqlParameter[] commandParameters, out bool
mustCloseConnection )
{
..... (code)

command.CommandTimeout = connection.ConnectionTimeout;

.... (more code)
}

 

[Reinout Hillmann [MS]]

Does changing "Integrated Security=true" to "Integrated Security=SSPI" help?

Reinout Hillmann
SQL Server Product Unit