I have a daily scheduled SQL job on SSMS with multiple steps, if any of these steps fail, I want to disable the job in order to prevent the scheduled job from running the following day.
I have currently got this working by setting step 1 to check the value in a control table and only continue if the value is 1. Then on failure of any following steps, jump to step 10 which is a T-SQL script that updates the value in this table to 0
Whilst this does work, I think disabling the job entirely seems cleaner. Is there a way to do this?
Have a step in the job that disables the job (probably either the last or the first) using sp_update_job and then have the other step(s) run that step on failure (and go to the next step on success). You’ll also likely want your disabling step to report a failure after it successfully runs. Just make sure that either the job starts at step 2 or the penultimate step quits the job on success.