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.

Answer 1

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).