Sample Header Ad - 728x90

Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?

1 vote
1 answer
157 views
Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond... > "Msg 6401, Level 16, State 1 ... Cannot roll back SavePoint. No transaction or savepoint of that name was found." ...but I do not get such errors when SAVE TRAN SavePoint; is at location (B)? I can only imagine it's something to do with the context of the cursor loop, but I don't understand why. ``` -- Premable stuff, opening cursor, etc. here... BEGIN TRAN SAVE TRAN SavePoint; -- (A) WHILE @@FETCH_STATUS = 0 BEGIN; -- SAVE TRAN SavePoint; -- (B) BEGIN TRY -- UPDATE statement here which may fail SAVE TRAN SavePoint; END TRY BEGIN CATCH ROLLBACK TRAN SavePoint; END CATCH; FETCH NEXT FROM Cursor INTO @X, @Y, @Z; END; -- CLOSE & DEALLOCATE stuff here IF @DryRun = 1 ROLLBACK TRAN; ELSE COMMIT TRAN;
Asked by Chris Marriott (13 rep)
Nov 20, 2023, 11:52 AM
Last activity: Nov 20, 2023, 05:19 PM