Is it possible to disable a SQL Agent job using the tsql commands from a job step within the job itself? The situation is that we have a job that runs hourly to move data from a proprietary application to our SQL Database. We've had some instances where unexpected invalid data came across from the proprietary application that caused step 3 of our 7 step job to fail after hours. Because this runs 24 hours, that same data was sent across every hour causing the same error every hour which made a mess of some of our data. We are working to tighten up our process to handle these thing more gracefully but as a stop-gap sort of fix, if, when one of the steps failed, we could disable the job so it wouldn't kick off again before someone had a chance to review the issue could save lots of cleanup time.
asked Oct 27 '11 at 10:00 AM in Default
You sure can, just add a SQL step for on failure that runs
GOEXEC msdb.dbo.sp_update_job @job_name=N'', @enabled=0
answered Oct 27 '11 at 10:10 AM
I would recommend looking at the
In your case, you would want to have your scheduled task set up with an error step that runs something similar to the following:
Don't forget to set the final "real" job step to not move onto the error step unless it errors!