Home Forums SQL Server 7,2000 T-SQL How to get output of SP_HELP_JOB into a script? RE: How to get output of SP_HELP_JOB into a script?

  • 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-2 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-2
        set @query2 = 'exec ' +@servername +'. msdb.[dbo].[sp_stop_job]' + @JOBNAME
            
        --print(@query2)
        exec (@query2)
            PRINT @JOBNAME + ' STOPPED EXEXCUTING'
            SET @j-2 = @j-2+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