How to get output of SP_HELP_JOB into a script?

  • Richard Culpan

    Old Hand

    Points: 398

    Trying to write a T-SQL script to check up on the status of a replication merge agent job that randomly stops.

    I tried creating a temporary table and using the INSERT .. EXEC kind of syntax - however sp_help_job uses insert .. exec commands internally and these cannot be nested

    So, a statement such as:

    insert into #TempJobTable

       execute sp_help_job @category_name=N'REPL-Merge', @enabled=1, @execution_status=4

    results in an error such as:

    Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

    An INSERT EXEC statement cannot be nested.

    I also tried to do the status test implicitly by specifiying the input parameters to sp_help_job so that it only returned information about the job I was interested in; with the paramaters I used the rowset was empty if the job was running and had one row if the job was stopped. However sp_help_job did not set @@rowcount to the number of rows that it returned (it was still set from the outcome of earlier statements in my script where I was looking up the job ID)    

    Is there a way in which I can test the output of sp_help_job in a script, by getting it into a temporary table or into a bunch of local variables

    Thanks,

    Richard

  • Jen-169507

    Right there with Babe

    Points: 776

    you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

     

    HTH

  • Sergiy

    SSC Guru

    Points: 109666

    Not sure it will work, but why not try?

    SET @SQL = insert into #TempJobTable

      EXEC('execute sp_help_job @category_name=N''REPL-Merge'', @enabled=1, @execution_status=4')

     

  • Daryl AZ

    SSCrazy

    Points: 2824

    SELECT * INTO #JobInfo

    FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'

    , 'set fmtonly off exec msdb.dbo.sp_help_job @execution_status=4')

    This works to get the info intoa temp table. The main advantage is no need to define the temp table. The select * into handles column creation.

    I am not sure what the SET @sql = insert ... is doing. I was hoping it was simpler method to populate the tmp table, but I all i can see is populating the @sql variable.

    cheers

  • Richard Culpan

    Old Hand

    Points: 398

    Thanks for all the replies,

    Using the OPENROWSET method worked nicely.

     Note: the 'set fmtonly off' part of the command is necessary; the default in this case appears to be 'ON' which is not very useful and causes an error such as:

    Could not process object 'exec msdb.dbo.sp_help_job @execution_status=4'. The OLE DB provider 'sqloledb' indicates that the object has no columns.

    Thanks,

    Richard

  • Richard Culpan

    Old Hand

    Points: 398

    In case it helps anyone else, here is my completed script to check the status of the merge replication agent (that was started when the SQL Server Agent started -  and should be running continuously). If the merge agent is found to be stopped a message is logged and it is restarted.

    This can be run as a job on the server where the replication agent is running (in my case I was using push subscriptions so the script runs at the Distributor).

     

    declare @TempJobId uniqueidentifier, @TempJobName sysname

    set nocount on

    use msdb

    /* Search in MSDB for enabled replication merge jobs that are scheduled to run when SQL Server Agent starts */

    declare TempJobCursor cursor local fast_forward for

       select j.job_id, j.name from syscategories c, sysjobs j, sysjobschedules s where

          j.job_id = s.job_id and

          j.category_id = c.category_id and

          c.name = N'REPL-Merge' and

          s.freq_type = 64 and

          j.enabled = 1 and

          s.enabled = 1

    open TempJobCursor

    fetch next from TempJobCursor into @TempJobId, @TempJobName

    while @@fetch_status = 0

    /* Found a matching job - check its status */

    begin

       exec ('declare @TempStatus int

             select @TempStatus = current_execution_status from openrowset(''sqloledb'',

                ''server=(local);trusted_connection=yes'',

                ''set fmtonly off execute msdb.dbo.sp_help_job @job_id=''''' + @TempJobId +

                ''''', @job_aspect=''''JOB'''', @execution_status=4'')')

       if @@rowcount = 1

    /* Job found to be in the idle (not running) state; log an event and restart it */

       begin

          exec ('execute sp_start_job @job_id=''' + @TempJobId + '''')

          raiserror ('Watchdog: Restarted Merge Replication agent %s', 0, 1, @TempJobName) with log

       end

       fetch next from TempJobCursor into @TempJobId, @TempJobName

    end

    /* Clean up */

    close TempJobCursor

    deallocate TempJobCursor

    set nocount off

    HTH

    Richard

  • raoul.teeuwen

    SSC Enthusiast

    Points: 198

    I also needed to find a solution and eventually found a simple one and documented it on my blog: http://raoulteeuwen.blogspot.com/2008/09/query-job-last-run-status-in-ms-sql.html .

  • RichardBo

    Ten Centuries

    Points: 1200

    I was looking at this and although I've decided on the OPENROWSET method, I have got the table structure returned by sp_help_job if that helps anyone.

    CREATE TABLE #JobInfo(

    [job_id] [uniqueidentifier] NULL,

    [originating_server] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [enabled] [tinyint] NULL,

    [description] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [start_step_id] [int] NULL,

    [category] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [owner] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [notify_level_eventlog] [int] NULL,

    [notify_level_email] [int] NULL,

    [notify_level_netsend] [int] NULL,

    [notify_level_page] [int] NULL,

    [notify_email_operator] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [notify_netsend_operator] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [notify_page_operator] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [delete_level] [int] NULL,

    [date_created] [datetime] NULL,

    [date_modified] [datetime] NULL,

    [version_number] [int] NULL,

    [last_run_date] [int] NOT NULL,

    [last_run_time] [int] NOT NULL,

    [last_run_outcome] [int] NOT NULL,

    [next_run_date] [int] NOT NULL,

    [next_run_time] [int] NOT NULL,

    [next_run_schedule_id] [int] NOT NULL,

    [current_execution_status] [int] NOT NULL,

    [current_execution_step] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [current_retry_attempt] [int] NOT NULL,

    [has_step] [int] NULL,

    [has_schedule] [int] NULL,

    [has_target] [int] NULL,

    [type] [int] NOT NULL

    )

  • pdornai2

    Valued Member

    Points: 52

    Thank you, that saved me some time!

  • michael.a.keleher

    SSC Journeyman

    Points: 83

    Tested on SQL Server 2005 and 2012. This script select jobs that are currently running.

    declare @CurrentJobs table

    (

    [Job ID] uniqueidentifier,

    [Last Run Date] varchar(255),

    [Last Run Time] varchar(255),

    [Next Run Date] varchar(255),

    [Next Run Time] varchar(255),

    [Next Run Schedule ID] varchar(255),

    [Requested To Run] varchar(255),

    [Request Source] varchar(255),

    [Request Source ID] varchar(255),

    [Running] varchar(255),

    [Current Step] varchar(255),

    [Current Retry Attempt] varchar(255),

    [State] varchar(255)

    )

    insert into @CurrentJobs

    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

    select *

    from @CurrentJobs cj

    join msdb.dbo.sysjobs sj

    on

    cj.[Job ID]= sj.job_id

    where Running=1

  • prakashanand2223

    SSC Rookie

    Points: 48

    Jen-169507 - Thursday, February 16, 2006 8:58 PM

    you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

    HTH

    Hi,
    Can you please give me the script o the same?

    Prakash Anand

  • Jeff Moden

    SSC Guru

    Points: 993661

    prakashanand2223 - Saturday, September 22, 2018 1:13 AM

    Jen-169507 - Thursday, February 16, 2006 8:58 PM

    you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

    HTH

    Hi,
    Can you please give me the script o the same?

    Prakash Anand

    You should try making the mods yourself for two reasons... 1) you have to support it and 2) it's another good way of learning.  Use sp_helptext to get the code and have at it.

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

  • Michael L John

    One Orange Chip

    Points: 25632

    prakashanand2223 - Saturday, September 22, 2018 1:13 AM

    Jen-169507 - Thursday, February 16, 2006 8:58 PM

    you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

    HTH

    Hi,
    Can you please give me the script o the same?

    Prakash Anand

    You are asking for help, or actually you are asking someone to do your work, on a thread that is 9 years old who has not logged onto this site for 9 years. 

    Why don't you start a new thread, explain what your issue may be, what you want to do, and what you have tried.  You will probably get help faster, and more importantly, help that is specific to your needs.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Jeff Moden

    SSC Guru

    Points: 993661

    Michael L John - Saturday, September 22, 2018 4:40 PM

    prakashanand2223 - Saturday, September 22, 2018 1:13 AM

    Jen-169507 - Thursday, February 16, 2006 8:58 PM

    you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

    HTH

    Hi,
    Can you please give me the script o the same?

    Prakash Anand

    You are asking for help, or actually you are asking someone to do your work, on a thread that is 9 years old who has not logged onto this site for 9 years. 

    Why don't you start a new thread, explain what your issue may be, what you want to do, and what you have tried.  You will probably get help faster, and more importantly, help that is specific to your needs.

    Heh... either that or go back through this very thread and see that there are at least two other solutions already available.  My favorite is the OPENROWSET one but it does sometimes cause problems depending on how the procedure being called was written.  Sometimes it's written in such a fashion that it doesn't expose the meta-data necessary to successfully use SELECT/INTO to build a Temp Table from the results.

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

  • prakashanand2223

    SSC Rookie

    Points: 48

    Michael L John - Saturday, September 22, 2018 4:40 PM

    prakashanand2223 - Saturday, September 22, 2018 1:13 AM

    Jen-169507 - Thursday, February 16, 2006 8:58 PM

    you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

    HTH

    Hi,
    Can you please give me the script o the same?

    Prakash Anand

    You are asking for help, or actually you are asking someone to do your work, on a thread that is 9 years old who has not logged onto this site for 9 years. 

    Why don't you start a new thread, explain what your issue may be, what you want to do, and what you have tried.  You will probably get help faster, and more importantly, help that is specific to your needs.

    Hi John,
    I tried something different and the sql procedure which is mentioned below will fetch all the sql jobs getting executed at different servers (condition is that all the servers names must be stored in server_list table).

    As per my requirement I also appended stop job proc into the same procedure.
    If not required then comment out those codes.

    --SQL PROCEDURE START

    ALTER PROCEDURE [dbo].[get_all_servers_job_executing_list]
            
    AS
    BEGIN
        SET NOCOUNT ON;

      declare @servercount varchar(max);
    declare @servername varchar(max);
     select @servercount = count(*) from server_list --(server_list is the table where all the server names are stored. It must have a column ---server_name) ;
     declare @jobname varchar(max)
     declare @query varchar (max);
      declare @query1 varchar (1000);
         declare @query2 varchar (1000);
    declare @i varchar(max) = 1;
    declare @JOBcount varchar(max);
     declare @j varchar(max) = 1;
    create TABLE records1
    (
    [Job ID] UNIQUEIDENTIFIER,[Last Run Date] CHAR(8),[Last Run Time] CHAR(6),[Next Run Date] CHAR(8),[Next Run Time] CHAR(6),[Next Run Schedule ID] INT,
    [Requested To Run] INT,[Request Source] INT,[Request Source ID] SQL_VARIANT,[Running] INT,[Current Step] INT,[Current Retry Attempt] INT,[State] INT
    )
    while (@i <= @servercount )
        begin 
             WITH s AS (
                SELECT (ROW_NUMBER() OVER (ORDER BY server_name)) as row,server_name
                FROM server_list )
            SELECT @servername= server_name FROM s WHERE row = @i
        set @query1= 'INSERT INTO records1 EXECUTE ' + @servername +' .master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1,@job_owner=''0x4A6F6E47757267756C'''
        --print (@query1)
        exec (@query1);
    alter table records1 add server_name varchar(100)
    update records1 set server_name = @servername

    set @query = 'SElECT
    t.server_name,
    t.[Request Source ID] [Requester]
    ,t.[Job ID] [JobID]
    ,sj.[name] [JobName]
    ,sjs.[step_id] [StepID]
    ,sjs.[step_name] [StepName]
    into records2
    FROM records1 t
    INNER JOIN '+@servername+'. msdb.dbo.sysjobs sj ON t.[Job ID] = sj.[job_id]
    INNER JOIN '+@servername+'. msdb.dbo.sysjobsteps sjs ON sjs.[job_id] = sj.[job_id]
    AND t.[Job ID] = sjs.[job_id]
    AND t.[Current Step] = sjs.[step_id]
    INNER JOIN
    (
      SELECT * FROM '+@servername+' . msdb.dbo.sysjobactivity d
      WHERE EXISTS
      (
      SELECT 1
      FROM ' +@servername+' . msdb.dbo.sysjobactivity l
      GROUP BY l.[job_id]
      HAVING l.[job_id] = d.[job_id]
      AND MAX(l.[start_execution_date]) = d.[start_execution_date]
      )
    ) sja
    ON sja.[job_id] = sj.[job_id]
    LEFT JOIN (SELECT SUBSTRING([program_name],30,34) p,[session_id] FROM ' +@servername+' .msdb . sys.dm_exec_sessions
    WHERE [program_name] LIKE ''SQLAgent - TSQL JobStep%'') es
    ON CAST('''' AS XML).value(''xs:hexBinary(substring(sql:column("es.p"),3))'',''VARBINARY(MAX)'') = sj.[job_id]'

    --print (@query)
    exec (@query)
    alter table records1 drop column server_name;

    SELECT * INTO RECORDS3 FROM records2
    DROP TABLE mi_uwhca.dbo. records2

      select @JOBcount = count(*) from RECORDS3 ;
         while (@J <= @JOBcount )
        begin 
             WITH P AS (
                SELECT (ROW_NUMBER() OVER (ORDER BY JOBname)) as row,JOBname
                FROM RECORDS3 )
            SELECT @JOBname= JOBname FROM P WHERE row = @j
        set @query2 = 'exec ' +@servername +'. msdb.[dbo].[sp_stop_job]' + @JOBNAME
            
        --print(@query2)
        exec (@query2)
            PRINT @JOBNAME + ' STOPPED EXEXCUTING'
            SET @j = @j+1
            END
            
    insert into records4 select * from records3 --where jobname in (select distinct jobname from RECORDS3 )
    drop table RECORDS3
        set @i= @i+1
        truncate table records1
        end

    drop table records1
    END
    --SQL PROCEDURE END

    Results will be saved in table records4.

    Please let me know if any further  query arises.

    Thank You,
    Prakash Anand

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

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