SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to get all state Error in try catch block
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chiragvm
Yak Posting Veteran

India
63 Posts

Posted - 09/11/2013 :  08:36:26  Show Profile  Reply with Quote
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

Edited by - chiragvm on 09/11/2013 08:36:57

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 09/11/2013 :  09:54:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000