SQL Server – Bulk Change Agent Jobs to Quit on Failure

sql serversql-server-2008sql-server-2008-r2sql-server-agent

There are agent jobs with multiple steps in our servers and all their steps are defined as "go to the next step" "On Failure". So, even a job step fails, operator doesn't get notified because eventually the job succeeds.
How can I modify all steps in all jobs so that job fails reporting failure if a step fails?
edit: best way, I guess, would be to use msdb.dbo.sp_update_jobstep dynamically to update all steps where @on_fail_action !=2
Thanks all.

Best Answer

Use Script to update Job steps In Bulk 

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO


    DECLARE  @job_id VARCHAR(1000),
             @Step_id INT

    DECLARE JobList CURSOR  
            FOR  

            SELECT  jb.job_ID,
                    jstp.step_id
            FROM    msdb.dbo.sysjobs jb
                    INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
            WHERE   name = 'Jobname' 
            --AND Step_name <>'Step Name'     Add Step name to filter
            ORDER BY jb.job_ID, jstp.step_id


    OPEN JobList
        FETCH NEXT FROM JobList INTO @job_id,@Step_id
        WHILE @@FETCH_STATUS = 0
            BEGIN

                     -- Execute SP sp_update_jobstep to update Job Steps and pass 2(Quit with failure) as value to parameter @on_fail_action
                    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id, @step_id = @Step_id, @on_fail_action = 2 ---- 2 Quit with failure.


                FETCH NEXT FROM JobList INTO @job_id,@Step_id
           END
        CLOSE JobList
        DEALLOCATE JobList

Note: Make sure all steps need to be updated on failure as "Quit the job reporting failure" otherwise filter them by adding in WHERE Clause

Reference Link : https://msdn.microsoft.com/en-us/library/ms189827.aspx

Related Question