主页 / dba / 问题 / 192499
Accepted
Pரதீப்
Asked: 2017-12-07 00:37:16 +0800 CST

如何找到正在运行的作业的会话 ID

  • 772

试图获取执行作业的会话 id

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x 
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select * 
    from msdb.dbo.sysjobs j 
    inner join JobDetails jd on j.job_id = jd.Job_Id

但它会引发以下错误

消息 8169，级别 16，状态 2，第 47 行从字符串转换为唯一标识符时转换失败。

我试图将其job_id转换为varbinary但它没有产生任何结果

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x 
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select * 
    from msdb.dbo.sysjobs j 
    inner join JobDetails jd on cast(j.job_id as varbinary) = jd.Job_Id

但是当我像这样复制粘贴job_id时cte

select job_id, name 
    from msdb..sysjobs 
    where job_id = 0x128A47A31EAB8F4DA1AD852093D815F5

它有效。知道如何解决此查询

sql-server t-sql

1 个回答

  1. Best Answer

    我曾经CONVERT将 msdb.dbo.sysjob.job_id 转换为BINARY(16)，然后使用CONVERT（和style）将其转换为 varchar(max) ，它可以与你的 cte 产生的内容进行比较。您可以CONVERT通过查看CONVERT的文档来了解更多关于和样式的信息。这在我的测试中有效。

    尝试这个：

    ;
WITH JobDetails
AS (
    SELECT DISTINCT Job_Id = left(intr1, charindex(':', intr1) - 1)
        ,Step = substring(intr1, charindex(':', intr1) + 1, charindex(')', intr1) - charindex(':', intr1) - 1)
        ,SessionId = spid
    FROM master.dbo.sysprocesses x
    CROSS APPLY (
        SELECT replace(x.program_name, 'SQLAgent - TSQL JobStep (Job ', '')
        ) cs(intr1)
    WHERE spid > 50
        AND x.program_name LIKE 'SQLAgent - TSQL JobStep (Job %'
    )
SELECT *
FROM msdb.dbo.sysjobs j
INNER JOIN JobDetails jd ON jd.Job_Id = convert(VARCHAR(max), convert(BINARY (16), j.job_id), 1)
    • 5

相关问题