September 24, 2003 at 8:45 am
I am sure this has been talked about before but I couldn't find it in a search.
I have a stored proc that executes numerous stored procs that are not dependant on each other, they are just a list of feeds that occur over night. I want to be able to continue on to the next procedure if one of them throws a critical error that would normally halt the entire procedure. Does anyone know of any "On Error Resume Next" command in SQL Server? Or any other creative ways around the entire procedure breaking when one of the procs throws an error?
September 24, 2003 at 10:05 am
no way I know of. You'd have to handle this at the client for now. Yukon (next version of SQL) will introduce Try..Catch, which will help with this. Error Handling is pretty poor in SQL Server.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2003 at 10:49 am
That's no good! Thanks anyways. I don't have a client that does this, it was just going to be a job.
September 25, 2003 at 2:41 am
Perhaps you could consider placing each stored proc into a step on a job. In the ADVANCE tab of the job step, set the ON FAILURE action to GO TO NEXT STEP. If the current step fails then the job continues to the next step.
September 25, 2003 at 6:55 am
This is something of a kluge but I think if you call the stored procedure from osql at the command line it will continue after at least some errors. You might be able to call it with xp_cmdshell. I recently had to test some error checking and I was able to force a division by zero error and get the procedure to send an email message when I ran the procedure in osql.
Hope this helps.
Don
September 25, 2003 at 7:01 am
Thanks everyone for your suggestions, I think I am going to go ahead and break out the procedure and put the smaller procs into a job (like Jordan suggested). Then I can email myself when something fails.
September 25, 2003 at 7:04 am
Error handling in DTS is better. You could break it up there with e-mails generated and paths followed depending upon success or failure. Then just schedule it as a job.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply