How do i run a job based on success of another job?

Question

How do i run a job based on success of another job?

praveen.g.thampi

SSChasing Mays

Points: 615
More actions
January 22, 2009 at 8:55 am

#125792

Hi,
I want to run 3 sql agent jobs based on the success of another agent job. How do i accomplish this other than sp_start_job?
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The way I usually do that is to put the steps for the dependent jobs into the first job and set the "On Success" and "On Failure" characteristics to get the sequence I need. Then you only have one job to monitor.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

CrazyMan SSChampion Points: 12560 More actions · Answer 2

Why cant you combine all this 3 jobs into one job with 3 steps, if step 1 fails exit the job, else continue the job.

if you don't want to go down on this road then use sysjobs table to find out of that job ran successful then proceed on the next ones

Hope this helps 😀

spongemagnet SSCrazy Points: 2812 More actions · Answer 3

If it's not possible or logical to consolidate the jobs, you can have the last On Success step of the first job run sp_start_job to kick off the next. If the second and third jobs are dependent on the prior steps repeat this with the last On Success of each - or if not simply put three sp_start_jobs in the last On Success step of the first.

If you do this you'll likely want to consider deleting the schedule on the subsequent jobs if you don't want them to also start up on their own schedule.

-Ken

praveen.g.thampi SSChasing Mays Points: 615 More actions · Answer 4

Thanks everybody.

It's amazing that I get replies within seconds I posted this:)

My scenario:

I have 7 weekly re-index tasks of which one takes lot of time (namely job1 , job2 etc). So I want 3 jobs to run first of which the job3 is the longest one , and based on the success of job3 only I want to proceed with the rest (job 4-7). Otherwise my tempdb is thrased.

So i think the best solution is to call the rest 4 jobs on the last step of job 3?

spongemagnet SSCrazy Points: 2812 More actions · Answer 5

praveen.g.thampi (1/22/2009)
So i think the best solution is to call the rest 4 jobs on the last step of job 3?

Provided that that last step is reached On Success of the previous step or Job3 Quits Reporting Failure if that previous step fails.

-Ken

GSquared SSC Guru Points: 260824 More actions · Answer 6

I'd still go with a single job. Add a step in between that checks the end-time of step 3 (currently job 3), and continues to step 4 depending on the time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

praveen.g.thampi SSChasing Mays Points: 615 More actions · Answer 7

GSquared (1/22/2009)
Add a step in between that checks the end-time of step 3 (currently job 3), and continues to step 4 depending on the time.

Hi,

Can you pls elaborate that - I mean check the end-time of step 3 (how) and continues to step 4 depending on the time (but, I need it to go based on success/failure not time)

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 8

praveen.g.thampi (1/22/2009)
GSquared (1/22/2009)
Add a step in between that checks the end-time of step 3 (currently job 3), and continues to step 4 depending on the time.
Hi,
Can you pls elaborate that - I mean check the end-time of step 3 (how) and continues to step 4 depending on the time (but, I need it to go based on success/failure not time)
Thanks

I misread part of one of your posts. Thought you wanted to quit if step 3 took a long time. Just a misread on my part.

If you want to do something like that, you build a query that raises an error if certain conditions are met, like the current time being past a certain threshhold, and you tell the job to quit if that step fails. If it raises an error (with severity 16 or above), it will consider the step to have failed, and will not procede.

Doesn't actually apply in this case, but that's the theory if it ever does come up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

praveen.g.thampi SSChasing Mays Points: 615 More actions · Answer 9

praveen.g.thampi

SSChasing Mays

Points: 615

January 23, 2009 at 2:36 am

#931693

Thanks Mate.