2

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.

1

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

  • What does 'Get SQL Profiler Trace' come from? However, this won't do it for all jobs. – spaghettidba Mar 9 '15 at 9:57
  • 1
    @spaghettidba for testing purpose I think no one will allow to update all jobs all steps in a single click you always test your scripts first. – AA.SC Mar 9 '15 at 9:59
  • Thanks AA SC but I have too many agent jobs to manually type in their and their steps' names to the script. – Stackoverflowuser Mar 9 '15 at 12:25
  • I updated script but please be careful and make sure you are not adding any step which should not be part of this activity. – AA.SC Mar 9 '15 at 14:59
1

You can do it in bulk using this statement:

UPDATE msdb.dbo.sysjobsteps
SET on_fail_action = 2 -- Quit the job reporting failure

However, please note that you probably don't want to that without checking what the job steps do and whether some jobs are configured for specific additional steps in case of failure or simply ignore errors. Be careful!

  • Wrong or right depends on the jobs' logic (as I pointed out), but it's exactly what the OP asked for. – spaghettidba Mar 9 '15 at 10:04
0
  1. Expand the option of SQL Server Agent

  2. Expand Jobs

  3. Right Click on the respective job and click properties

  4. On job properties, click on second option saying Steps.

  5. Select the step and click on Edit

  6. Now select the second tab saying Advanced.

  7. Here on the tab which says ON failure action, select from drop down saying "Quit the job reporting failure" instead of go to the next step.

Make similar changes to step for you're respective jobs and to fast up the process you can script out one action and run that script for multiple changes.

Hope this answers your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.