Why Does SQL Server Keep Executing After Raiserror When Xact_Abort Is On

What happens here? SQL Server - XACT_ABORT ON + @@ERROR Checking . .

Correct.

SET XACT_ABORT jumps out of the batch. Your IF is part of the same batch.

If you want some processing on error, then use BEGIN TRY etc

SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION

    ---DO SOMETHING HERE THAT CAUSES AN ERROR

COMMIT TRANSACTION
END TRY
BEGIN CATCH
    raiserror('SP failed. Step 7.', 20, -1) with log
END CATCH
GO

I'm also intrigued by severity 20 because it breaks the connection. Usually you'd use 16 which is user defined error.

Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally?

You can set XACT_ABORT ON as a global default connection setting at the server level, although the command is a bit obscure:

EXEC sys.sp_configure N'user options', N'16384'
GO
RECONFIGURE WITH OVERRIDE
GO

See here for details.

The option can also be set through SSMS Object Explorer > Server Properties > Connections:

SQL Server XACT_ABORT with exclusion

OK... I was able to solve this using a combination of the great suggestions put forth by Alex and GameisWar, with the addition of the T-SQL GOTO control flow statement.

The basic ideas was to store the error message in a variable, which survives a rollback, then have the Catch send you to a FAILURE label which will do the following:

• Rollback the transaction
• Insert a record into the log table, using the data from the aforementioned variable
• Exit the stored procedure

I also use a second GOTO statement to make sure that a successful run will skip over the FAILURE section and commit the transaction.

Below is a code snippet of what the test SQL looked like. It worked like a charm, and I have already implemented this and tested it (successfully) in our production environment.

I really appreciate all the help and input!

SET XACT_ABORT ON               
DECLARE @MESSAGE VARCHAR(MAX) = '';

BEGIN TRANSACTION 
    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST');     -- WORKS FINE
    END TRY 
    BEGIN CATCH     
        SET @MESSAGE = 'ERROR - SECTION 1: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST2');        --WORKS FINE
        INSERT INTO TEST_TABLE VALUES ('ANOTHER TEST'); -- ERRORS OUT, DATA WOULD BE TRUNCATED
    END TRY 
    BEGIN CATCH 
        SET @MESSAGE = 'ERROR - SECTION 2: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

GOTO SUCCESS;

FAILURE:        
    ROLLBACK
    INSERT INTO LOGG SELECT @MESSAGE
    RETURN; 

SUCCESS:
COMMIT TRANSACTION

What is the benefit of using SET XACT_ABORT ON in a stored procedure?

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFF setting.)

Since a query timeout will leave the transaction open, SET XACT_ABORT ON is recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.

There's a really great overview on Dan Guzman's Blog,

Does SET XACT_ABORT ON do anything in a stored procedure if you're NOT in a transaction?

SET XACT_ABORT ON changes the behavior of statement-terminating errors so that they instead become batch-aborting errors. The batch within which your procedure is executing would be more likely to stop completely when an error was encountered, rather than continuing on to the next T-SQL statement.

This has implications for error-handling. Statement-terminating errors which would normally allow you to continue and execute handling code via a IF @@ERROR <> 0 block within your procedure will not be executed. What's worse, there is no way you can intercept batch-abortion in T-SQL code, so unless there is a check on @@ERROR immediately in a following batch on the same connection, you might not know there was a problem.

Perhaps your INSERT is failing quietly in some way that still allows the IDENTITYseed to be incremented. This is not uncommon...identity values reside in the SQL Server memory cache, the volatility of which provides no guarantee that those values be continuous and without gaps.

Alternatively, the calling context of the stored procedure matters. If called from within an outer-scope transaction, either initiated by SQL Server or at the application level, then a rollback at that outer scope will rollback the inner scope work, regardless of explicit transaction handling code at the inner scope. Again, the IDENTITY seed would be incremented.

TRY/CATCH blocks (available since SQL Server 2005) obviate the need for SET XACT_ABORT ON.

---

Related Topics