Hello, I wrote a pretty complex Stored Procedure that takes about 2 minutes to run (Is is a single Update statement). However, in this 2 minutes of time the stored procedure times out. I was wondering if there was a way to refresh the Stored Procedures timeout expiration so that I don't have to change the servers timeout from 30 seconds. I cannot really break the stored proc up any more than it already is so running multiple smaller versions of the stored proc is kind of off the table. It would be nice to say somewhere in the stored procedure like every time the stored proc successfully updates a row, refresh the timer so that it can update all the rows without timing out. Any advice would be appreciated. Thanks.
views:
23answers:
1
+2
A:
No. The command time out is purely a client side thing.
What happens with CommandTimeout is, SQL Server will just chug along performing a query and doesn't know anything about a timeout, and when the client determines that the query is taking too long, it will then send a message to the server telling it to cancel the query. So, since the timeout value is purely a client-side thing, you won't be able to change it inside SQL Server.
You would need to increase the CommandTimeout in the calling code.
Martin Smith
2010-08-11 16:47:36
I see, that is unfortunate. Thanks for the reply.
Sam F
2010-08-11 16:50:03
@SamF - How are you calling it? Can you increase the timeout? If not can you call it asynchronously?
Martin Smith
2010-08-11 16:51:11
Ah I see, I can set the Timeout value myself before and then set it back to the original one afterwards (.NET)
Sam F
2010-08-11 16:54:17
@Sam F - Yes. In `.NET` it is the `SqlCommand.CommandTimeout` property that you need.
Martin Smith
2010-08-11 16:56:35
Thanks for advice.
Sam F
2010-08-11 17:03:19