How to handle SQL errors

Last Post 06 Nov 2007 03:55 PM by nosepicker. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
HarryNejad
New Member
New Member

--
03 Nov 2007 07:58 PM


I have a stored procedure that calls many other stored procedures. (I call it in Query Analyzer)


If one query in one of them fails, the rest of the whole thing will be skipped, and the entire process will be aborted.

How can I make it continue with the next query?

Or even better:

How can I have an error handler where I could programmically process the error (for example log the error code, message, time and date, the name of the stored procedure where it happenned, etc)?

Any help on this will be greatly appreciated.

thanks,

Harry
SQLUSA
New Member
New Member

--
05 Nov 2007 03:40 PM
Instead of calling from a main sproc, you have to call them from a DTS or SSIS workflow. There you can add the appropriate branching and erro control.

Kalman Toth, DB, DW & BI Architect
Fall 2007 SQL Conference in Las Vegas - http://www.sqlusa.com/order2005grandslam
HarryNejad
New Member
New Member

--
06 Nov 2007 01:30 PM

I actually have no other choice but to call the sp thorugh Query Analyzer.


Is there any way I could still do some degree of error handling (even very basic) when I call it through Query Analyzer?

Any ideas?

Any help on this will be greatly appreciated.


thanks,

Harry
nosepicker
New Member
New Member

--
06 Nov 2007 03:55 PM
A very basic thing to do is capture the @@ERROR value immediately after a SQL statement executes (will return 0 if successful). The types of errors associated with the error number can be found in the sysmessages table in the master database.
If you're using SQL 2005, you can make use of the TRY...CATCH syntax as well.
You are not authorized to post a reply.

Acceptable Use Policy