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 step, 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 (either th last or the first, 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 disabled job 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.