查询msdb sysjobhistory并加入Integration Services目录上的失败包

Question

我目前有一个查询，它从msdb sysjobhistory中提取失败作业的数据。这很好，但包失败原因的错误消息没有用处，因为它会将我发送到Integration Services目录以获取错误消息。我更希望有一个查询来获取作业名称、失败的步骤以及失败的原因。

我使用下面的查询来获取失败的作业。你能帮我完成查询吗。

USE MSDB
GO
SELECT j.name JobName,h.step_name StepName, 
--CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) AS RunDate, 
CONVERT(DATETIME, CONVERT(CHAR(8), h.run_date, 112) + ' ' + CONVERT(CHAR(8), STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':'), 108)) AS RunDate,
--h.run_date,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') AS RunTime, 
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') AS RunDuration, 
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') AS StepDuration,
case h.run_status when 0 then 'Failed'
when 1 then 'Succeded' 
when 2 then 'Retry' 
when 3 then 'Cancelled' 
when 4 then 'In Progress' 
end as ExecutionStatus, 
h.message MessageGenerated,
    H.Server
    --INTO Maintenance.dbo.Intergration_Logs
    FROM sysjobhistory h inner join sysjobs j
    ON j.job_id = h.job_id
    --WHERE h.run_date = CONVERT(VARCHAR(10),GETDATE(),112)

Answer 1

有关作业失败原因的信息存储在sysjobhistory中。

如果你正在寻找一个能切中要害的东西，当一项工作失败时，它能让你知道你需要知道的基本信息（工作名称、失败的步骤和失败的原因），这样的东西可能会奏效：

WITH T1 AS (SELECT [job_id], [instance_id], [message]
    FROM sysjobhistory
    WHERE run_status = 0 AND step_name = '(Job Outcome)')
, T2 AS (SELECT [job_id], [instance_id]
    FROM T1
    WHERE [message] NOT LIKE '%the last step to run was%'
    UNION
    SELECT jh.[job_id], jh.[instance_id]
    FROM  sysjobhistory jh
    JOIN T1 ON T1.[instance_id] - 1 = jh.[instance_id]
    WHERE T1.[message] LIKE '%the last step to run was%')
SELECT j.[name], jh.[step_id], jh.[step_name], jh.[message]
FROM sysjobs j
JOIN sysjobhistory jh ON jh.[job_id] = j.[job_id]
JOIN T2 ON T2.[instance_id] = jh.[instance_id]