|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-08-26 : 08:10:11
|
| Tony writes "In many situation I create individual jobs that have specfic functionality and can be executed independently. In many cases, however, I need to create a work flow using a DTS package when one job needs to complete (not just successfully started) before it can branch (Success/Failure) to the appropriate next Job.I am running SQL2K SP3 on Win2K SP4I have written the following but get inconsistent results when the job completes (for example, step 2 starts before step 1 completes). Can You Help?Thanks,TonyCREATE PROCEDURE dbo.sp_StartSynchronousSQLAgentJob( @JobName SYSNAME = NULL, @PollingDelay CHAR(8) = '00:00:10')AS/******************************************************************************DESCRIPTION This procedure will start a SQL Agent job and wait for its completion before returning.PARAMETERS @JobName @PollingDelayHISTORY 5/23/2003 Tony Semaan Author******************************************************************************/BEGIN SET NOCOUNT ON DECLARE @Error INT , @OK INT , @JobId UNIQUEIDENTIFIER , @IsSysAdmin INT , @JobOwner sysname , @TRUE INT , @FALSE INT , @IsRunning INT , @SUCCESS TINYINT , @RunOutcome TINYINT , @RunMessage VARCHAR(512) , @SuccessMessage VARCHAR(512) , @FailMessage VARCHAR(512) , @NoJobMessage VARCHAR(512) , @NoJobIdMessage VARCHAR(512) , @NoJobStartMessage VARCHAR(512) SELECT @Error = 0 , @OK = 0 , @IsSysAdmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) , @JobOwner = SUSER_SNAME() , @TRUE = 1 , @FALSE = 0 , @IsRunning = 0 , @SUCCESS = 1 , @RunOutcome = 5 , @RunMessage = 'Job ''' + @JobName + ''' is currently running' , @SuccessMessage = 'Job ''' + @JobName + ''' executed successfully.' , @FailMessage = 'Job ''' + @JobName + ''' failed or was canceled!' , @NoJobMessage = 'Job ''' + @JobName + ''' does not exist!' , @NoJobIdMessage = 'Job ''' + @JobName + ''' has no corresponding JobId!' , @NoJobStartMessage = 'Job ''' + @JobName + ''' failed while attempting to start!' IF NULLIF(LTRIM(@JobName),'') IS NULL BEGIN SELECT @Error = 99999 RAISERROR(@NoJobMessage, 16, 1) GOTO TheEnd END SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE name = @JobName IF @JobId IS NULL BEGIN SELECT @Error = 99999 RAISERROR(@NoJobIdMessage, 16, 1) GOTO TheEnd END CREATE TABLE #xp_results ( job_id UNIQUEIDENTIFIER NOT NULL , last_run_date INT NOT NULL , last_run_time INT NOT NULL , next_run_date INT NOT NULL , next_run_time INT NOT NULL , next_run_schedule_id INT NOT NULL , requested_to_run INT NOT NULL -- BOOL , request_source INT NOT NULL , request_source_id SYSNAME COLLATE database_default NULL , running INT NOT NULL -- BOOL , current_step INT NOT NULL , current_retry_attempt INT NOT NULL , job_state INT NOT NULL ) EXEC @Error = msdb.dbo.sp_start_job @job_name = @JobName IF @Error != @OK BEGIN RAISERROR(@NoJobStartMessage, 16, 1) GOTO TheEnd END --Wait 10 seconds to make sure the job has started before starting to poll for its status WAITFOR DELAY '00:00:10' WHILE (@IsRunning = @TRUE) BEGIN DELETE #xp_results INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @IsSysAdmin, @JobOwner, @JobId SELECT @IsRunning = running FROM #xp_results IF @IsRunning = @TRUE BEGIN WAITFOR DELAY @PollingDelay CONTINUE END ELSE BEGIN BREAK END END SELECT @RunOutcome = last_run_outcome FROM msdb.dbo.sysjobservers WHERE job_id = @JobId IF ISNULL(@RunOutc |
|
Starting SQL Agent Job and Waiting Until Completion 