if then else statment for jobs... ?

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  December 20, 2006 at 12:18 pm

  #68562

  i'm trying to alter a job step based on a value in the

  sysjobhistory table under the [run_status] column.

  basically if the stats is '4' then the job is still running

  so i'm trying to create an if/else statement which will

  change the step based on the '4', but need a little help

  with the logic.

  here's what i got so far.

  if MYJOB [run_status] = 4

  then

  sp_update_jobstep

  @job_name = 'MYJOB',

  @step_id = 3,

  @on_success_action = 1 --Changes to: Quit Reporting success.

  else

  sp_update_jobstep

  @job_name = 'MYJOB',

  @step_id = 3,

  @on_success_action = 3 --Changes to: Go to Next Step

  to get the job name and status of a job you simply run a

  query join between the sysjobs & sysjobhistory tables which

  i've done here in case this helps out with the logic.

  select sysjobs.name, sysjobhistory.run_status from

  sysjobs join sysjobhistory on (sysjobs.job_id = sysjobhistory.job_id)

  where sysjobs.name = 'MYJOB'

  in any case is the if/then/else possible for some thing like this?

  any help would be greatly appreciated.

  thanks in advance.

  _________________________

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  December 21, 2006 at 10:07 am

  #679494

  Please do not double post.

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."