How to set @on_fail_action variable in runtime by using sp_update_jobstep

• vbpandian

  SSC Enthusiast

  Points: 189

  More actions

  June 9, 2009 at 1:01 am

  #205342

  Hi All

  I am facing one Problem while setting variable(@on_fail_action) in Sql Job 2000 .

  I have JOb with 2 Step.First Step is validating the Holiday CHeck,if Today is not an Holiday Then it will move to Next step,it while execute one Sp.This is working fine.

  If today is an Holiday then I wrote statement for Raiseerror by manually then it will quit the job with Failure.

  But I need to this JOb Quit with Success.I tried to change the Setting on failure action for Quit with Success.Ny requirement is not that one.

  I need to set this set Quit with Success before the raiseerror statement after executing the raiseError statement it should revert back Quit with Failure.

  Please do the needful.

  DECLARE @lintCnt int

  select @lintCnt=count(*) from Emp

  if @lintCnt>0

  Begin

  EXEC msdb.dbo.sp_update_jobstep @job_name = 'TestJob', @step_id = 1, @step_name = 'ChkHliday', @on_fail_action = 1

  RaisError('Error Occurred while execuitng Job',12,127)

  EXEC msdb.dbo.sp_update_jobstep @job_name = 'TestJob', @step_id = 1,

  @step_name = 'ChkHliday',@on_fail_action = 2

  END