sp_start_job question 2

[jonwolds]

I have a table listing the job_id and the order the jobs are to be run. I am looping through the table running sp_start_job for each job_id in the table.

The problem is that I need to wait until the current job finishes before sp_start_job starts the next one.

Is there a way to do this with out continously monitoring the job status?

Thanks
Jon

 

[jonwolds]

Have thought of a better idea. This would be to create a new job which calls all the other jobs as steps.

Anything better please let me know.

Jon

 

[donutman]

Star for solving your own problem.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jonwolds]

I don't deserve the star. It doesn't work. The job steps in the new job are 'exec sp_start_job ..etc'. Once the job has started it moves onto the next job step. This means that multiple steps can run at the same time.

Any thoughts anyone?

 

[SQLSister]

I think your problem is that you have sp_start_job as the steps. It would consider starting the job as success of the step.

Have you tried putting what is actually in the jobs as the steps in a amultiple step process? Then the completion of the task would be the indicator of success of the step which would cause it to move onto the next step.

Questions about posting. See faq183-874

 

[donutman]

Interesting, sounds like it's unable to distinguish between the successful completion of a job vs a step within a job. Is it a national disaster to put all the steps of all the jobs into one job?
Alternatively, you could just schedule each job individually giving each sufficient time to finish.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jonwolds]

Thanks for your thoughts. Due to the volume of jobs and job steps I think I'll run the first job then periodically check (Using WAITFOR) the status until it has finished then kick off the next job and so on.

Pretty ugly method though.

 

[donutman]

Ok then do it this way. Make the last step of job 1 be to start job 2 and so on.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]