Start & monitor a job to completion using T-SQL?

  • DarylAndDaryl

    Default port

    Points: 1491

    Is there a way to start a SQL Server 2000 job and then monitor it to completion? Has some one already developed a similar method that I may use?

    My company is implementing an open systems job scheduling product. I want to have this scheduling product start a SQL Server jobs and then have it report on wheather or not the SQL Server job was successful. I am currently developing my own SQL code that uses the msdb..sp_start_job and then monitors the job by selecting data from the msdb..sysjobhistory table. The problem with this is when the SQL Server job has only 1 jobstep, a record does not appear in the sysjobhistory table until the job completes. A work around is to make sure all SQL Server jobs have more than one jobstep and the first jobstep ends successful everytime. This way a row will show up in the sysjobhistory table before the job completes. Another option that I recently thought of was to take advantage of the master..sysprocesses table in conjuction with the msdb..sysjobhistory table.

  • This was removed by the editor as SPAM

  • Dave-3000

    SSCertifiable

    Points: 6186

    I pieced this together using posts from elsewhere on SQLservercentral.com:

     

    CREATE  PROCEDURE dbo.usp_IsSQLAgentJobRunning

     @ParmJobName sysname

    AS

    /*

    --  procedure to determine if the SQL Agent job named in @ParmJobName is currently running.  returns 0 if it is not currently running, -1 if it is.

    */

    DECLARE @Return int

    CREATE TABLE #Job (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  -- 1 if currently running, otherwise zero

                       current_step          INT              NOT NULL, 

                       current_retry_attempt INT              NOT NULL, 

                       job_state             INT              NOT NULL) 

    INSERT INTO #Job

       EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo

    IF EXISTS (SELECT *

          FROM #Job AS j

          JOIN msdb.dbo.sysjobs AS s

            ON j.job_id = s.job_id

         WHERE s.name = @ParmJobName

           AND j.running = 1)

      RETURN -1

    ELSE

      RETURN 0

    GO

     

    There is no "i" in team, but idiot has two.
  • DarylAndDaryl

    Default port

    Points: 1491

    Excellent! I don't find any documents that talk about xp_sqlagent_enum_jobs. What are the parameters being passed? Is there other parameters that my be passed?

  • noeld

    SSC Guru

    Points: 96590

    These are the parameters :

    EXEC xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>]

     

    HTH


    * Noel

  • SueB

    SSCommitted

    Points: 1537

    Dave,

    When I execute your stored procedure in Query Analyzer I don't get any value returned.

     

    Sue

  • hoo-t

    SSChampion

    Points: 12713

    xp_sqlagent_enum_jobs is undocumented.  As such, Microsoft makes no claims as to its availability or functionality in future releases,  and generally discourages customers' reliance on them.  You may want to consider this before using it in a project that you intend to market!

    Steve

  • Dave-3000

    SSCertifiable

    Points: 6186

    Sue:  Obvious question, but how are you calling it?  EXEC @Ret = dbo.usp_IsSQLAgentJobRunning 'myjobname'?  Then @Ret will have either a 0 or a -1.  Also, you need EXECUTE permission on master.dbo.xp_sqlagent_enum_jobs and SELECT permission on master.dbo.sysjobs.

    There is no "i" in team, but idiot has two.
  • Dave-3000

    SSCertifiable

    Points: 6186

    Steve:  That's a good point.  Now that you mention it, I recall considering that at the time, and fiddled around for quite awhile trying to get the same result out of a pure SQL solution, and finally gave up and went with the undocumented xproc.  We have this on our list of things to verify when we get a new service pack for SQL Server.

    There is no "i" in team, but idiot has two.
  • noeld

    SSC Guru

    Points: 96590

    Dave:

    What is the problem with using sp_help_job and use the current_execution_status field ?

     


    * Noel

  • Dave-3000

    SSCertifiable

    Points: 6186

    Noel:  It just plain didn't work for me, for reason or reasons unknown.  current_execution_status always returned zero, no matter what I did.  grrrr. 

    So I came up with the proc I described earlier.

    There is no "i" in team, but idiot has two.
  • hoo-t

    SSChampion

    Points: 12713

    Same here, Dave.

    Couldn't get the output I needed from Microsoft Documented procedures, so I hacked my own.  Works great!  I just hope Microsoft doesn't change or eliminate it.  But if they do, its my own little procedure in my own little world, and my own little fault for using an undocumented proc!

    Steve

  • DarylAndDaryl

    Default port

    Points: 1491

    The problem with sp_help_job is that one cannot capture its output. When one tries to capture the output into a table, an error occurs because sp_help_job calls other procs (nested). sp_help_job works great when executing it manually.

  • DarylAndDaryl

    Default port

    Points: 1491

    Below you will find my enhancement of Dave's stored procedure. This stored procedure will allow us to create SQL Server jobs but have a third party vendor solution run them. In the code below, I question why I have to have the WAITFOR DELAY. I found that with Query Analyzer, I am able to start a SQL Server Job a second time before the first execution has completed. However, I am not able to do this with SQL Server Enterprise Manatger. Can someone explain?

    CREATE PROCEDURE dbo.SpRunSQLAgentJob

    @ParmJobName sysname

    AS

    /*

    -- Procedure to start SQL Agent job named in @ParmJobName determine

    -- when it finishes, and whether or not it was successful.

    */

    SET NOCOUNT ON

    DECLARE @Return int, -- return code when calling other stored procedures

    @JobNotRunning bit, -- 0 = job is not running, 1 = job is running

    @Loop_Flag bit,

    @job_id uniqueidentifier,

    @last_run_date int,

    @last_run_time int,

    @run_status int,

    @run_status_name varchar(50),

    @StoredProc sysname -- will contain this stored procedure name

    CREATE TABLE #Job (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 -- 1 if currently running, otherwise zero

    current_step INT NOT NULL,

    current_retry_attempt INT NOT NULL,

    job_state INT NOT NULL)

    SET @StoredProc = db_name() + '..SpRunSQLAgentJob'

    /*

    -- Start the SQL Server Job

    */

    EXEC @Return = msdb..sp_start_job @ParmJobName

    IF @Return = 1

    BEGIN

    RAISERROR ('FAILED TO START SQL SERVER JOB ''%s'' FROM %s!', 18, 1, @ParmJobName, @StoredProc)

    RETURN(-9010)

    END

    -- Delay processing for 15 seconds.

    WAITFOR DELAY '00:00:15'

    -- Set variables

    SET @Loop_Flag = 1

    SET @JobNotRunning = 1

    -- Get SQL Server Job Id

    SELECT @job_id = job_id FROM msdb..sysjobs WHERE name = @ParmJobName

    /*

    -- Loop until job stops executing. When this looping process

    -- completes, there will be only 1 record in the #Job table.

    */

    WHILE @Loop_Flag = @JobNotRunning -- Loop while job is executing.

    BEGIN

    INSERT INTO #Job

    EXEC master.dbo.xp_sqlagent_enum_jobs 1, dbo, @job_id

    IF EXISTS (SELECT * FROM #Job AS j

    WHERE j.running = 1)

    TRUNCATE TABLE #Job

    ELSE

    SET @JobNotRunning = 0

    END

    -- Get the SQL Server Job's run date and time.

    SELECT @last_run_date = last_run_date, @last_run_time = last_run_time FROM #Job

    /*

    -- Determine whether or not the SQL Server Job executed successfully.

    */

    -- Get the final execution status of the SQL Server Job.

    SELECT @run_status = run_status FROM msdb..sysjobhistory

    WHERE job_id = @job_id

    AND step_id = 0

    AND run_date = @last_run_date

    AND run_time = @last_run_time

    DROP TABLE #Job

    -- Determine final executions status meaning.

    SELECT @run_status_name =

    CASE @run_status

    WHEN 1 THEN 'SQL SERVER JOB COMPLETED SUCCESSFULLY'

    WHEN 0 THEN 'SQL SERVER JOB FAILED'

    WHEN 3 THEN 'SQL SERVER JOB WAS CANCELLED'

    ELSE 'SQL SERVER JOBs STATUS IS ' + CAST(@run_status AS varchar(1))

    END

    -- Mark the job completion as successful or failing.

    IF @run_status = 1

    BEGIN

    SELECT @run_status_name

    END

    ELSE

    BEGIN

    SET NOCOUNT OFF

    RAISERROR ('%s', 18, 1, @run_status_name)

    RETURN(-9020)

    END

    SET NOCOUNT OFF

    RETURN(0)

  • noeld

    SSC Guru

    Points: 96590

    Dave, Steve, DarylAndDaryl

    Do you want to give this a shot? ....(IT WORKS FOR ME)

    select *

    from

    openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q

    --where q.name = MYJOBNAME

    HTH

     


    * Noel

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

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