You need to start working with an agent and wait until completion and achieve success or failure

Question

I am trying to find sample code using SQL Server 2005, I need to start Agent Job and wait for the process to complete, and then get success or failure.

I know I can use

EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName' 

to run the task, but I can’t find any sample code to poll msdb.dbo.sp_help_job to find out when it will be completed, and then find out if it was successful or failed.

Answer 1

 -- ============================================= -- Description: Starts a SQLAgent Job and waits for it to finish or until a specified wait period elapsed -- @result: 1 -> OK -- 0 -> still running after maxwaitmins -- ============================================= CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins int = 5) --, @result int output) AS BEGIN set NOCOUNT ON; set XACT_ABORT ON; BEGIN TRY declare @running as int declare @seccount as int declare @maxseccount as int declare @start_job as bigint declare @run_status as int set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate()) set @maxseccount = 60*@maxwaitmins set @seccount = 0 set @running = 0 declare @job_owner sysname declare @job_id UNIQUEIDENTIFIER set @job_owner = SUSER_SNAME() -- get job id select @job_id=job_id from msdb.dbo.sysjobs sj where sj.name=@job -- invalid job name then exit with an error if @job_id is null RAISERROR (N'Unknown job: %s.', 16, 1, @job) -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table declare @xp_results TABLE ( 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) -- start the job declare @r as int exec @r = msdb..sp_start_job @job -- quit if unable to start if @r<>0 RAISERROR (N'Could not start job: %s.', 16, 2, @job) -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?) WAITFOR DELAY '0:0:01'; set @seccount = 1 -- check job run state insert into @xp_results execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id set @running= (SELECT top 1 running from @xp_results) while @running<>0 and @seccount < @maxseccount begin WAITFOR DELAY '0:0:01'; set @seccount = @seccount + 1 delete from @xp_results insert into @xp_results execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id set @running= (SELECT top 1 running from @xp_results) end -- result: not ok (=1) if still running if @running <> 0 begin -- still running return 0 end else begin -- did it finish ok ? set @run_status = 0 select @run_status=run_status from msdb.dbo.sysjobhistory where job_id=@job_id and cast(run_date as bigint) * 1000000 + run_time >= @start_job if @run_status=1 return 1 --finished ok else --error RAISERROR (N'job %s did not finish successfully.', 16, 2, @job) end END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200); SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE(); RAISERROR ( @ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- original error number. @ErrorSeverity, -- original error severity. @ErrorState, -- original error state. @ErrorProcedure, -- original error procedure name. @ErrorLine -- original error line number. ); END CATCH END 

Answer 2

You can refer to the run_status column in the sysjobhistory table. 0 indicates a failure.

Answer 3

This may not be a very reliable method, but I could try to write the task to a specific table both at the beginning and at the end of the process and poll this table in my client application (or use ADO events to fire the appropriate event handlers).

Answer 4

Here is the code I wrote for this purpose. One caveat is that it does not handle the case where the task is already running while this procedure is in progress.

 CREATE PROCEDURE [admin].[StartAgentJobAndWaitForCompletion] @JobName SYSNAME, @TimeLimit INT = 60, -- Stop waiting after this number of minutes @Debug BIT = 0 AS SET NOCOUNT ON; DECLARE @JobId UNIQUEIDENTIFIER, @Current DATETIME, @Message NVARCHAR(MAX), @SessionId INT; SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE name = @JobName; IF @JobId IS NULL BEGIN RAISERROR ('No job named "%s"', 16, 1, @JobName) WITH NOWAIT; RETURN 1; END; EXEC msdb.dbo.sp_start_job @job_id = @JobId; IF @Debug =1 BEGIN SET @Message = CONVERT(VARCHAR(19), CURRENT_TIMESTAMP, 120) + ' ' + @JobName + ' started'; RAISERROR (@Message, 0, 1) WITH NOWAIT; END; SET @Current = CURRENT_TIMESTAMP; WAITFOR DELAY '00:00:02'; -- Allow time for the system views to be populated WHILE DATEADD(mi, @TimeLimit, @Current) > CURRENT_TIMESTAMP BEGIN SET @SessionId = NULL; SELECT TOP(1) @SessionId = session_id FROM msdb.dbo.sysjobactivity sja WHERE sja.job_id = @JobId AND sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL ORDER BY sja.start_execution_date DESC; IF @SessionId IS NULL BREAK; IF @Debug =1 BEGIN SET @Message = CONVERT(VARCHAR(19), CURRENT_TIMESTAMP, 120) + ' ' + @JobName + ', Session: ' + CONVERT(VARCHAR(38), @SessionId); RAISERROR (@Message, 0, 1) WITH NOWAIT; END; WAITFOR DELAY '00:00:05'; END; IF @Debug = 1 BEGIN SET @Message = CONVERT(VARCHAR(19), CURRENT_TIMESTAMP, 120) + ' ' + @JobName + ' completed'; RAISERROR (@Message, 0, 1) WITH NOWAIT; END; WAITFOR DELAY '00:00:02'; -- Allow time for the system views to be populated RETURN 0; 

Answer 5

 CREATE PROCEDURE dbo.usp_RunJobWithOutcome @JobName sysname , @RunTimeout int , @RunStatus int output AS SET NOCOUNT ON --Verify that this job exists IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = @JobName) BEGIN SET @RunStatus = 5 --Unknown RAISERROR('Invalid job name ''%s''', 16, 245, @JobName); RETURN 1 END; --Start the job DECLARE @retval int; exec @retval = msdb.dbo.sp_start_job @job_name=@JobName; --If start succeeded, poll for completion IF @retval = 0 BEGIN PRINT N'Job started successfully'; WAITFOR DELAY '00:00:05'; DECLARE @JobRunTime int; SET @JobRunTime = 0; SET @RunStatus = 5; --Unknown -> default return WHILE @JobRunTime < @RunTimeout BEGIN WAITFOR DELAY '00:00:05'; --SELECT statement below give the same result as 'sp_help_jobactivity' sys-proc SELECT @JobRunTime = CASE WHEN stop_execution_date IS NULL THEN DATEDIFF(SECOND, start_execution_date, GETDATE()) ELSE @RunTimeout END FROM ( SELECT ja.session_id, ja.job_id, j.[name] job_name, ja.run_requested_date, ja.run_requested_source, ja.queued_date, ja.start_execution_date , ja.last_executed_step_id, ja.last_executed_step_date, ja.stop_execution_date, ja.next_scheduled_run_date, ja.job_history_id , jh.[message], jh.run_status, jh.operator_id_emailed, jh.operator_id_netsent, jh.operator_id_paged FROM msdb.dbo.sysjobactivity ja JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity ja1 WHERE ja1.job_id = ja.job_id AND ja1.run_requested_date IS NOT NULL) AND j.[name] = @JobName ) JobActivity; END; --Get the final stats SELECT @RunStatus=run_status, @JobRunTime=DATEDIFF(SECOND, start_execution_date, stop_execution_date) FROM ( SELECT ja.session_id, ja.job_id, j.[name] job_name, ja.run_requested_date, ja.run_requested_source, ja.queued_date, ja.start_execution_date , ja.last_executed_step_id, ja.last_executed_step_date, ja.stop_execution_date, ja.next_scheduled_run_date, ja.job_history_id , jh.[message], jh.run_status, jh.operator_id_emailed, jh.operator_id_netsent, jh.operator_id_paged FROM msdb.dbo.sysjobactivity ja JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity ja1 WHERE ja1.job_id = ja.job_id AND ja1.run_requested_date IS NOT NULL) AND j.[name] = @JobName ) JobActivity; PRINT N'Job completed in ' + CONVERT(nvarchar, @JobRunTime) + ' seconds.' IF @RunStatus = 1 RETURN 0; --Success ELSE RETURN 1; --Failed END; ELSE BEGIN PRINT N'Job could not start'; SET @RunStatus = 5 --Unknown RETURN 1; --failed END; GO DECLARE @RunStatus int, @retval int --Run for max 60 minutes exec @retval=dbo.usp_RunJobWithOutcome @JobName='*<your job name here>*', @RunTimeout=3600, @RunStatus=@RunStatus output SELECT @retval, @RunStatus GO