10

Is it possible to call a SQL Server job to run within another job?

I know we can add all steps of Job 1 to Job 2, but I prefer not to do that. First the Job 2 is already quite big and second I couldn't find a copy-paste option to copy steps between jobs, so it would be time consuming to add the steps manually.

Any suggestion is appreciated.

7
  • Right click on the job whose steps you want to add and choose "Script Job As->Create to new query window", in the resulting script look for all sections that have this format
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'<stepname>', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=3, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'<code>', 
  @database_name=N'', 
  @flags=0
  • Open a new query window and run this:
DECLARE @jobId BINARY(16)
    SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = '<job name, to which you want to copy the steps>')

-- Followed by all the msdb.dbo.sp_add_jobstep from the script that scripted out in the earlier step

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'<stepname>', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=3, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'<code>', 
      @database_name=N'', 
      @flags=0
14

Option 1

In Job2, create a job step of type TSQL Command. In the contents, have it run the existing job (sp_start_job)

EXECUTE msdb.dbo.sp_start_job 'Job1'

That is going to run asynchronously, thus after it starts calls the stored procedure, it will return and perform the next step in the job. It will not wait for that started job to complete. If the called job fails, it will not percolate back to the invoking job.

Option 2

Right click on Job1 and script to new query window. Repeat that with Job2 and then thread the job steps from 1 into 2 as/where needed. Far less clicking than recreating the wheel and hopefully less error prone.

  • Thanks for your reply. It seems we have to go for Option 2. I'm not quite sure whether I understood it correctly though. Do you mean I should create the scripts for both jobs, and then append steps of job2 to end of job1? Is that right? – Sky Dec 28 '12 at 5:13
6

From stackoverflow (mafafu )

WAITFOR DELAY '00:00:02';
while exists (select * from msdb.dbo.sysjobs j 
                inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id 
                where name = 'Job 1' 
                and stop_execution_date is null and start_execution_date is not null)
begin
    PRINT 'Waiting...'
    WAITFOR DELAY '00:00:02';   
end
  • This was useful for us, where we needed to delay the execution of a second job (which runs the early following day) based on the execution of the first job late in the evening. – James D Jan 10 '19 at 12:39
  • I am running a SYNC(Daily) and Incremental(Hourly) jobs and this helped me to keep a check on the Incremental and wait till it is finished to run the SYNC. – snp.it Jun 21 '19 at 18:41

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.