如何知道 SQL AGENT JOB 的状态

这个问题，需要查询两个：

MSDB：

SELECT  j.job_id , j.name
FROM    msdb..sysjobs j
WHERE   EXISTS ( SELECT 1
                 FROM   msdb..sysjobsteps sj
                        INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id
                 WHERE  sj.job_id = j.job_id
                        AND ja.start_execution_date <= GETDATE()
                        AND ja.stop_execution_date IS NULL
                        AND ja.session_id = ( SELECT    MAX(ja2.session_id)
                                              FROM      msdb..sysjobactivity ja2
                                              WHERE     ja2.job_id = j.job_id
                                            ) )

另一个，SYE.DM_EXEC_SESSIONS:

SELECT  j.job_id, j.name
FROM    msdb..sysjobs j
WHERE   dbo.uf_HexToChar(j.job_id, 16) IN (
        SELECT  SUBSTRING(sp.[program_name], 32, 32)
        FROM    sys.dm_exec_sessions sp
        WHERE   sp.status IN ( 'running', 'sleeping' )

上面有个自定义函数：

CREATE FUNCTION [dbo].[uf_HexToChar]
    (
      @binaryValue VARBINARY(100) ,
      @numBytes INT
    )
RETURNS VARCHAR(200)
AS
    BEGIN

        DECLARE @i VARBINARY(10) ,
            @hexdigits CHAR(16) ,
            @s VARCHAR(100) ,
            @h VARCHAR(100) ,
            @currentByte SMALLINT
        SET @hexdigits = '0123456789ABCDEF'
        SET @currentByte = 0
        SET @h = ''
          -- process all  bytes
        WHILE @currentByte < @numBytes
            BEGIN
                SET @currentByte = @currentByte + 1
          -- get first character of byte
                SET @i = SUBSTRING(CAST(@binaryValue AS VARBINARY(100)), @currentByte, 1)
          -- get the first character
                SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
          -- shift over one character
                SET @i = @i / 16
          -- get the second character
                SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
                    + @s
          -- build string of hex characters
                SET @h = @h + @s
            END
        RETURN(@h)
    END
GO