Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 ;-)

Cheers,

John van Beek

 

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

create Procedure within Procedure

create Procedure within Procedure

FORUM

Execute a stored procedure from a batch file

Execute a stored procedure from a batch file

FORUM

Creating temp table in Batch file

Creating temp table in Batch file

ARTICLE

Deleting Batches of Rows with TOP

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

FORUM

snapshot replication job starts immediately when created

snapshot replication job starts immediately when created

Tags
jobs    
sql agent    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones