0

I want to rollback a transaction after finding an error in my SP, so I found here, the TRANSACTION block should go inside the TRY block.

However, when I run that example I am getting:

Msg 6401, Level 16, State 1, Line 16
Cannot roll back MYTRAN. No transaction or savepoint of that name was found.

I wonder if that approach is correct or is backward, I mean the TRY block should go inside the TRANSACTION block.

Edit

I am running a very similar query used in the link above.

But just in case the code is

BEGIN TRY
        BEGIN TRANSACTION MYTRAN; -- Give the transaction a name

        SELECT 1/0  -- Generates divide by zero error causing control to jump into catch

        PRINT '>> COMMITING'
        COMMIT TRANSACTION MYTRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN 
            PRINT '>> ROLLING BACK'
            ROLLBACK TRANSACTION MYTRAN; -- The semi-colon is required (at least in SQL 2012)

            SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
        END
    END CATCH
Jamo
  • 494
  • 5
  • 24
  • You need to show your code. We can't guess why you're getting this error. – Aaron Bertrand Feb 27 '18 at 00:43
  • Updated. I hope that helps. – Jamo Feb 27 '18 at 03:23
  • The error suggests you are running the code in an inner (already started) transaction. The transaction name on the `ROLLBACK` must refer to the outmost transaction. It is unclear it that happened by mistake due to previous work in the same session or if this code is only part of what a larger body of work. Why use the transaction name at all? If all you want to do is rollback this transaction on failure, remove the transaction name. – Dan Guzman Feb 27 '18 at 04:06
  • Works! All three examples, I saw in the link provided, had a name for the transaction this is why I used! – Jamo Feb 27 '18 at 04:21

0 Answers0