ROLLBACK TRANSACTION without the corresponding BEGIN TRANSACTION

1

I have the following problem. I have a procedure in SQL Server 2008 that has a Cursor. The actions that are executed within the stored procedure are within a transaction to cancel it in case of error, because if only one of them fails, I must cancel the entire process. When the execution is correct there is no problem but when it fails I receive the following message:

  

The ROLLBACK TRANSACTION request does not have the corresponding BEGIN   TRANSACTION.

I've tried putting BEGIN TRANSACTION before BEGIN TRY but the error is the same.

DECLARE Lineas_Cursor CURSOR FOR   
SELECT .....

OPEN Lineas_Cursor  

    BEGIN TRY
        BEGIN TRANSACTION 
        FETCH NEXT FROM Lineas_Cursor   
        INTO .......

        WHILE @@FETCH_STATUS = 0  
        BEGIN  

            --Actualizo
            UPDATE ......

            FETCH NEXT FROM Lineas_Cursor   
            INTO .....
        END   
    COMMIT TRANSACTION 
END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

    ROLLBACK TRANSACTION                
END CATCH

CLOSE Lineas_Cursor;  
DEALLOCATE Lineas_Cursor;  
    
asked by Jaime Capilla 30.11.2017 в 10:03
source

1 answer

2

The reason for this error is that it is possible that there is no active transaction at the moment when you try to execute the ROLLBACK . The usual solution is to check if a transaction exists before trying to execute it:

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; 
    
answered by 30.11.2017 / 15:39
source