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;