Need to start agent job and wait until completes a

Question

I have been trying to find sample code with using SQL Server 2005, I need to start an Agent Job and wait until it finishes process and then get the success or failure.

I know that I can use

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

to start the job but I can't find any sample code to poll the msdb.dbo.sp_help_job to know when it is completed and then find out if it was successful or failed.

Answer 1

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

Answer 2

Here is code that I wrote for this purpose. One caveat is that it does not handle the case where the job is already running when this procedure is executed.

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 3

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

Answer 4

-- =============================================
-- 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 5

Maybe not an awfully reliable method, but I might try to have the job write to a certain table both at the beginning and at the end of the process, and to poll that table in my client application (or to use ADO events to trigger corresponding event handlers).