Create Your Own Batch Runs Composed Of Existing Jobs

,

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

 

USE [msdb]
GO
/****** Object: StoredProcedure [csp_StartJobWithWait] Script Date: 05/14/2009 08:13:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [csp_StartJobWithWait]( @par_JobName VARCHAR(100) )
AS
 BEGIN
 DECLARE @JobName VARCHAR(100) ,
 @Sql VARCHAR(4000) ,
 @WaitAfterStartJob VARCHAR(10) ,
 @WaitAfterStatusCheck VARCHAR(10) ,
 @ErrorMsg VARCHAR(100) ,
 @current_execution_status INT
 /*
 0 Returns only those jobs that are not idle or suspended.
 1 Executing.
 2 Waiting for thread.
 3 Between retries.
 4 Idle.
 5 Suspended.
 7 Performing completion actions.
 */
 ,
 @last_run_outcome INT
 /*
 0 = Failed
 1 = Succeeded
 3 = Canceled
 5 = Unknown
 */
 /*
 Create temporay table to catch te result of sp_help_job
 */
 IF OBJECT_ID('tempdb..#temp_status') IS NOT NULL
 DROP TABLE
 #temp_status
 CREATE TABLE
 #temp_status
 (
 current_execution_status INT ,
 last_run_outcome INT
 )
 SET @WaitAfterStartJob = '00:00:20'
 SET @WaitAfterStatusCheck = '00:00:10'
 SET @JobName = @par_JobName
 SET @Sql = 'INSERT INTO #temp_status SELECT current_execution_status, last_run_outcome FROM OPENROWSET(' + '''' + 'sqloledb' + '''' + ', ' + '''' + 'server=(local);trusted_connection=yes' + '''' + ', ' + '''' + 'set fmtonly off exec msdb.dbo.sp_help_job @job_name = ' + '''' + '''' + @JobName + '''' + '''' + ', @job_aspect = JOB' + '''' + ')'
 /*
 Check if the job exists
 */
 IF
 (
 SELECT
 COUNT(1)
 FROM
 msdb.dbo.sysjobs
 WHERE
 name = @JobName
 )
 <> 1
 BEGIN
 SET @ErrorMsg = @JobName + ' not found'
 RAISERROR ( @ErrorMsg, -- Message text.
 16, -- Severity.
 1 -- State.
 );
 RETURN
 END EXEC
 msdb.dbo.sp_start_job @job_name = @JobName
 /*
 Wait a little, to give sp_start_job the time to start the job
 */
 WAITFOR DELAY @WaitAfterStartJob
 EXEC (@Sql)
 SET @current_execution_status =
 (
 SELECT
 TOP 1 current_execution_status
 FROM
 #temp_status
 )
 WHILE @current_execution_status <> 4 -- Niet Idle
 BEGIN
 TRUNCATE TABLE
 #temp_status
 EXEC (@Sql)
 SET @current_execution_status =
 (
 SELECT
 TOP 1 current_execution_status
 FROM
 #temp_status
 )
 /*
 Wait a little while before checking again
 */
 WAITFOR DELAY @WaitAfterStatusCheck
 END
 /*
 Job is finished, check the result
 */
 SET @last_run_outcome =
 (
 SELECT
 TOP 1 last_run_outcome
 FROM
 #temp_status
 )
 IF @last_run_outcome <> 1 -- Not succeeded
 BEGIN
 SET @ErrorMsg = @JobName + ' failed'
 RAISERROR ( @ErrorMsg, -- Message text.
 16, -- Severity.
 1 -- State.
 );
 RETURN
 END
END

Rate

4 (2)

Share

Share

Rate

4 (2)