Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to set up chained steps in sql server agent Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 12:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
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!
Post #1466342
Posted Friday, June 21, 2013 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1466346
Posted Friday, June 21, 2013 1:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
Thank you Elliott. That works like a charm.
Post #1466363
Posted Friday, June 21, 2013 1:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
You are welcome.

CEWII
Post #1466365
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse