Capturing multiple error messages from a single st

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)

Answer 1

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

Answer 2

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 

Answer 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