September 22, 2015 at 10:45 am
I have some code that I'd ideally like to run using a TRY / CATCH setup, something like the following:
BEGIN TRY
UPDATE X
SET X.somefield = 'somevalue'
FROM dbo.MyTable AS X;
--... more occurrences of similar update code
END TRY
BEGIN CATCH
ROLLBACK;
END CATCH
Will the ROLLBACK work by rolling back everything that had succeeded so far before an error was encountered? Or do I need an explicit transaction for this? I searched online, but wasn't able to find any examples or explanations that I felt covered this scenario. My search wasn't exactly exhaustive, but I don't have the time to spend a day researching this...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2015 at 12:40 pm
This recent thread may help
http://www.sqlservercentral.com/Forums/Topic1721408-392-1.aspx
September 22, 2015 at 12:44 pm
You would need an explicit transaction. In fact, the ROLLBACK itself will give you an error unless a transaction is active, so even with an explicit trans, you need to check in your CATCH block before just issuing a ROLLBACK:
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
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".
September 22, 2015 at 1:17 pm
Thanks Kristen and Scott. I now have the information I need. Based on what we now know, and some other things that have changed in the interim, my work requirement is going to need to avoid the explicit transaction anyway, due primarily to locking and volume considerations, but this is definitely good to know. Thanks again!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply