Can we over write the job out come?

• lakshmanchand.15

  SSC-Addicted

  Points: 445

  More actions

  December 3, 2014 at 4:14 am

  #295526

  Hi,

  I am calling a job 'x' from job step of another job 'Y'. As the the purpose of the job step is to call another job, I included the below

  code in the job step to wait until the job completes.

  EXEC msdb.dbo.sp_start_job @job_name ='X'

  WHILE(

  SELECT count(1)

  FROM msdb.dbo.sysjobactivity aj

  JOIN msdb.dbo.sysjobs sj on sj.job_id = aj.job_id

  WHERE aj.stop_execution_date IS NULL -- job hasn''t stopped running

  AND aj.start_execution_date IS NOT NULL -- job is currently running

  AND name='X'

  AND NOT EXISTS( -- make sure this is the most recent run

  SELECT 1

  from msdb.dbo.sysjobactivity new

  WHERE new.job_id = aj.job_id

  AND new.start_execution_date > aj.start_execution_date))>0

  WAITFOR DELAY '00:00:30'

  The step waits until the job complete but, i want my job 'Y' to report failure when job X failed. I mean, Please share the script that resolves my problem.

  And one more question is, can we over write the outcome of the job?

  Thanks

  Lakshman