views:

403

answers:

4

I am getting sqlcommand timeout issues when I debug the application even though the stored procedure runs in less than 25 seconds in management studio. I set the timeout attribute to 180 seconds and still get the error. Any suggestions?

A: 

Where did you set the timeout attribute at? I used to run into the same issue when I was setting the timeout in the SqlConnection string, but it turns out I also needed it in the SqlCommand itself.

JustLoren
A: 

25 seconds is a long time for a stored proc to be running. I'd suggest optimizing the query further.

Austin Salonen
A: 

Are you setting "Connect Timeout" or "Command Timeout" in connection string? It's easy to do mistakes with them.

--EDIT

Try searching in your code if your system isn't locking a table used in the SP.

It won't solve the problem, but logging the start/end of the procedure (on database side, by putting the SP inside another SP) can tell you if the problem is before starting the SP (because of network, webserver load, etc) or while executing it.

I hope it helps

mkato
CommandTimeout in the code, not in the connection string. I don't understand why a sp that can run in 1-25 secs in management studio can take over 2.5 - 4 minutes in the application. Is there anything you can suggest? BTW, I did not write the sp and cannot alter it.
A: 

Try running a sp_recompile against that stored procedure, then try from your application again.

Josh