SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Create Your Own Batch Runs Composed Of Existing Jobs

By John van Beek,

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


Total article views: 1019 | Views in the last 30 days: 1
Related Articles

create Procedure within Procedure

create Procedure within Procedure


Execute a stored procedure from a batch file

Execute a stored procedure from a batch file


Be Careful of Your Create Stored Procedure Batch

I was rehearsing a demo with someone recently and we had some stored procedure code that looked like...


Creating temp table in Batch file

Creating temp table in Batch file


Deleting Batches of Rows with TOP

Using the TOP clause in a procedure to delete records in batch.

agent jobs    
sql agent