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?

  • 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