July 9, 2012 at 7:35 am
According to BOL, if you use a SET option (e.g. SET LOCK_TIMEOUT) inside an SP, this value returns to its previous one once the SP call is finished.
e.g.
SET LOCK_TIMEOUT 10
call SP which sets lock_timeout to 999
SELECT @@Lock_Timout
would return 10, the value of 999 being "lost" once the SP call is finished.
Does anyone know whether it's possible to override that behaviour, i.e. persist the value of 999 after the SP call has finished?
I know there's probably a bazillion reasons why it's a bad idea, but for now I'm just interested in whether it's possible or not 🙂
July 9, 2012 at 11:06 am
It is not about it being in an SP, it is because the connection is dropped and SET options are connection specific. So... No, you cannot have this persist outside of the sp (connection).
Jared
CE - Microsoft
July 9, 2012 at 3:36 pm
SQL Server explicitly discards SET values at the end of the stored proc and, AFAIK, there is absolutely no way to change that directly.
You could, of course, save the desired SET value(s) somewhere and retrieve them after the sp finished. For example, in CONTEXT_INFO, which will retain a sp setting even after the sp terminates.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply