How do I get all the errors when in the CATCH block

Question

How do I get all the errors when in the CATCH block

Alex-815008

Mr or Mrs. 500

Points: 510
More actions
October 7, 2010 at 4:23 am

#230088

I'm running this statement in a try/catch block:-
alter table ref.[User] alter column EmployeeID smallint not null
The catch is defined as:-
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
end catch
And this results in the output:-
4922 16 9 NULL 64 ALTER TABLE ALTER COLUMN EmployeeID failed because one or more objects access this column.
However, when I run that ALTER statement on its own in SSMS, I get the stack of errors:-
Msg 5074, Level 16, State 1, Line 1
The object 'PK_Users' is dependent on column 'EmployeeID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN EmployeeID failed because one or more objects access this column.
Note that the stack of errors tells me what the problem is - that PK_Users is dependent - but the output in the CATCH statement is only giving me the last erron in the stack, which isn't enough to actually identify the problem.
Is there a way to get the whole error stack listed?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

As far as I know - only the last error message is available in the TRY...CATCH block and there is no way to get the entire error message stack.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

Because one of those errors is dependent on resolution of action, it's not caught by the TRY/CATCH. It's explained here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 3

Grant Fritchey (10/7/2010)
Because one of those errors is dependent on resolution of action, it's not caught by the TRY/CATCH. It's explained here.

Which link in that reference should I follow for the explanation? :ermm:

(Thanks for the reply anyway!)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Alex-815008 (10/7/2010)
Grant Fritchey (10/7/2010)
Because one of those errors is dependent on resolution of action, it's not caught by the TRY/CATCH. It's explained here.
Which link in that reference should I follow for the explanation? :ermm:
(Thanks for the reply anyway!)

Oops. Copied & pasted the wrong link. Sorry about that. Try this one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 5

Still not seeing it :unsure:

I've searched that article for "resolution" since that seemed to be a key part of what you were saying, but I only found references to "deferred name resolution".

I think I get what you're saying, I would just like to read the documentation so I can understand it more.

thanks!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

Alex-815008 (10/7/2010)
Still not seeing it :unsure:
I've searched that article for "resolution" since that seemed to be a key part of what you were saying, but I only found references to "deferred name resolution".
I think I get what you're saying, I would just like to read the documentation so I can understand it more.
thanks!

This is the one: ALTER TABLE ALTER COLUMN EmployeeID failed because one or more objects access this column.

Because SQL Server is resolving this stuff on the fly, it's not going to get caught in the standard TRY/CATCH. Normally, in a TRY/CATCH, there's only one error, because, as soon as you get an error, you're in the CATCH area.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 7

Thanks for the explanation, I think I see what you're saying.

I'm surprised there is such a limitation because I thought that actually it was common for there to be a stack of errors from a database after a single thing had gone wrong (I remember having to cope with this first of all back in the days of VB6 and Sql Server 7!).

For example, in my case here Msg 5074 is raised followed by (the more generic) Msg 4922. Now I would've thought that this is just one stack of errors and would be made available as such in the CATCH block.

Mind you, I am a C# programmer really so maybe I am just to used to how that language presents errors 😉

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 8

Alex-815008 (10/7/2010)
Thanks for the explanation, I think I see what you're saying.
I'm surprised there is such a limitation because I thought that actually it was common for there to be a stack of errors from a database after a single thing had gone wrong (I remember having to cope with this first of all back in the days of VB6 and Sql Server 7!).
For example, in my case here Msg 5074 is raised followed by (the more generic) Msg 4922. Now I would've thought that this is just one stack of errors and would be made available as such in the CATCH block.
Mind you, I am a C# programmer really so maybe I am just to used to how that language presents errors 😉

Nah, you just have to change your last sentence. You're used to how a real language presents errors.

TSQL just doesn't do a good job (better than ever, but still not good).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning