Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted
  • 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
share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.