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