if job failes... disable a step in a different job. this possible?

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  October 18, 2006 at 2:31 pm

  #153241

  i know i can enable/disable other jobs upon failiure

  from another job, but... how do you disable a step??

  say for example it's step 1 of a job. can you disable

  it?? cause there really isn't any precedence for a

  (stop reporting failure/success) action prior to it.

  i have a Job1, which if it failes will disable Job3

  however; if Job2 is set to run Job3... then it wouldn't

  matter if it was disabled or not cause the sp_start_step

  would run it regardless. thats my issue at the moment.

  thoughts?

  _________________________

• Ian Griffiths

  Ten Centuries

  Points: 1025

  More actions

  October 19, 2006 at 3:15 am

  #666732

  I've never tried exactly what you're after, so my solution is a bit round-a-bout, there's probably a better SQL Agent solution.  However, can you set a flag on a table in job 1 depending on success or failure, and read it from a new first step in jobs 2 or 3?  Depending on the value of the flag that the later jobs read, they could stop or continue as necessary.

• TDuffy

  SSCarpal Tunnel

  Points: 4170

  More actions

  October 19, 2006 at 7:27 am

  #666782

  Individual job steps can not be disabled or enabled.

  You can run sp_update_jobstep on the step preceeding the one you want disabled and change its @on_success_action  and or @on_success_step_id Alternatively, if the desired step you want disabled is the first step, you can run sp_update_job altering the @start_step_id.

  Terry

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  October 19, 2006 at 9:12 am

  #666832

  You may replace steps with individual jobs and implement the workflow using stored procedure that you can schedule.

  Regards,Yelena Varsha

• Kemp Davis

  Ten Centuries

  Points: 1067

  More actions

  October 19, 2006 at 10:56 am

  #666865

  You could create a table, and update it with the status of job 1 by adding an additional step at the end of job 1 that executes on failure of the preceding step updating the status of job 1 in the table to failed. Then add a step to the beginning of the other job to check the status of job 1. If it was successful continue, if job 1 failed then end.