SQL Agent Job - Change all job steps to go to a specific step when the job fails

Question

I have a SQL Agent Job that I need to change, so that any time any step fails the 'ON FAILURE' action goes to a specific step.

Does anyone have a script or can you show me how to write this so that I can do a bulk update for all the steps in a job to go to a specific step on failure?

I have a job that has over 800 steps. It's a crazy vendor job, but it needs a step so I can execute a sendmail script if the job fails.

Answer 1

I would use something like this (concrete example for Stephen Morris's answer although I don't use a cursor). This will generate the command to use in the CMD output for the second query.

However, are you not able to use the "Notifications" tab on the job itself? That would be easier.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql?view=sql-server-ver15

/** Find your JobID here **/
SELECT J.job_id, J.[name]
FROM msdb.dbo.sysjobs AS J

DECLARE @JobID UNIQUEIDENTIFIER
SET @JobID = 'F6F5DBF7-18FD-49D5-B018-81A55010C59D'

/** GET LIST OF ALL STEPS HERE **/
SELECT S.step_id
    , S.step_uid
    , S.step_name
    , S.on_fail_action
    , S.on_fail_step_id
    , CMD = 'EXEC msdb.dbo.sp_update_jobstep @job_id = ' + QUOTENAME(@JobID, '''') + ', @step_id = ' + CONVERT(NVARCHAR(20), S.step_id) + ', @on_fail_action = 4, @on_fail_step_id = <STEP HERE>;'
FROM msdb.dbo.sysjobsteps AS S
WHERE S.job_id = @JobID
    --AND <Additional filtering?>
ORDER BY S.step_id

Answer 2

You can retrieve all the job steps using sp_help_jobstep. I suggest you call the sp and store the results in a table. Then loop (cursor) over all the steps and call sp_update_jobstep, set the param @on_fail_action to 4 and the @on_fail_step_id to the step which contains sendmail.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql?view=sql-server-ver15

Answer 3

use this sp on every step you need:

use msdb
go

exec sp_update_jobstep 
@job_name = 'test
job', 
@step_id = 1, 
@on_fail_action = 4, 
@on_fail_step_id = 2

Answer 4

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE  @job_id VARCHAR(1000),
         @Step_id INT,
         @Get_Step_Id INT
         @Get_Step_Id = Job Step ID that you want jobs to goto on Failure
         

DECLARE JobList CURSOR  
        FOR  

        SELECT  jb.job_ID,
                --jb.name,
                --jstp.step_name,
                jstp.step_id
        FROM    msdb.dbo.sysjobs jb
                INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
        WHERE   name = 'Job Name' 
        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 = 4, @on_fail_step_id = @Get_Step_ID ---- 2 Quit with failure.


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