There are times I want to compose a batch that consists of existing SQL Server Agent jobs. Unfortunately, there is no SQL Server Agent job step for starting jobs, at least not in SQL 2000 en SQL 2005, I don't know about SQL 2008. You could of course use sp_start_job to start the job, but it does what it says, it starts the job, and than finishes after a few seconds, because the job is started.
What you want, of course, is that the next step does not start until the job you started is finished. That is what this stored procedure does. It starts a job, whose name you pass as a parameter, and then waits untill its finished. Using this procedure you can create a batch by creating a new job that starts existing jobs in each jobstep. If you want to nest levels, by creating a week batch, and then a month batch that starts week batches, you could create one stored procedure for each level, by adding a number (StartJobWithWait_1) or something like that
The best part is of course the OPENROWSET query, which I took from the net. I can't find the site anymore where I got it, but it is also discussed here on SQLServerCentral : http://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx
The procedure runs both on SQL 2000 and on SQL 2005.
And eh.. use at your own risk 😉
John van Beek