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



  • 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.



  • 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://docs.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 = ;'
    FROM msdb.dbo.sysjobsteps AS S
    WHERE S.job_id = @JobID
    --AND <Additional filtering?>
    ORDER BY S.step_id




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2