Capturing multiple error messages from a single statement inside TRY CATCH

Question

I am running a batch of statements on several columns and tables and want to retrieve information on what errors occur.

The statement is a type change (varchar to nvarchar) and when it fails, it seems to return 2 errors.

Msg 5074, Level 16, State 1, Line 1 The object 'DF_XXX_YYY' is dependent on column 'YYY'.

Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column.

However, when I wrap it in a TRY/CATCH block, and select ERROR_MESSAGE(), it only returns the second error:

ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column.

Ideally I would have it return the first message, as this is much more informative.

The exact SQL statement is:

begin try
    alter table XXX
    alter column YYY
    nvarchar(200)
end try
begin catch
    select ERROR_MESSAGE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE()
end catch

Does anyone know how I can retrieve the more informative message? (@@ERROR also returns the second error)

I plan to run the script on a backed up version of the DB and peruse the resulting errors (of which I suspect there will be many.) How I will proceed after that I'm not yet sure. — david
– david, Commented Sep 13, 2010 at 2:03
Is there any reason you can't just run it in SSMS? Do you need a programatic solution? I don't know if there is a way of doing this. You could just run the script without error handling and use extended events to capture all errors (filtered by your spid of interest). — Martin Smith
– Martin Smith, Commented Sep 13, 2010 at 2:09

Martin Smith · Accepted Answer · 2011-04-24 20:34:35Z

Depending on your needs and the permissions of the account that you are running this script under you may be able to use DBCC OUTPUTBUFFER(@@spid).

I came across this idea when reading Erland Sommarskog's Error Handling article. He links to a procedure spGET_ErrorMessage.

Unfortunately this didn't quite work in my test script on SQL Server 2008 though so I'm not sure if the buffer format has changed but it might get there with a bit of tweaking!

CREATE TABLE #foo
(
c INT DEFAULT(0)
)
ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)

GO
EXEC spGET_LastErrorMessage

Actual Output

Msg 5074, Level 16, State 1, Line 2
The object 'DF__#foo___________c__6DCC4D03' is dependent on column 'c'.

    Msg 4922, Level 16, State 9, Line 2
    ALTER TABLE ALTER COLUMN c failed because one or more objects access this column.

Claimed Output

errNumber            errState    errLevel    errMessage                                                                               errInstance                                                                                                                     errProcedure    errLine
-------------------- ----------- ----------- ---------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------- --------------- -----------
5074                 1           16          The object 'DF__#foo___________c__6DCC4D03' is dependent on column 'c'.                  MARTINHP                                                                                                                        NULL            2
4922                 9           16          The object 'DF__#foo___________c__6DCC4D03' is dependent on column 'c'.ALTER TABL        MARTINHP 䄀䰀吀䔀刀 䌀伀䰀唀䴀一 挀 昀愀椀氀攀搀 戀攀挀愀甀猀攀 漀渀攀 漀爀 洀漀爀攀 漀戀樀攀挀琀猀 愀挀挀攀猀猀 琀栀椀         NULL            117

Davos · Accepted Answer · 2015-03-23 07:16:57Z

3

MikeCov has answered this, but I didn't want to trust the future documentation. The future is now, so I tested this and can confirm that THROW does indeed return all the error messages.

You can reproduce this with the below script. Run each section between the comments one at a time to see the output.

/*Create tables */

CREATE TABLE dbo.test 
(
columna int primary key
)
GO

CREATE TABLE dbo.test2
(
columnb int 
)
GO

/*Create foreign key between these tables*/
ALTER TABLE dbo.test2  WITH CHECK ADD  CONSTRAINT [FK_test_to_test] FOREIGN KEY(columnb)
REFERENCES dbo.test  (columna)
GO
ALTER TABLE dbo.test2 CHECK CONSTRAINT [FK_test_to_test] 
GO

/* TEST 1 - only returns the last error message */
BEGIN TRY
    ALTER TABLE dbo.test 
    ALTER Column columna varchar
END TRY
BEGIN CATCH
    DECLARE  @ERROR_MESSAGE NVARCHAR(2048) = ERROR_MESSAGE()
    RAISERROR (@ERROR_MESSAGE,16,16)
END CATCH       

/* TEST 2 - Returns both messages, YAY */
BEGIN TRY
    ALTER TABLE dbo.test 
    ALTER Column columna varchar
END TRY
BEGIN CATCH
    THROW;
END CATCH       


/* Clean up */
DROP TABLE dbo.test2
DROP TABLE dbo.test

answered Mar 23, 2015 at 7:16

Davos

5,51549 silver badges70 bronze badges

5 Comments

cassandrad Over a year ago

Want to mention that there is possibly a bug in THROW for Sql Server 2012. You can get “Incorrect syntax” message even with semicolon at the end.

Davos Over a year ago

@cassandradied thanks that's a good point. ANSI SQL says you should end every statement with a semicolon. THROW seems to be one of the few things where Microsoft has implemented this strictly. If you are getting errors like that, chances are you need to end the preceeding statement with a semicolon, i.e. the one immediately before the THROW. I've had this problem with IF ELSE blocks that try to THROW.

cassandrad Over a year ago

unfortunately, seems like there is a real bug. Even examples from MSDN don't work for me. Also, this: social.msdn.microsoft.com/Forums/sqlserver/en-US/….

Nathan Over a year ago

But there is apparently still no way for you to actually log all the errror messages (just try inserting the multiple error messages into a log table - no way that I can find.)

Davos Over a year ago

@Nathan If you need to do it on the database, and assuming you're not running an ancient version of SQL Server, then Extended Events appears to be the way to go. See Erland Sommarskog's notes: sommarskog.se/error_handling/Part3.html#xp_cmdshell Dave Mason has 2-part blog all about how to: itsalljustelectrons.blogspot.com.au/2016/09/…

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

I know this is a bit old, but worth repeating here. This is a limitation of the error functions of sql, but looks like they are addressing for future versions.

Social MSDN question - sql inner exception error

Yes, this is a limitation of error_xxxxx() functions. When there are multiple error messages, they only give information about one of the messages - not always the most informative one.

In the next version of SQL Server, codenamed Denali, there is a new command THROW which you can use in a catch handler, and which re-reraises the error, so you don't have to handle that yourself. When you use THROW, both errors will be reraised

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 12, 2011 at 8:31

MikeCov

1361 silver badge5 bronze badges

Collectives™ on Stack Overflow

Capturing multiple error messages from a single statement inside TRY CATCH

3 Answers 3

Actual Output

Claimed Output

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Actual Output

Claimed Output

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related