Calling a SQL Server job within another job

Question

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.

Answer 1

• 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

Answer 2

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.

Answer 3

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