June 13, 2002 at 9:02 am
Hello all -
Is there a way to execute on stored procedure when a job scheduled via EM fails?
Instead of 'on failure' - send an email, I want 'on failure' - take some action (using a stored proc).
Thanks in advance!
apf
June 13, 2002 at 9:08 am
Hi make a new job step with the sp. In the flow logic refer to this step on failure.
June 13, 2002 at 9:09 am
You could call the SP as the next step. If it fails, it goes to the next step which is the Stored Procedure. If it passes, set the On Success, Goto Step x to the step after the stored proc.
Assume you have 3 steps.
If Step 1 succeeds, go to Step 3
If Step 1 fails, go to Step 2
Hope that helps,
Clive
Clive Strong
June 13, 2002 at 9:15 am
Wow! You guys are fast. Thank you very much for the quick response and the info.
A follow-up:
Is there a way to pass info from one step to the next? (return code, job step, job name, etc.)
Thanks again!
apf
June 13, 2002 at 9:22 am
I don't believe there is a way to pass return codes etc between steps in Scheduled Jobs. To be honest, I never thought of trying. Maybe you can...If so, I'd also be interested to know how!!
Right now tho, if I am creating a job of steps whereby I need to pass a value into the next step, I'd use a DTS. You could hold the value in a global variable and reference it from any step.
You can then include the DTS in your scheduled job.
Clive
Clive Strong
June 13, 2002 at 6:40 pm
I have never seen a way and as far as I know you can only say if a stepped failed and choose path of execute. DTS is a better solution for what you are asking and does allow you to pass a variable between pieces. If someone knows a way to do within a job I sure want to know how you pulled it off.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 13, 2002 at 7:47 pm
Not directly - you can fake it by writing the status to a table or a file, have the next step check it for the status.
Andy
June 14, 2002 at 6:58 am
Thank you all for your suggestions!
I believe I will have to give Andy's suggestion (writing to a table) a try as my DTS knowledge is very limited.
apf
June 14, 2002 at 12:22 pm
I use Andy's solution to "loosely couple" steps together from different jobs. Allows them to be linked, but not at a success or failure level.
Steve Jones
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply