Need To Start Agent Job And Wait Until Completes And Get Success Or Failure

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 tha

Solution 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-- =============================================CREATEprocedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitminsint=5) --, @result int output)ASBEGINset NOCOUNT ON;
set XACT_ABORT ON;

    BEGIN TRY

    declare@runningasintdeclare@seccountasintdeclare@maxseccountasintdeclare@start_job asbigintdeclare@run_status asintset@start_job =cast(convert(varchar, getdate(), 112) asbigint) *1000000+ datepart(hour, getdate()) *10000+ datepart(minute, getdate()) *100+ datepart(second, getdate())

    set@maxseccount=60*@maxwaitminsset@seccount=0set@running=0declare@job_owner sysname
    declare@job_id UNIQUEIDENTIFIER

    set@job_owner = SUSER_SNAME()

    -- get job idselect@job_id=job_id
    from msdb.dbo.sysjobs sj
    where sj.name=@job-- invalid job name then exit with an error
    if @job_id isnull
        RAISERROR (N'Unknown job: %s.', 16, 1, @job)

    -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following tabledeclare@xp_results TABLE ( job_id                UNIQUEIDENTIFIER NOTNULL,
                                last_run_date         INTNOTNULL,
                                last_run_time         INTNOTNULL,
                                next_run_date         INTNOTNULL,
                                next_run_time         INTNOTNULL,
                                next_run_schedule_id  INTNOTNULL,
                                requested_to_run      INTNOTNULL, -- BOOL
                                request_source        INTNOTNULL,
                                request_source_id     sysname          COLLATE database_default NULL,
                                runningINTNOTNULL, -- BOOL
                                current_step          INTNOTNULL,
                                current_retry_attempt INTNOTNULL,
                                job_state             INTNOTNULL)

    -- start the jobdeclare@rasintexec@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 stateinsertinto@xp_results
    execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

    set@running= (SELECT top 1runningfrom@xp_results)

    while @running<>0and@seccount<@maxseccountbegin
        WAITFOR DELAY '0:0:01';
        set@seccount=@seccount+1deletefrom@xp_results

        insertinto@xp_results
        execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

        set@running= (SELECT top 1runningfrom@xp_results)
    end-- result: not ok (=1) if still running

    if @running<>0begin-- still runningreturn0endelsebegin-- did it finish ok ?set@run_status =0select@run_status=run_status
        from msdb.dbo.sysjobhistory
        where job_id=@job_id
          andcast(run_date asbigint) *1000000+ run_time >=@start_job

        if @run_status=1return1--finished okelse--error
            RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)

    endEND TRY
    BEGIN CATCH

    DECLARE@ErrorMessage    NVARCHAR(4000),
        @ErrorNumberINT,
        @ErrorSeverityINT,
        @ErrorStateINT,
        @ErrorLineINT,
        @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

Solution 2:

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

Solution 3:

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).

Solution 4:

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.

CREATEPROCEDURE [admin].[StartAgentJobAndWaitForCompletion]
    @JobName    SYSNAME,
    @TimeLimitINT=60,   --     Stop waiting after this number of minutes@Debug      BIT         =0ASSET NOCOUNT ON;

DECLARE@JobId          UNIQUEIDENTIFIER,
        @Current        DATETIME,
        @Message        NVARCHAR(MAX),
        @SessionIdINT;

SELECT@JobId= job_id
  FROM  msdb.dbo.sysjobs
 WHERE  name =@JobName;

IF @JobIdISNULLBEGIN
    RAISERROR ('No job named "%s"', 16, 1, @JobName) WITH NOWAIT;
    RETURN1;
END;

EXEC msdb.dbo.sp_start_job @job_id =@JobId;

IF @Debug=1BEGINSET@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_TIMESTAMPBEGINSET@SessionId=NULL;

    SELECT  TOP(1) @SessionId= session_id
      FROM  msdb.dbo.sysjobactivity sja
     WHERE  sja.job_id =@JobIdAND  sja.start_execution_date ISNOTNULLAND  sja.stop_execution_date ISNULLORDERBY sja.start_execution_date DESC;

    IF @SessionIdISNULL
        BREAK;

    IF @Debug=1BEGINSET@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=1BEGINSET@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 populatedRETURN0;

Solution 5:

CREATEPROCEDURE dbo.usp_RunJobWithOutcome
@JobName sysname
, @RunTimeoutint
, @RunStatusint output
ASSET NOCOUNT ON--Verify that this job exists
IF NOTEXISTS (SELECT1FROM msdb.dbo.sysjobs WHERE [name] =@JobName)
BEGINSET@RunStatus=5--Unknown
    RAISERROR('Invalid job name ''%s''', 16, 245, @JobName);
    RETURN1END;

--Start the jobDECLARE@retvalint;
exec@retval= msdb.dbo.sp_start_job @job_name=@JobName;

--If start succeeded, poll for completion
IF @retval=0BEGIN
    PRINT N'Job started successfully';
    WAITFOR DELAY '00:00:05';

    DECLARE@JobRunTimeint;
    SET@JobRunTime=0;
    SET@RunStatus=5; --Unknown -> default return

    WHILE @JobRunTime<@RunTimeoutBEGIN
        WAITFOR DELAY '00:00:05';

        --SELECT statement below give the same result as 'sp_help_jobactivity' sys-procSELECT@JobRunTime=CASEWHEN stop_execution_date ISNULLTHEN DATEDIFF(SECOND, start_execution_date, GETDATE()) ELSE@RunTimeoutENDFROM ( 
            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
            LEFTJOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
            WHERE ja.session_id = (SELECTMAX(session_id) FROM msdb.dbo.sysjobactivity ja1 WHERE ja1.job_id = ja.job_id AND ja1.run_requested_date ISNOTNULL)
                AND j.[name] =@JobName
        ) JobActivity;
    END;

    --Get the final statsSELECT@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
        LEFTJOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
        WHERE ja.session_id = (SELECTMAX(session_id) FROM msdb.dbo.sysjobactivity ja1 WHERE ja1.job_id = ja.job_id AND ja1.run_requested_date ISNOTNULL)
            AND j.[name] =@JobName
    ) JobActivity;

    PRINT N'Job completed in '+CONVERT(nvarchar, @JobRunTime) +' seconds.'
    IF @RunStatus=1RETURN0; --SuccessELSERETURN1; --FailedEND;
ELSEBEGIN
    PRINT N'Job could not start';
    SET@RunStatus=5--UnknownRETURN1; --failedEND;

GO
DECLARE@RunStatusint, @retvalint--Run for max 60 minutesexec@retval=dbo.usp_RunJobWithOutcome @JobName='*<your job name here>*', @RunTimeout=3600, @RunStatus=@RunStatus output

SELECT@retval, @RunStatus
GO