October 7, 2010 at 4:23 am
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?
October 7, 2010 at 5:30 am
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.
October 7, 2010 at 6:36 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 7, 2010 at 6:53 am
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!)
October 7, 2010 at 7:02 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 7, 2010 at 7:12 am
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!
October 7, 2010 at 7:52 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 7, 2010 at 8:18 am
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 😉
October 7, 2010 at 11:23 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply