CODEWITHSUNDEEP
t-sqlstored-proceduresssisssms-2014
Chandana Puppy
Chandana Puppy

Reputation: 133

How to run Sequential Jobs using job schedule in Sql server

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

Upvotes: 1

Views: 3030

Answers (2)

Tim Mylott
Tim Mylott

Reputation: 2766

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.

  1. On each job add a tsql job step.
  2. Make sure it is the last step

  3. 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: enter image description here

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

Upvotes: 3

Amirhossein
Amirhossein

Reputation: 1251

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 : enter image description here

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

Upvotes: 0

Related Questions