Create Your Own Batch Runs Composed Of Existing Jobs

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