How to run Sequential Jobs using job schedule in Sql server

Question

I have a requirement that i need to run multiple jobs in a sequence. Let's say I have 20 jobs which already existed. These jobs are depending on each other. Right now i am running these jobs manually like below

run Job1 --> run job2 upon Job1 Success/quit next process upon Job1 failure --> run job3 upon Job2 Success/quit next process upon Job2 failure --> .... run job20 upon Job19 Success/quit next process upon Job19 failure --> Quit the job.

Along with this i want to have log history for these all jobs. If any job failed in mid way i want to check the log to find which job got failed and what is the reason for the fail.

Now i want to make it automatic. Is there a possible way to do it?

Please help me

Thank you in advance Chandana

One way is just use sp_start_job. Add a step to each job, make sure it's the last step and have it execute "exec msdb.dbo.sp_start_job N'<>' — Tim Mylott, Mar 02 '20 at 22:15
@TimMylott's comment is the "standard" way to do this. Tim, if you make that an answer, I'd up vote it. Sometimes even a short answer is the right answer. — Eric Brandt, Mar 03 '20 at 14:51

Tim Mylott · Answer 1 · 2020-03-05T20:27:22.600

2

I assume when you say "job" you mean a SQL Server Agent Job.

One way to accomplish what you are after is to simply use sp_start_job stored procedure.

On each job add a tsql job step.
Make sure it is the last step
Then have it execute the code below

EXEC msdb.dbo.sp_start_job N'Name of Next Job to Start'

Here is a visual of that setup:

You can then daisy change the jobs and have the last step of Job2 start Job3 and so on...

edited Mar 05 '20 at 20:27

answered Mar 05 '20 at 20:24

Tim Mylott

2,553
1
5
11

Way to flesh that out. Good visuals. – Eric Brandt Mar 05 '20 at 20:26
In this case, if Job 2 fails then it will start job 1 again or starts from job 2 ? – Sql Programmer May 19 '21 at 18:47
It would quit and start from step 1 next time it is ran. You can also manually run it from SSMS and decide what step to start from. – Andrew Borst May 21 '21 at 16:57

score 0 · Answer 2 · answered Mar 02 '20 at 21:17

you can use maintenance plan.

in this feather you can set any job for step by step OR set If statement .

in this picture i show you a Incomplete display of maintenance plan :

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.

you can read HOW TO CREATE A MAINTENANCE PLAN HERE

How to run Sequential Jobs using job schedule in Sql server

2 Answers2