SQL Server try-catch inner exception message conundrum
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;
So it seems after much searching that there is no solution to this problem. Hopefully they will fix this in a future version.
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
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).