3

It looks like XACT_ABORT ON is not working as expected. Here are a table and a procedure that inserts into it:

CREATE TABLE rain
    (
      rain_time DATETIME ,
      location VARCHAR(100)
    );
GO

CREATE PROCEDURE insert_rain
    @rain_time DATETIME ,
    @location VARCHAR(100)
AS
    BEGIN;
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;
        PRINT 'before insert';
        INSERT  INTO rain
                ( rain_time, location )
        VALUES  ( @rain_time, @location );
        PRINT 'after insert';
        COMMIT;
    END;
GO

I've created a trigger to imitate a runtime error:

CREATE TRIGGER rain_no_insert
ON rain
FOR INSERT 
AS
RAISERROR('Cannot insert', 16, 1);
GO

When I invoke the insert procedure, I do get the error, but the execution does not stop, because the last PRINT prints its 'after insert' message:

EXEC insert_rain
    @rain_time = '2015-03-30 12:34:56',
    @location = 'Wautoma, WI';

before insert
Msg 50000, Level 16, State 1, Procedure rain_no_insert, Line 5
Cannot insert

(1 row(s) affected)
after insert

Also the transaction commits:

SELECT * FROM dbo.rain;

rain_time               location
----------------------- ---------------------------------
2015-03-30 12:34:56.000 Wautoma, WI

Why my XACT_ABORT does not abort?

  • Good edit, since XACT_ABORT is not documented as working with RAISERROR. – Aaron Bertrand Mar 30 '15 at 18:29
6

Check MS documentation. Specifically the top line:

The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR.

I believe that is your problem, right there.

https://msdn.microsoft.com/en-us/library/ms188792.aspx

  • Yes, THROW worked. Can you explain how the fact that RAISERROR does not honor SET XACT_ABORT makes SQL Server a better product? – AlexC Mar 30 '15 at 18:23
  • I couldn't tell you with absolute certainty. Someone else may be able to shine light on that. I can tell you there was a connect item that was closed as won't fix. connect.microsoft.com/SQLServer/feedback/details/275308/… – Kris Gruttemeyer Mar 30 '15 at 18:26
  • 1
    @AlexC Why do you think RAISERROR is supposed to make SQL Server a better product? Who suggested that in any way? It has to work the way it does because millions of lines of code have been written over the past 20 years that rely on the current behavior, and they need to continue to work even though you don't like the behavior you've only just now stumbled upon. This is the whole reason they introduced THROW and are recommending you use THROW instead of RAISERROR. They can't just "fix" RAISERROR because fixing it for you breaks it for everyone else in the world. – Aaron Bertrand Mar 30 '15 at 18:26
  • @AaronBertrand Sometimes some features are added to improve users' experience, to make the product better. Of course, there may be other reasons for adding features ;). – AlexC Mar 30 '15 at 18:41
  • @AlexC RAISERROR has been there since long before I've ever used SQL Server, and it really hasn't changed much. They have added THROW to improve users' experience, and they initially created RAISERROR also to improve users' experience. The fact that it doesn't work well with XACT_ABORT is inconvenient for you, but do you think that affects all users of SQL Server? – Aaron Bertrand Mar 30 '15 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.