June 12, 2007 at 5:10 pm
I have a job step that calls a stored proc that then calls 3 additional stored procs. My goal here is to know if one of those stored procs fails w/o interrupting the operation of the other procs being called. RAISERROR to my knowledge would cause the job to fail and successfully show up in my alerts however it would prevent any subsequent procs to be executed so thats out. The alerts I get come from sysjobhistory.
So what I'm wondering is if anyone can think of any reason not to write my own errors to the sysjobhistory table?
I assume it will be fine but just want to double check w/ everyone to see if anyone has tried this before and run into any issues...
Thanks!
Ben Sullins
bensullins.com
Beer is my primary key...
June 12, 2007 at 10:07 pm
Why not just change the "On failure action" for the step to be "Goto the next step" instead of "quit the job reporting failure" (this is on the Advanced tab within each job step) - no need to worry about writing to sysjobhistory at all.
June 12, 2007 at 10:49 pm
Well there are 3 procs called from one 'step' that calls a 'master' proc. So the problem is if I do a raiserror and kill the 'step' it would potentially not let the other procs inside the 'master' be executed. So it is setup like this...
Job is executed
>> Step 2 calls proc spStep2
>>>> spStep2 calls spUpdate1, spUpdate2, spInsert1
So if spUpdate1, spUpdate2, or spInsert1 fails I want to record that it failed so it shows in my alerting system which looks @ the sysjobhistory table. I'm fairly confident that I won't run into any issues just inserting a new row to this table but wanted to get some more opinions on it...
Thanks!
Ben Sullins
bensullins.com
Beer is my primary key...
June 12, 2007 at 10:57 pm
It is up to you (in your master sproc) to do the error handling and decide whether you should abort or not. You can control what happens so I don't see why one stored procedure failing needs to stop the next one being called.
As for writing to sysjobhistory - I assume that you have all the relevant data on that (i.e. appropriate values for all of the fields). I would consider using sp_sqlagent_log_jobhistory or something like that so that you can be sure that everything you write to sysjobhistory is valid.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply