I have 3 jobs in sql agent job and they are name, Job1, job2 and Job3.
Each job will have several steps inside the job.
I can run each manually for each job. I would like to create another job to combine all of them and run in order.
The job must be completed successfully before to go to next job and so on.
I see people using sp_start_job in stored procedure, but I want to run in Agent job.
Can someone give me advise? Thank you.
Can we do script sp_start_job wait first job finish then run second job and so on
Option 1: Start the first job, and inside each Job at the end of the code add command (using sp_start_job) to execute the next job. Since the command comes at the end it will execute after the rest of the code assuming the rest of the code is simple query which run synchronously.
Option 2: in the main job use steps (you can also do it without steps but I ,like to have it separated) and in each step execute one of the jobs using sp_start_job.
At the beginning of each step, use simple loop with "WAITFOR DELAY '00:00:02';" onside the loop. Check if the previous job finished using the Stored Procedure sp_help_jobactivity.
If previous job ended then exit the loop and continue, which mean now we will execute the next job
Option 3 (crazy and probably not recommended for most cases): executing the jobs one after the other through a VBS script using Distributed Management Objects (DMO) from the main job.
I hope this solve your needs. I will upload full code in my blog soon...