Capturing multiple error messages from a single statement.

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

ansaurus

tags:

views:

answers:

Capturing multiple error messages from a single statement.

related questions