Fatal errors in SQL 2000 sp's

[kishore.kr]

Hi All,
I have to trap fatal errors in SQL Server 2000's Stored proc. Is it possible to capture the fatal error in the same stored proc itself or is there only any workaround like capturing in some other stored proc's. I would prefer to capture it in the same procedure itself.
Any help or guidance will be appreciable. Thank you.

[Merrion]

Fatal errors? Like disk crashes or server reboots can't be trapped

Normal errors in process flow can - and you use the @@error system variable to see if an error happened in the preceeding step.

[kishore.kr]

Merrion, Thanks 4 the reply. The statement I am using is,

delete from tblname 
if @@error <> 0 goto err_handler

here the tblname table does not exists. I couldn't capture this error using @@error. It says invalid object name and then quits.

[disruptivehair]

Merrion, Thanks 4 the reply. The statement I am using is,

delete from tblname 
if @@error <> 0 goto err_handler

here the tblname table does not exists. I couldn't capture this error using @@error. It says invalid object name and then quits.

If there's a possibility the table won't exist then use an IF statement and check for it first.

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'dbo.<table>') and OBJECTPROPERTY(id, N'IsTable') = 1)

' Do other stuff

[kishore.kr]

Hi Disruptive Hair, That was a useful suggesstion. Thank you.
But my thing is I want to trap the fatal error, when it occurs. Bcos there are also some few other cases which I came across where the fatal error occurs. So please let me know if you got any ideas for that.

[disruptivehair]

Hi Disruptive Hair, That was a useful suggesstion. Thank you.
But my thing is I want to trap the fatal error, when it occurs. Bcos there are also some few other cases which I came across where the fatal error occurs. So please let me know if you got any ideas for that.

Kishore, which fatal error is occurring? If you could paste the entire error so I could see it it would be helpful; then I could tell you possibly how to trap it.

If the table not existing is THE error that's occurring, it's more easily trapped by checking for the existence of the table first than by using @@ERROR, which clearly isn't working very well. If you check for the existence of the table in sysobjects then you can tell the program exactly what to do if it's there or if it isn't, and if the object isn't there you can raise an error yourself using the RAISERROR keyword; RAISERROR is covered pretty well in the SQL Server BOL.

I use the 'if exists' structure in all of my create/amend scripts and it prevents the SQL server from going haywire on the off chance that the object isn't there, either because it was accidentally deleted (not likely) or because I misspelled the object's name in the script (much more likely). It's pretty standard practice.

Good luck!

[szlamany]

Google for this:

"errors you cannot trap in SQL with @@ERROR"

and happy reading!

The topics are numerous on which errors can and cannot be trapped.

A bad table name (one which does not exist) is not a trappable error. This makes lots of sense to me - because it's a DDL error. That would be like trying to reference a column name that does not exist.

[kishore.kr]

Thank you Disruptivehair, for your nice suggesstion. I feel that I should follow this one atleast for now.

Szlamany, thanks for googling text, I went thru that. I am able to get that fatal errors are not trappable. But I'll be trying to get some direct solution for this. Let me try.

[disruptivehair]

Thank you Disruptivehair, for your nice suggesstion. I feel that I should follow this one atleast for now.

Szlamany, thanks for googling text, I went thru that. I am able to get that fatal errors are not trappable. But I'll be trying to get some direct solution for this. Let me try.

You're welcome kishore; let me know how you get on with that. I've got some sample code if you need it.