Trapping proper error message,

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 2005 Forums

SQL Server Administration (2005)

Trapping proper error message,

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-02-11 : 14:31:58

Following code generates two error messages,

Create table tab_b (id int references non_existant_table(col1))

quote:
Msg 1767, Level 16, State 0, Line 1
Foreign key 'FK__tab_b__id__6A90B8FC' references invalid table 'non_existant_table'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

First error message (1767) is more descriptive than the second one (1750)

However in the CATCH block when I trap the error message using
ERROR_NUMBER(), ERROR_MESSAGE() only the second one is captured.

Is there a way to trap the first error message?

------------------------
I think, therefore I am - Rene Descartes

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-12 : 16:12:40

Hello;

I don't think you can trap the 1st error in a TRY CATCH block as it is regarded as a Compile Time error (not runtime).

This post has a section that discusses what cannot be trapped.
[url]http://msdn.microsoft.com/en-us/library/ms175976.aspx[/url]

r&r

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-02-13 : 09:32:02

quote:
Originally posted by revdnrdy

Hello;

I don't think you can trap the 1st error in a TRY CATCH block as it is regarded as a Compile Time error (not runtime).

I am using dynamic sql, hence I could capture the error. Anyway, ERROR_xxx() functions trap only the recent error.

------------------------
I think, therefore I am - Rene Descartes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:33:56

also see

http://www.sommarskog.se/error-handling-I.html

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-13 : 09:50:05

You can always go back to the good ole days and actually anticipate potential problems and test for that condition. In this case if it is possible that your referenced table may not exist then check for it's existence prior to referencing it and raise the appropriate error.

Be One with the Optimizer
TG

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-02-13 : 09:56:06

quote:
Originally posted by TG
...it is possible that your referenced table may not exist then check for it's existence prior to referencing it and raise the appropriate error.

This is a good suggestion. Thank you.

------------------------
I think, therefore I am - Rene Descartes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources