How To Check if Job is Running?

  • Calvin Lawson

    SSChampion

    Points: 11030

    Oops!  wrong forum post.  I was on the same track you guys were, though...so that's good

    Signature is NULL

  • Jeff Moden

    SSC Guru

    Points: 994647

    noeld - Wednesday, February 11, 2004 10:41 AM

    how about:

    CREATE  PROCEDURE IsJobRunning @JobName as varchar(100) = Null as

    --check!!

    if (@JobName IS NULL) RETURN -1

    Declare @theID as uniqueidentifier

    --get the ID

    Select @theID = A.job_id FROM OPENROWSET('SQLOLEDB','dbServerName'; 'Uname'; 'pwd' , 'SET FMTONLY OFF  exec msdb.dbo.sp_help_job')  as AWHEREA.[name]= @JobName

    if EXISTS(Select * FROM OPENROWSET('SQLOLEDB','dbServerName'; 'Uname'; 'pwd' , 'SET FMTONLY OFF  exec master.dbo.xp_sqlagent_enum_jobs 1,dbo')  as A WHERE A.[Job ID] = @theID AND       A.Running = 1 )

     RETURN 1ELSE RETURN 0

    I know this is a wicked old post and I don't know if it ever worked because this is the first time I've seen this post, but OPENROWSET doesn't currently work with anything that hits on an extended stored procedure because it can't determine what the meta-data is.  Here's the error that I get...

    Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 14]
    The metadata could not be determined because statement 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo' invokes an extended stored procedure (DLL).
    Of course, I might just be doing something wrong. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sue_H

    SSC Guru

    Points: 90260

    Jeff Moden - Tuesday, May 22, 2018 3:26 PM

    I know this is a wicked old post and I don't know if it ever worked because this is the first time I've seen this post, but OPENROWSET doesn't currently work with anything that hits on an extended stored procedure because it can't determine what the meta-data is.  Here's the error that I get...

    Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 14]
    The metadata could not be determined because statement 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo' invokes an extended stored procedure (DLL).
    Of course, I might just be doing something wrong. 😀

    I get the same error but I'm pretty sure this used to work back in the day. I remember scripts with the openrowset for this very thing - pretty sure I have one somewhere.
    But then the definition of the result set for xp_sqlagent_enum_jobs ended up being posted in quite a few places so it really wasn't needed. I guess now I should post it since you revived a 30 year old thread and someone may want it 🙂
    This is the definition I have for the results set for xp_sqlagent_enum_jobs. It still works (in the year 2018) and the spacing should be a mess since that's how this forum software is:

    CREATE TABLE #AgentEnumJobs
    (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    COLLATE database_default NULL,
    running     INT     NOT NULL,
    current_step    INT     NOT NULL,
    current_retry_attempt INT     NOT NULL,
    job_state     INT     NOT NULL)

    Sue

  • Jeff Moden

    SSC Guru

    Points: 994647

    Sue_H - Tuesday, May 22, 2018 6:35 PM

    Jeff Moden - Tuesday, May 22, 2018 3:26 PM

    I know this is a wicked old post and I don't know if it ever worked because this is the first time I've seen this post, but OPENROWSET doesn't currently work with anything that hits on an extended stored procedure because it can't determine what the meta-data is.  Here's the error that I get...

    Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 14]
    The metadata could not be determined because statement 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo' invokes an extended stored procedure (DLL).
    Of course, I might just be doing something wrong. 😀

    I get the same error but I'm pretty sure this used to work back in the day. I remember scripts with the openrowset for this very thing - pretty sure I have one somewhere.
    But then the definition of the result set for xp_sqlagent_enum_jobs ended up being posted in quite a few places so it really wasn't needed. I guess now I should post it since you revived a 30 year old thread and someone may want it 🙂
    This is the definition I have for the results set for xp_sqlagent_enum_jobs. It still works (in the year 2018) and the spacing should be a mess since that's how this forum software is:

    CREATE TABLE #AgentEnumJobs
    (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    COLLATE database_default NULL,
    running     INT     NOT NULL,
    current_step    INT     NOT NULL,
    current_retry_attempt INT     NOT NULL,
    job_state     INT     NOT NULL)

    Sue

    Thanks for the confirmation Sue.  Heh... I went digging in my OPENROWSET archives and found many code examples where calls to XPs used to work just fine.  Must be one of the "improvements" that MS made.  Just for the sake of curiosity, I have an old 2005 box at work.  I'll see if OPENROWSET on xp_sqlagent_enum_jobs there.

    Heh... Unfortunately, this is yet again another proof that "Change is inevitable... change for the better is not." 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sue_H

    SSC Guru

    Points: 90260

    Jeff Moden - Tuesday, May 22, 2018 9:06 PM

    Thanks for the confirmation Sue.  Heh... I went digging in my OPENROWSET archives and found many code examples where calls to XPs used to work just fine.  Must be one of the "improvements" that MS made.  Just for the sake of curiosity, I have an old 2005 box at work.  I'll see if OPENROWSET on xp_sqlagent_enum_jobs there.

    Heh... Unfortunately, this is yet again another proof that "Change is inevitable... change for the better is not." 😀

    I was going to try the oldest I have (2008) since I'm curious about when it broke. Just wondering if they killed that functionality with openrrowset when they introduced the resource database and started all of the openrowset calls that databases. Seems like they would have made changes to get the behaviors with those calls.

    Sue

  • nimbell

    SSChasing Mays

    Points: 631

    I think there's a much easier way, as provided by Microsoft here:

    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobactivity-transact-sql?redirectedfrom=MSDN&view=sql-server-2017#example

    SELECT sj.Name, 
    CASE
    WHEN sja.start_execution_date IS NULL THEN 'Not running'
    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
    END AS 'RunStatus'
    FROM msdb.dbo.sysjobs sj
    JOIN msdb.dbo.sysjobactivity sja
    ON sj.job_id = sja.job_id
    WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

     

     

     

Viewing 6 posts - 16 through 21 (of 21 total)

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