September 16, 2005 at 8:19 am
Hi all,
I’m having trouble with the following code in an SP.
Declare @Timeout Int
Select @Timeout = @@Lock_Timeout
Set LOCK_TIMEOUT -1
--Some code goes here
Set LOCK_TIMEOUT @Timeout
Basically we want to turn off the timeout, do our processing, then change it back to whatever the customer has set for their server.
I can set the timeout if we use a number eg Set LOCK_TIMEOUT 1000, but not by using a variable.
Is there a way to change the LOCK_TIMEOUT just for the life of this SP call, and have it automatically revert to it’s previous value?
Any help is greatly appreciated.
September 16, 2005 at 8:38 am
From what I have seen Timeout is a connection setting.
You have to set the connection string in the application to have a longer timeout.
The reality of it is the sql statement continues to process after the application has issued a timeout.
September 16, 2005 at 9:28 am
Thanks for the reply.
But I've been asked to change the Timeout within the SP rather than for the connection etc...
September 16, 2005 at 9:52 am
It's different between Connection time out and lock time. Connection time out is to control the maximum time that a client can wait for the response. If the time is expired the connectivity (e.g ADO.net) will return an error. The time out may or may not caused by blocking. e.g. caused by long transaction. Lock time just controls how long to wait for a locked resource to release.
September 16, 2005 at 1:29 pm
If you *really* need such functionality I would execute the code in a "separeated connection" like an SQL Agent Job, DTS, ROWSET functions or sp_OA* procedure calls
* Noel
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply