SQL Server try-catch inner exception message conundrum

Question

The following SQL statement below is a good example of some SQL that throws an exception with nested details. It seems in the catch part of the statement I can only get outer exception details Could not create constraint. See previous errors (not very useful!). What I want to get is the inner exception message:

Introducing FOREIGN KEY constraint 'FK_TWO' on table 'TABLE2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints (you can get this message by running the code without try-catch).

In the Catch block, how can this be achieved in T-SQL?

BEGIN TRY
      BEGIN TRAN;

        CREATE TABLE TABLE1 (USER_ID INTEGER NOT NULL PRIMARY KEY, USER_NAME
            CHAR(50) NOT NULL);

        CREATE TABLE TABLE2 (AUTHOR_ID INTEGER NOT NULL PRIMARY KEY, AUTHOR_NAME
            CHAR(50) NOT NULL, LASTMODIFIEDBY INTEGER NOT NULL, ADDEDBY INTEGER NOT
            NULL);

        ALTER TABLE TABLE2 ADD CONSTRAINT FK_ONE FOREIGN KEY (LASTMODIFIEDBY)
            REFERENCES TABLE1 (USER_ID) ON DELETE CASCADE ON UPDATE CASCADE;

        ALTER TABLE TABLE2 ADD CONSTRAINT FK_TWO FOREIGN KEY (ADDEDBY)
            REFERENCES TABLE1(USER_ID) ON DELETE NO ACTION ON UPDATE CASCADE;   

        COMMIT TRAN;
END TRY
BEGIN CATCH

      DECLARE @ERROR_MSG NVARCHAR(MAX), @SEVERITY INT, @STATE INT
      SELECT @SEVERITY = ERROR_SEVERITY(), @STATE = ERROR_STATE()
            , @ERROR_MSG = ERROR_MESSAGE() + ' err src line: ' + CAST( ERROR_LINE() AS NVARCHAR(20)) + ' ' + ISNULL(ERROR_PROCEDURE(), ''); 

      ROLLBACK;
      -- RE-THROW EXCEPTION FOR DIAGNOSTIC VISIBILITY
      RAISERROR (@ERROR_MSG ,@SEVERITY, @STATE); 
END CATCH;

[Edit]

So it seems after much searching that there is no solution to this problem. Hopefully they will fix this in a future version.

I don't think there's any way to do this. See also this related question: Capturing multiple error messages from a single statement — Joe Stefanelli
– Joe Stefanelli, Commented Dec 3, 2010 at 21:26

Remus Rusanu · Accepted Answer · 2010-12-03 22:36:27Z

You cannot re-throw the original error. You have to throw a new error, with error number above 50000, that contains the captured error message. See Exception handling and nested transactions for an example:

begin catch
    declare @error int, @message varchar(4000), @xstate int;
    select @error = ERROR_NUMBER()
        , @message = ERROR_MESSAGE();
raiserror ('Caught exception: %d: %s', 16, 1, @error, @message) ;
return;
end catch

The article I linked has a more thorough example, covering also the mandatory XACT_STATE() check and mixing try/catch blocks with transaction semantics.

With the next version ("Denali") this problem is fixed, as you have the ability to issue a throw; w/o any argument, that will raise the original exception like in other try/catch languages. See TRY CATCH THROW: Error handling changes in T-SQL

Updated

D'oh, I sort of read the post in diagonal. If there are more exception raised, you can only catch one. That is still true, with Denali too. But most times the exceptions raised are severity 0 (meaning they're really prints, not exceptions) and those still make it to the client as informational messages (SqlConnection.InfoMessage events).

James · Accepted Answer · 2010-12-07 18:53:02Z

1

There is no solution to this problem now. According to this the solution in the next version of SQL Server will be to use the new throw keyword which will re-raise both errors.

answered Dec 7, 2010 at 18:53

James

12.8k12 gold badges70 silver badges105 bronze badges

1 Comment

Martin Smith Over a year ago

There might be a solution if you are willing to mess about parsing the output buffer to get it to work!

Collectives™ on Stack Overflow

SQL Server try-catch inner exception message conundrum

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related