How to get all state Error in try catch block

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to get all state Error in try catch block

Author

Topic

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2013-09-11 : 08:36:26

How can I get all state error in SQL Server 2008/2012?

I am using Try catch block in my stored procedure for error message and return error message if any error occur.

I am trying to alter one table column which is associated with other object so when I am change that column datatype SQL Server return an error

ALTER TABLE ALTER COLUMN Abc_col1 failed because one or more objects access this column.

When I am executing only alter script in Sql it will give me 2 error messages as below


Msg 5074, Level 16, State 1, Line 1
The object 'fn_Abc_state' is dependent on column 'Abc_col1 '.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Abc_col1 failed because one or more objects access this column.

when compare both message only state is different

my question is How we can get all the Error message when it's occur while executing my Sp along with state as below

State 1 = The object 'fn_ABC_state' is dependent on column 'Abc_col1 '.
State 9 = ALTER TABLE ALTER COLUMN Abc_col1 failed because one or more objects access this column.

-------------
Chirag
India
Sr.Software Engineer

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 09:54:09

Hm, I'm on thin ice here but I'm not sure it's possible using try/catch. In't the catch-block thrown on the first error? How about trying something like this:

insert into #proc_result
exec my_stored_proc

select * from #proc_result

Don't that will work either to be honest but might be worth a try...?

- Lumbago
My blog-> http://thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources