Rollback Logic Isn't Working

Question

Rollback Logic Isn't Working

water490

Ten Centuries

Points: 1015
More actions
February 1, 2025 at 2:55 am
Go to Answer
#4527203
Hi Everyone
I am not sure how to do this correctly. The below code "looks" right but it didn't rollback the code. I put 'select 1/0' to guarantee a failure to test the rollback logic. In production, I won't be using this. Instead, it will be a bunch of exec SP statements. How do I fix it?
Code:
```
ALTER procedure [dbo].[RunStoredProcedures]
as

BEGIN TRANSACTION

truncate table dbo.testtable
select 1/0

COMMIT TRANSACTION
```
Thank you

Viewing 15 posts - 1 through 15 (of 24 total)

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

water490 Ten Centuries Points: 1015 More actions · Answer 1

I made some progress. New code:

ALTER procedure [dbo].[RunStoredProcedures]
as

BEGIN TRANSACTION

BEGIN TRY
truncate table dbo.testtable
select 1/0
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR('Rollback of stored procedures was successful', 10, 1) WITH NOWAIT;
END CATCH

IF @@TRANCOUNT > 0 
COMMIT TRANSACTION

I have a few questions:

Why is the output looking like this? I am not sure what it means. Why is there "no column name" appearing? Also, what does the -6 mean?

Screenshot 2025-01-31 202538

2. The SP is going to be run in SSIS. I need to know if a rollback has occurred or not. I thought that adding "RAISERROR('Rollback of stored procedures was successful', 10, 1) WITH NOWAIT;" would work but it doesn't display anything in SS output window (see above screenshot) plus nothing in the SSIS Output Window. What is the best way to output a message to the output window in SSIS?

Thank you

Jonathan AC Roberts SSCoach Points: 18391 More actions · Answer 2

water490 wrote:

I made some progress. New code:
ALTER procedure [dbo].[RunStoredProcedures]
as

BEGIN TRANSACTION

BEGIN TRY
truncate table dbo.testtable
select 1/0
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR('Rollback of stored procedures was successful', 10, 1) WITH NOWAIT;
END CATCH

IF @@TRANCOUNT > 0 
COMMIT TRANSACTION
I have a few questions:
Why is the output looking like this? I am not sure what it means. Why is there "no column name" appearing? Also, what does the -6 mean?
2. The SP is going to be run in SSIS. I need to know if a rollback has occurred or not. I thought that adding "RAISERROR('Rollback of stored procedures was successful', 10, 1) WITH NOWAIT;" would work but it doesn't display anything in SS output window (see above screenshot) plus nothing in the SSIS Output Window. What is the best way to output a message to the output window in SSIS?
Thank you

You can get a column name if you change the code to: select 1/0 as myCol

You need to look in the messages tab to see your rollback message.

Not sure why you are getting results from an additional query (Return Value] = -6), is there some code you haven't shown us?

Jonathan AC Roberts SSCoach Points: 18391 More actions · Answer 3

.

This reply was modified 1 month ago by Jonathan AC Roberts. Reason: deleted, duplicate

water490 Ten Centuries Points: 1015 More actions · Answer 4

Thanks for the reply. That is the entire code. Not sure where/how it got -6. Any ideas on how it got there?

water490 Ten Centuries Points: 1015 More actions · Answer 5

Actually...the above two outputs are not that important. The SP is run in SSIS so I never see the above two issues. Ok for now.

The more pressing issue is that there is no output shown in SSIS output if there is a rollback. The rollback logic does work but nothing gets displayed. How can I amend my code so a message is shown when there is a rollback?

Jonathan AC Roberts SSCoach Points: 18391 More actions · Answer 6

water490 wrote:

Actually...the above two outputs are not that important. The SP is run in SSIS so I never see the above two issues. Ok for now.
The more pressing issue is that there is no output shown in SSIS output if there is a rollback. The rollback logic does work but nothing gets displayed. How can I amend my code so a message is shown when there is a rollback?

Try writing your catch statement like this:

BEGIN CATCH
    IF @@TRANCOUNT > 0;
        ROLLBACK TRANSACTION;
    THROW;
END CATCH

The addition of THROW; will just throw the error back to the SSIS package.

water490 Ten Centuries Points: 1015 More actions · Answer 7

Jonathan AC Roberts wrote:

water490 wrote:
Actually...the above two outputs are not that important. The SP is run in SSIS so I never see the above two issues. Ok for now.
The more pressing issue is that there is no output shown in SSIS output if there is a rollback. The rollback logic does work but nothing gets displayed. How can I amend my code so a message is shown when there is a rollback?
Try writing your catch statement like this:
BEGIN CATCH
    IF @@TRANCOUNT > 0;
        ROLLBACK TRANSACTION;
    THROW;
END CATCH
The addition of THROW; will just throw the error back to the SSIS package.

Thank for this. The SSIS output window now shows the reason why the SP failed. This is great. In addition to the default Sql Server error messages, I also want to add an additional custom message confirming that the roll back was successful. How can I add this custom message? I still want to keep all the default SS errors b/c those are super helpful for debugging purposes.

Phil Parkin SSC Guru Points: 247084 More actions · Answer 8

-- Reply deleted

This reply was modified 1 month ago by Phil Parkin.

Jonathan AC Roberts SSCoach Points: 18391 More actions · Answer 9

water490 wrote:

Jonathan AC Roberts wrote:
water490 wrote:
Actually...the above two outputs are not that important. The SP is run in SSIS so I never see the above two issues. Ok for now.
The more pressing issue is that there is no output shown in SSIS output if there is a rollback. The rollback logic does work but nothing gets displayed. How can I amend my code so a message is shown when there is a rollback?
Try writing your catch statement like this:
BEGIN CATCH
    IF @@TRANCOUNT > 0;
        ROLLBACK TRANSACTION;
    THROW;
END CATCH
The addition of THROW; will just throw the error back to the SSIS package.
Thank for this. The SSIS output window now shows the reason why the SP failed. This is great. In addition to the default Sql Server error messages, I also want to add an additional custom message confirming that the roll back was successful. How can I add this custom message? I still want to keep all the default SS errors b/c those are super helpful for debugging purposes.

I think you can add a parameter to the stored procedure with OUTPUT

ALTER PROCEDURE [dbo].[RunStoredProcedures]
    @OutputString NVARCHAR(255) OUTPUT  -- Added OUTPUT parameter
AS
BEGIN
    SET NOCOUNT ON;    

    BEGIN CATCH
        IF @@TRANCOUNT > 0;
            ROLLBACK TRANSACTION;
        -- Set the value of the output parameter
        SET @OutputString = 'Stored procedure rolled back';
        THROW;
    END CATCH
END;

In SSIS to call it use a Execute SQL Task :

EXEC [dbo].[RunStoredProcedures] @OutputString = ?

In the Parameter Mapping tab:

Click Add to create a new parameter.
Set Variable Name: Create or select an SSIS string variable (e.g., User::OutputVar).
Set Direction: Output
Set Data Type: NVARCHAR
Set Parameter Name: 0 (since SSIS uses ordinal positions for parameters)
Set Size: 255 (or the appropriate size of the output string)

Use the Output Variable in SSIS

Now, the SSIS variable (User::OutputVar) will hold the value returned from the stored procedure.

Alternatively you could just throw a user defined error in the catch on rollback:

BEGIN CATCH
    IF @@TRANCOUNT > 0 BEGIN
        -- Rollback the transaction
        ROLLBACK TRANSACTION;
        THROW 50001, 'The stored procedure was successfully rolled back.', 1;
    END ELSE BEGIN
        -- If no active transaction, re-throw the original error
        THROW;
    END
END CATCH;

water490 Ten Centuries Points: 1015 More actions · Answer 10

Thank you!

I have some questions:

1. re the output parameter option solution. Will that message show up in the output window?

2. re the alternative solution. I am getting an error message when I code it exactly as you have done:

Screenshot 2025-02-02 141030

How do I fix this?

Jonathan AC Roberts SSCoach Points: 18391 More actions · Answer 11

water490 wrote:

Thank you!
I have some questions:
1. re the output parameter option solution. Will that message show up in the output window?
2. re the alternative solution. I am getting an error message when I code it exactly as you have done:
How do I fix this?

Regarding the output parameter, this fills a variable that you can use in SSIS for whatever you want.

Apologies, you need a semicolon before a THROW (after the BEGIN):

BEGIN CATCH
    IF @@TRANCOUNT > 0 BEGIN
        -- Rollback the transaction
        ROLLBACK TRANSACTION;
        THROW 50001, 'The stored procedure was successfully rolled back.', 1;
    END ELSE BEGIN;
        -- If no active transaction, re-throw the original error
        THROW;
    END
END CATCH;

water490 Ten Centuries Points: 1015 More actions · Answer 12

deleted reply

This reply was modified 1 month ago by water490.

water490 Ten Centuries Points: 1015 More actions · Answer 13

I have been thinking more about this. I don't think relying on SSIS output window is the right way to capture errors. I need a persistent record of errors (not sure if SSIS output window is persistent or not). I came across an article online that gave me an idea:

https://stackoverflow.com/questions/13647437/how-to-get-sql-error-in-stored-procedure

I created a table to store errors. It has the name of the procedure which I was initially after. The script doesn't quite work as expected. The main SP is RunStoredProcedures. This SP will run other SP. If any of the SP fail then ALL have to be rolled back. It is an all or nothing approach. Either all run successfully or none do. Here is my script thus far. The rollback isn't working. The truncate command should have been undone but it wasn't. Even though it had no errors in it but because ClearProcessingDates had an error then both commands need to be undone. My example only has 2 commands as an example but there will be close to 20 SP when the script is finished. How can I get this to work?

Code:

ALTER procedure [dbo].[RunStoredProcedures]
as

BEGIN TRANSACTION

BEGIN TRY
   truncate table dbo.testtable
BEGIN TRY
    EXEC DBO.ClearProcessingDates
END TRY
BEGIN CATCH
   INSERT INTO DBO.RunTimeErrors
   VALUES(GETDATE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE())
END CATCH
END TRY

BEGIN CATCH
   IF @@TRANCOUNT > 0 BEGIN
     ROLLBACK TRANSACTION;
   THROW;
  END 
END CATCH;

IF @@TRANCOUNT > 0 
   COMMIT TRANSACTION

This reply was modified 1 month ago by water490.
This reply was modified 1 month ago by water490.
This reply was modified 1 month ago by water490.
This reply was modified 1 month ago by water490.

water490 Ten Centuries Points: 1015 More actions · Answer 14

I made some progress on this.

Code:

ALTER procedure [dbo].[RunStoredProcedures]
as

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

END TRY

BEGIN CATCH
IF @FAIL_COUNT > 0
  ROLLBACK TRANSACTION
END CATCH

IF @FAIL_COUNT = 0 
  COMMIT TRANSACTION

Error:

Error: 0xC002F210 at RunStoredProcedures, Execute SQL Task: Executing the query "EXEC dbo.RunStoredProcedures" failed with the following error: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: RunStoredProcedures

What is wrong with my code?

Thank you

Rollback Logic Isn't Working

Cookies on SQLServerCentral