Rollback Logic Isn't Working

Question

Rollback Logic Isn't Working

Phil Parkin

SSC Guru

Points: 247036
More actions
February 3, 2025 at 8:07 pm
Answer
#4529017
I said 'add another THROW', not 'add another THROW 51000, '2. One or more stored procedures had errors, please investigate', 1;'!
Also, surely you want the INSERT to dbo.RunTimeErrors to occur only if the fail count is greater than zero?
```
BEGIN CATCH
    IF XACT_STATE () <> 0
        ROLLBACK TRANSACTION;

    INSERT dbo.RunTimeErrors
    SELECT *
    FROM @RUN_TIME_ERRORS;

    THROW;
END CATCH;
```
- This reply was modified 1 week ago by Phil Parkin.

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247036 More actions · Answer 1

I swapped things around a bit. Obviously, I cannot test it.

ALTER PROCEDURE dbo.RunStoredProcedures
AS
SET NOCOUNT ON;

DECLARE @FAIL_COUNT INT = 0;

BEGIN TRANSACTION;

BEGIN TRY
    BEGIN TRY
        TRUNCATE TABLE dbo.temptable;
    END TRY
    BEGIN CATCH
        SET @FAIL_COUNT = @FAIL_COUNT + 1;
    END CATCH;

    BEGIN TRY
        EXEC dbo.ClearProcessingDates; -- has 1/0 error
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.RunTimeErrors
        VALUES
        (GETDATE (), ERROR_NUMBER (), ERROR_SEVERITY (), ERROR_STATE (), ERROR_PROCEDURE (), ERROR_LINE ()
        ,ERROR_MESSAGE ());

        SET @FAIL_COUNT = @FAIL_COUNT + 1;
    END CATCH;

    IF @FAIL_COUNT > 0
        THROW 51000, 'One or more procs had errors, please investigate', 1;
    ELSE
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE () <> 0
        ROLLBACK TRANSACTION;
END CATCH;

water490 Ten Centuries Points: 1014 More actions · Answer 2

Phil Parkin wrote:

I swapped things around a bit. Obviously, I cannot test it.

ALTER PROCEDURE dbo.RunStoredProcedures
AS
SET NOCOUNT ON;

DECLARE @FAIL_COUNT INT = 0;

BEGIN TRANSACTION;

BEGIN TRY
    BEGIN TRY
        TRUNCATE TABLE dbo.temptable;
    END TRY
    BEGIN CATCH
        SET @FAIL_COUNT = @FAIL_COUNT + 1;
    END CATCH;

    BEGIN TRY
        EXEC dbo.ClearProcessingDates; -- has 1/0 error
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.RunTimeErrors
        VALUES
        (GETDATE (), ERROR_NUMBER (), ERROR_SEVERITY (), ERROR_STATE (), ERROR_PROCEDURE (), ERROR_LINE ()
        ,ERROR_MESSAGE ());

        SET @FAIL_COUNT = @FAIL_COUNT + 1;
    END CATCH;

    IF @FAIL_COUNT > 0
        THROW 51000, 'One or more procs had errors, please investigate', 1;
    ELSE
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE () <> 0
        ROLLBACK TRANSACTION;
END CATCH;

Thank you for this. It didn't work. Here is what happened:

There is a 1/0 error in the SP mentioned. Nothing was logged in DBO.RunTimeErrors.
The throw message didn't appear in the SSIS output window

What do you suggest to address this?

Phil Parkin SSC Guru Points: 247036 More actions · Answer 3

Instead of logging your errors in a physical table (whose changes are being rolled back, I assume), log them in a table variable and then insert from the table variable to the physical table after doing the rollback, in the final CATCH block.

water490 Ten Centuries Points: 1014 More actions · Answer 4

Phil Parkin wrote:

Instead of logging your errors in a physical table (whose changes are being rolled back, I assume), log them in a table variable and then insert from the table variable to the physical table after doing the rollback, in the final CATCH block.

I see what you mean. I have 20 SP that need to run in that main SP. Would I need 20 separate set of variables (ie 1 set for each SP)?

Also, the THROW message didn't appear in the SSIS output window. Does that mean that block of code didn't run?

This reply was modified 1 week, 1 day ago by water490.

Phil Parkin SSC Guru Points: 247036 More actions · Answer 5

No, only 1 TV is required. You simply run multiple INSERTS to it in your code.

To make the error appear in the SSIS Output window, you need to add THROW again in the final CATCH, after rolling back the transaction.

water490 Ten Centuries Points: 1014 More actions · Answer 6

Phil Parkin wrote:

No, only 1 TV is required. You simply run multiple INSERTS to it in your code.
To make the error appear in the SSIS Output window, you need to add THROW again in the final CATCH, after rolling back the transaction.

Thank you! So far, the rollback logic is working now but I still need to do more extensive testing. Plus the errors are being logged even after rollback. Great to hear!

The part that isn't working is below part:

IF @FAIL_COUNT > 0
THROW 51000, 'One or more stored procedures had errors, please investigate', 1;
ELSE
COMMIT TRANSACTION;

Why isn't the THROW showing anything? The COMMIT works when I remove the 1/0 error from the SP so that tells me that @FAIL_COUNT variable is working.

Phil Parkin SSC Guru Points: 247036 More actions · Answer 7

Did you add another THROW in the final CATCH block, like I suggested?

If yes, please post the code.

water490 Ten Centuries Points: 1014 More actions · Answer 8

Phil Parkin wrote:

Did you add another THROW in the final CATCH block, like I suggested?
If yes, please post the code.

Yes I did. If I add the second THROW then the table variable is not being updated. I am not sure why the first THROW isn't producing anything.

Code:

ALTER procedure [dbo].[RunStoredProcedures]
as

SET NOCOUNT ON;

DECLARE @FAIL_COUNT INT = 0;

DECLARE @RUN_TIME_ERRORS TABLE
(
[ERROR_DATETIME] [datetime] NOT NULL,
[ERROR_PROCEDURE] [nvarchar](128) NOT NULL,
[ERROR_MESSAGE] [nvarchar](4000) NOT NULL,
[ERROR_NUMBER] [int] NOT NULL,
[ERROR_SEVERITY] [int] NOT NULL,
[ERROR_STATE] [int] NOT NULL,
[ERROR_LINE] [int] NOT NULL
);


BEGIN TRANSACTION;

BEGIN TRY

BEGIN TRY
truncate table dbo.testtable;
END TRY
BEGIN CATCH
SET @FAIL_COUNT = @FAIL_COUNT + 1;
END CATCH;

BEGIN TRY
EXEC DBO.ClearProcessingDates; -- has 1/0 error
END TRY
BEGIN CATCH
INSERT INTO @RUN_TIME_ERRORS
VALUES
(GETDATE(),ERROR_PROCEDURE(),ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE());
SET @FAIL_COUNT = @FAIL_COUNT + 1;
END CATCH;

IF @FAIL_COUNT > 0
THROW 51000, '1. One or more stored procedures had errors, please investigate', 1;
ELSE
COMMIT TRANSACTION;
END TRY

BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW 51000, '2. One or more stored procedures had errors, please investigate', 1;
END CATCH;

INSERT INTO DBO.RunTimeErrors
SELECT*
FROM @RUN_TIME_ERRORS

SSIS output:

Screenshot 2025-02-03 094849

Thank you

water490 Ten Centuries Points: 1014 More actions · Answer 9

Phil Parkin wrote:

I said 'add another THROW', not 'add another THROW 51000, '2. One or more stored procedures had errors, please investigate', 1;'!
Also, surely you want the INSERT to dbo.RunTimeErrors to occur only if the fail count is greater than zero?
BEGIN CATCH
    IF XACT_STATE () <> 0
        ROLLBACK TRANSACTION;

    INSERT dbo.RunTimeErrors
    SELECT *
    FROM @RUN_TIME_ERRORS;

    THROW;
END CATCH;

Sorry for the misunderstanding. It works now! Thank you so much for your help!!!