Printable View
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.
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.
Merrion, Thanks 4 the reply. The statement I am using is,
here the tblname table does not exists. I couldn't capture this error using @@error. It says invalid object name and then quits.VB Code:
If there's a possibility the table won't exist then use an IF statement and check for it first.Quote:
Originally Posted by kishore.kr
Code:IF EXISTS (select * from dbo.sysobjects where id = object_id(N'dbo.<table>') and OBJECTPROPERTY(id, N'IsTable') = 1)
' Do other stuff
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.Quote:
Originally Posted by kishore.kr
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! :thumb:
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.
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.Quote:
Originally Posted by kishore.kr