SQL Server Agent Jobs – Displaying Status in an Application

  • Comments posted to this topic are about the item SQL Server Agent Jobs – Displaying Status in an Application

  • Re <>

    It was only a matter of time....

    http://news.bbc.co.uk/1/hi/world/africa/8248056.stm

  • Hilarious this one, thanks for the link.

    Now, when you consider that I wrote this a couple of months ago, I really should concentrate on guessing the lottery numbers...:-)

  • When the article stated that each job is only listed once in the sysjobactivity table I checked the table and found that to be false. I loose interest in articles that are inaccurate and poorly edited.

  • Hi, I would like to try this out but I can't find the "pr_RunAgentJob".

    Is this similar to the sp_start_job in MS SQL 2005?

    Also I found that code was missing in the VB.NET part:

    "'pollResult' is not declared."

    Guess a

    Dim pollResult As String = "" is missing

    Attaching the codefiles would make this easier to test.

  • Here is the code for pr_RunAgentJob:

    Sorry about the non-inclusion

    CREATE PROCEDURE [dbo].[pr_RunAgentJob]

    (

    @JobName VARCHAR(200)

    )

    AS

    /*

    This procedure is a wrapper for the sp_start_job stored procedure.

    It tests whether the job is running before allowing the job to be re-run

    */

    DECLARE @TestExists TINYINT

    SELECT @TestExists = COUNT(*)

    FROM msdb.dbo.sysjobactivity JA inner join msdb.dbo.sysjobs JO

    ON JA.Job_ID = JO.Job_ID

    WHERE JO.Name = @JobName

    AND start_execution_date IS NOT NULL

    AND Stop_execution_date IS NULL

    IF @TestExists = 0

    BEGIN

    EXEC msdb.dbo.sp_start_job @JobName

    RETURN 0 -- job called OK

    END

    ELSE

    BEGIN

    RETURN 1 -- Job running

    END

  • Thanks,

    However, can this wrapper really work?

    As Dave Wille says sysjobactivity contains many records for each job and for me most jobs contain values in Start_execution_date and Stop_execution_date even if they are not running.

    /F

  • I find the pr_RunAgentJob procedure a little curious. It checks if the job exists and if it is currently running. Well, it won't run if it doesn't exist and it won't run again if it is already running, so why not run the sp_start_job procedure and just let the code deal with any errors it would throw? Maybe I'm missing something, but I just don't see any added value from the "wrapper" procedure.

  • I've seen also that sysjobactivity contains several lines per jobs.

    For information i have implemented this using two stored procedures:

    - the first one called "uspTECH_GET_JOB_RUNSTATE" to retrieve current status:

    - the second one called "uspTECH_GET_JOB_FINALSTATUS" to retrieve the final status

    (once the job has finished)

    - an TSQL sql file named "exec_job_and_wait.sql" that:

    execute sp_start_job

    loop through uspTECH_GET_JOB_RUNSTATE while job is not finished

    finally get the final status with "uspTECH_GET_JOB_FINALSTATUS"

    This "exec_job_and_wait.sql" sql file is called for information by a batch dos called:

    exec_job_and_wait.bat

    I decided to compile those two stored proc. in master database.

    For information, current user that invoke this batch dos must have the following rights on SQL Server database:

    on master database:

    Grant execute on both stored proc

    GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs]

    on msdb database (to allow execution of jobs):

    EXEC sp_droprolemember N'SQLAgentReaderRole', N'ToMe'

    EXEC sp_droprolemember N'SQLAgentUserRole', N'ToMe'

    EXEC sp_droprolemember N'SQLAgentOperatorRole', N'ToMe'

    For information, i found those two stored proc. on internet, and i had a little bit adapt them:

    Current status:

    -- ======================================================================

    -- Author: From Internet

    -- Create date: 12/03/2009

    -- Description: Allow to determin the status of a job (executing or not)

    -- History:

    -- . :

    -- . :

    --

    -- Parameter:

    -- Input:

    -- . pIN_JobName : Job name to detect the status

    -- . pIN_JobID : Job ID to detect the status

    --

    -- Output:

    -- . pOUT_RunState : return the status of the job

    --

    -- ======================================================================

    ALTER PROCEDURE [dbo].[uspTECH_GET_JOB_RUNSTATE]

    @pIN_JobName sysname = NULL,

    @pIN_JobID UNIQUEIDENTIFIER = NULL,

    @pOUT_RunState int OUTPUT

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    --------------------------------------------------------------------------------

    -- DECLARE

    --------------------------------------------------------------------------------

    DECLARE @vErrCode INT

    DECLARE @VErr_Stored_Proc nvarchar(126)

    DECLARE @vErrline int

    DECLARE @vErrMessage nvarchar(4000)

    declare @vRc int

    declare @vBeginTranCount int

    DECLARE @is_sysadmin INT

    DECLARE @vCpt INT

    DECLARE @job_owner sysname

    DECLARE @wrkJobID UNIQUEIDENTIFIER

    DECLARE @wrkJobName sysname

    --------------------------------------------------------------------------------

    -- INITIALIZING

    --------------------------------------------------------------------------------

    -- variable locale commencant par v

    SET @vErrCode = 0

    set @vRc=0

    SET @vErrCode = 0

    set @vErrMessage ='';

    set @vErrline=0;

    set @VErr_Stored_Proc='' ;

    set @vBeginTranCount= @@TRANCOUNT

    --------------------------------------------------------------------------------

    -- PROCESSING

    --------------------------------------------------------------------------------

    BEGIN TRY

    -- SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

    SET @is_sysadmin = 1

    SELECT @job_owner = SUSER_SNAME()

    IF (@pIN_JobName = N'')

    SELECT @pIN_JobName = NULL

    -- jobname or job id must be set

    IF @pIN_JobName IS NULL

    IF @pIN_JobID IS NULL

    RETURN (-1) -- Need a job parameter

    -- retrieve jobname from jobid

    IF @pIN_JobName IS NULL

    BEGIN

    SELECT

    @pIN_JobName = [name]

    FROM

    msdb.dbo.sysjobs_view

    WHERE

    job_id = @pIN_JobID

    END

    ELSE

    BEGIN

    -- retrieve jobid from jobname

    IF @pIN_JobID IS NULL

    SELECT

    @pIN_JobID = job_ID

    FROM

    msdb.dbo.sysjobs_view

    WHERE

    [name] = @pIN_JobName

    ELSE

    BEGIN

    SELECT

    @wrkJobID = job_id,

    @wrkJobName = [name]

    FROM

    msdb.dbo.sysjobs_view

    WHERE

    job_id = @pIN_JobID

    IF IsNull(@wrkJobName,'') @pIN_JobName

    RETURN (-2) -- @pIN_JobID does not match @pIN_JobName

    END

    END

    IF @pIN_JobID IS NULL OR @pIN_JobName IS NULL

    RETURN (-3) -- No job by that name/id on file

    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,

    request_source int NOT NULL,

    request_source_id sysname NULL,

    running int NOT NULL, -- Boolean 1 Yes 0 No

    current_step int NOT NULL,

    current_retry_attempt int NOT NULL,

    job_state int NOT NULL

    )

    INSERT INTO #xp_results

    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @pIN_JobID

    IF @@ERROR 0

    BEGIN

    DROP TABLE #xp_results

    RETURN (-4)

    END

    SELECT @pOUT_RunState = running

    FROM #xp_results

    WHERE

    job_id = @pIN_JobID

    set @vCpt = coalesce(@@rowcount,0)

    IF @vCpt @vBeginTranCount ROLLBACK TRANSACTION

    RAISERROR (@vErrMessage,16,1)

    END CATCH

    RETURN 0

    END

    Then Final status:

    ==============

    -- ======================================================================

    -- Author: From Internet

    -- Create date: 23/03/2009

    -- Description: Allow to determin the final status of a job once terminated

    -- History:

    -- . :

    -- . :

    --

    -- Parameter:

    -- Input:

    -- . pIN_JobName : Job name to detect the status

    -- . pIN_JobID : Job ID to detect the status

    --

    -- Output:

    -- . pOUT_FinalStatus : return the status once finished

    -- . pOUT_FinalStatus : description associe du status

    --

    -- ======================================================================

    ALTER PROCEDURE [dbo].[uspTECH_GET_JOB_FINALSTATUS]

    @pIN_JobName sysname = NULL,

    @pIN_JobID UNIQUEIDENTIFIER = NULL,

    @pOUT_FinalStatus int OUTPUT,

    @pOUT_FinalStatusDesc VARCHAR(1024) OUTPUT,

    @pIN_bDescriptionComplete int = 0

    AS

    BEGIN

    DECLARE @is_sysadmin INT

    DECLARE @job_owner sysname

    DECLARE @wrkJobID UNIQUEIDENTIFIER

    DECLARE @wrkJobName sysname

    SET NOCOUNT ON

    SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

    SELECT @job_owner = SUSER_SNAME()

    IF (@pIN_JobName = N'')

    SELECT @pIN_JobName = NULL

    -- jobname or job id must be set

    IF @pIN_JobName IS NULL

    IF @pIN_JobID IS NULL

    RETURN (-1) -- Need a job parameter

    -- retrieve jobname from jobid

    IF @pIN_JobName IS NULL

    BEGIN

    SELECT

    @pIN_JobName = [name]

    FROM

    msdb.dbo.sysjobs_view

    WHERE

    job_id = @pIN_JobID

    END

    ELSE

    BEGIN

    -- retrieve jobid from jobname

    IF @pIN_JobID IS NULL

    SELECT

    @pIN_JobID = job_ID

    FROM

    msdb.dbo.sysjobs_view

    WHERE

    [name] = @pIN_JobName

    ELSE

    BEGIN

    SELECT

    @wrkJobID = job_id,

    @wrkJobName = [name]

    FROM

    msdb.dbo.sysjobs_view

    WHERE

    job_id = @pIN_JobID

    IF IsNull(@wrkJobName,'') @pIN_JobName

    RETURN (-2) -- @pIN_JobID does not match @pIN_JobName

    END

    END

    IF @pIN_JobID IS NULL OR @pIN_JobName IS NULL

    RETURN (-3) -- No job by that name/id on file

    -- retourne le resultat du job

    SELECT

    @pOUT_FinalStatus = sjh.run_status,

    @pOUT_FinalStatusDesc =

    case @pIN_bDescriptionComplete

    -- description succinte

    when 0 then

    case sjh.run_status

    when 0 then 'Failed '

    when 1 then 'Succeeded '

    when 2 then 'Retry (step only) '

    when 3 then 'Canceled '

    when 4 then 'In-progress message '

    when 5 then 'Unknown '

    else 'Unknown '

    end

    when 1 then

    CAST(SUBSTRING(sjh.message,1,1024) AS VARCHAR(1024))

    end

    FROM

    msdb.dbo.sysjobhistory sjh

    INNER JOIN

    msdb.dbo.sysjobs_view sj

    on sjh.job_id = sj.job_id

    where sj.name = @pIN_JobName

    and exists (

    SELECT max_step

    FROM (

    SELECT max(sjhii.step_id) as max_step

    FROM msdb.dbo.sysjobhistory sjhii

    INNER JOIN msdb.dbo.sysjobs_view sjii

    on sjhii.job_id = sjii.job_id

    and sjii.name = @pIN_JobName

    where (cast ( sjhii.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjhii.run_time, 6)) in

    (

    SELECT max(dt) from (

    SELECT sjhi.job_id, sjhi.step_id, cast ( sjhi.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjhi.run_time, 6) dt

    FROM msdb.dbo.sysjobhistory sjhi

    INNER JOIN msdb.dbo.sysjobs_view sji

    on sjhi.job_id = sji.job_id

    and sji.name = @pIN_JobName

    ) I2

    )

    and cast ( sjhii.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjhii.run_time, 6) =

    cast ( sjh.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjh.run_time, 6)

    ) I

    where sjh.step_id = I.max_step

    );

    SET @pOUT_FinalStatus = COALESCE(@pOUT_FinalStatus,-1);

    RETURN 0

    END

    ---------------------------------------------------------------

    An finally a TSQL code that i execute from a batch dos, that

    - start the job

    - execute the first SP until it is finished

    - get the final status using the second stored proc.

    TSQL stored in exec_job_and_wait.sql:

    ====================================

    -- execute le job passe en parametre

    DECLARE @ReturnCode INT

    DECLARE @ReturnStatus INT

    DECLARE @ReturnStatusDesc VARCHAR(50)

    DECLARE @Job sysname

    DECLARE @JobState int

    DECLARE @Msg NVARCHAR(250)

    SET @Job = N'$(V_JOBNAME)'

    IF COALESCE(N'$(V_STEP)',N'') = N''

    EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=@Job

    ELSE

    EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=@Job, @step_name=N'$(V_STEP)'

    IF @ReturnCode 0

    BEGIN

    SET @Msg = 'Error execution du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"'

    RAISERROR(@Msg,16,1)

    END

    ELSE

    BEGIN

    print Convert(VARCHAR(30), GetDate(),121) + ': Lancement job("' + @Job + '")'

    WaitFor Delay '00:00:02' -- Allow 2 seconds for job to start

    SET @JobState = 1

    WHILE @JobState 0 AND @ReturnCode = 0

    BEGIN

    print Convert(VARCHAR(30), GetDate(),121) + ': Attente Fin job("' + @Job + '")'

    WaitFor Delay '00:00:05' -- Check every 05 seconds

    Execute @ReturnCode = uspTECH_GET_JOB_RUNSTATE @pIN_JobName = @Job, @pOUT_RunState = @JobState OUTPUT

    END

    IF @ReturnCode 0

    BEGIN

    SET @Msg = 'Error attente du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"'

    RAISERROR(@Msg,16,1)

    END

    ELSE

    BEGIN

    Execute @ReturnCode = uspTECH_GET_JOB_FINALSTATUS @pIN_JobName = @Job,

    @pOUT_FinalStatus = @ReturnStatus OUTPUT,

    @pOUT_FinalStatusDesc = @ReturnStatusDesc OUTPUT

    IF @ReturnCode 0

    BEGIN

    SET @Msg = 'Error recuperation final status du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"'

    RAISERROR(@Msg,16,1)

    END

    ELSE

    print Convert(VARCHAR(30), GetDate(),121) + ': Status Final job("' + @Job + '")="' + + COALESCE(@ReturnStatusDesc,'') + '"'

    -- 1 = Success

    IF @ReturnStatus 1

    BEGIN

    SET @Msg = 'Error lors du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '", status final = "' + COALESCE(@ReturnStatusDesc,'') + '"'

    RAISERROR(@Msg,16,1)

    END

    END

    END

    GO

    ==========================================

    batch dos for information:

    -------------------------

    @echo off

    set SQLCMDSERVER=%~1%

    set SQLCMDDBNAME=master

    set VAR_JOBNAME=%~2%

    set VAR_STEP=%~3%

    call :mydate

    echo.

    echo execution du job "%VAR_JOBNAME%" a l'etape "%VAR_STEP%"

    sqlcmd -b -i exec_job_and_wait.sql -v V_JOBNAME="%VAR_JOBNAME%" -v V_STEP="%VAR_STEP%" -o "log\exec_job_and_wait_%VAR_JOBNAME%_%mydate%.log"

    IF NOT %ERRORLEVEL% == 0 GOTO FIN_ERREUR_CD

    :FIN

    echo.

    ECHO C est la fin correcte du lancement du job "%VAR_JOBNAME%" sur le serveur "%SQLCMDSERVER%" a partir de l'etape "%VAR_STEP%"

    goto FINALL

    :FIN_ERREUR_CD

    echo.

    ECHO !!! Erreur lors du lancement du job "%VAR_JOBNAME%" sur le serveur "%SQLCMDSERVER%" a partir de l'etape "%VAR_STEP%"

    goto FINALL

    :mydate

    for /f "tokens=1-3 delims=/" %%a in ('date /T') do set jour=%%a&set mois=%%b&set anneedeb=%%c

    for /f "tokens=1-3 delims=:" %%a in ('echo %time%') do set heures=%%a&set minutes=%%b&set secondesdeb=%%c

    SET annee=%anneedeb: =%

    SET secondes=%secondesdeb: =%

    SET mydate=%username%_%annee%%mois%%jour%%heures%%minutes%%secondes%

    :FINALL

  • For information sp_start_job is asynchonous, this is the problem .....

  • Re <>

    It was only a matter of time....

    http://news.bbc.co.uk/1/hi/world/africa/8248056.stm

    When a co worker sent this to me I replied with

    However it was found that when suitable security and data protection processes were included, the bird was squished by the chain mail.

    Debate arose among the researchers when a splinter group opposed a plan to do a feasibility study using Kevlar-based chain mail—claiming CrocodileNet™® would have inherent security and data protection advantages.

    CrocodileNet advocates pointed out that the Chicken-on-a-Stick/GPS interface used to guide the reptile worked flawlessly and minimized the 78% mortality rate in reception staff at the target site on data delivery. An additional faction claims these losses could be reduced by 37.2% if the rushed to production Chicken-on-a-Stick/GPS interface were to include the Croco-Snooze add on (consisting of a tranquilizer syringe that would be triggered when the GPS detected arrival at target site and/or in the reception area thereof), which had originally been part of the base specification but was dropped late during the rollout due to budgetary constraints.


    Cursors are useful if you don't know SQL

  • You need to tell the user the status of a job (sql job name is known) that was kicked off at a known date and time. Unless there's something else special going on, I don't think you need SSIS to get this done. (does your SSIS keep auto-refreshing your application page or something?)

    I think you're better off using sysjobhistory and run_status to check job status...

    The code below (turn it into a USP) will show you job/step details for a named job, run within the last n seconds (where n is set via @WithinTheLastNseconds). You could probably grab the exact time when the user kicked off the job so you could tighten this part up. Call the USP when the user clicks on a "refresh status" button or similar. You may want more or less detail; that's your call.

    I used a variation of this when I had a 30-40 ETL scripts (as separate sql jobs) to run. They had to run without overlapping, so I created a sql job that would run each job-- and at 30 second intervals, check to see if that job had completed before going on to the next.

    DECLARE @jname varchar(200);SET @jname = 'PUT YOUR SQL JOB NAME HERE' -- faked parm

    DECLARE @WithinTheLastNseconds int;SET @WithinTheLastNseconds = 6000 -- restrict to only jobs within this many seconds; recommended parm

    DECLARE

    @yyyymmddint int,

    @hhmmssch char(8),

    @hhmmssint int

    SELECT @yyyymmddint = CONVERT(varchar(20),getdate(),112),@hhmmssch = CONVERT(char(8),DATEADD(second,-@WithinTheLastNseconds,getdate()),8)

    SET @hhmmssint = SUBSTRING(@hhmmssch,1,2) + SUBSTRING(@hhmmssch,4,2) + SUBSTRING(@hhmmssch,7,2)

    select sj.name,

    jh.instance_id,

    jh.run_date,

    jh.run_time,

    jh.step_id,

    jh.step_name,

    jh.message,

    jh.run_status,

    CASE jh.run_status

    WHEN 0 THEN 'Failed'

    WHEN 1 THEN 'Succeeded'

    WHEN 2 THEN 'Retry'

    WHEN 3 THEN 'Canceled'

    WHEN 4 THEN 'In progress'

    ELSE '-abnormal-'

    END as run_status_desc

    ,jh.*

    from msdb.dbo.sysjobhistory jh

    inner join msdb.dbo.sysjobs sj ON jh.Job_ID = sj.Job_ID

    where

    jh.run_date = @yyyymmddint

    and jh.run_time >= @hhmmssint

    and sj.name=@jname

    order by

    sj.name,

    jh.instance_id


    Cursors are useful if you don't know SQL

  • You might want to look at msdb.dbo.sp_get_composite_job_info instead, single row per job, gives you running status.

  • I created a stored proc to pull job status (like the article), and it works great in the test environment. As soon as I pushed it to production I get the message "select permissions was denied on object 'sys.jobs'".

    I'm stumped on a safe way to show the job status.

  • I was naively trying to run the sp_stop_job in a maintenance plan, which of course, was throwing errors if the job was not running. Thanks to your article, I was able to now craft a simple sp to check the status and only stop it if it's running. Thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply