How to set up chained steps in sql server agent

  • I need to set up a job that has 3 steps.

    I would like it to do the following:

    1. Run step 1

    If success go to step 2

    If failure send email to xxxx and go to step 2

    2. Step 2

    IF sucess go to step 3

    If failure send email to xxxx and go to step 3

    3. Step 3

    IF sucess go to step 3

    If failure send email to xxxx

    Under the advanced tab, I can see how to set the success /Go to next step.

    I want the failure to send mail first then go to next step. How do I set this up?

    Thank you!

  • You end up with extra steps

    1. Do something, If success goto step 3, if fail goto step 2

    2. Email the failure, goto step 3

    3. Do something else, If success goto step 5, if fail goto step 4

    4. Email the failure, goto step 5

    5. Do something else, If success end with success, if fail goto step 6

    6. Email the failure, end with success

    Email the failure means an explicit call to msdb.dbo.sp_send_dbmail

    The ending on step 5/6 could be different where if step 5 fails you fail the job and let SQL Agent handle that failure but for consistency I like this setup.

    CEWII

  • Thank you Elliott. That works like a charm.

  • You are welcome.

    CEWII

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply