sql - 在 SQL Server 2012 中获取 SQL Server 代理作业的执行状态
问题描述
我正在尝试在 SQL Server 2012 中获取当前正在执行的作业状态。我尝试使用sp_help_job
过程来获取它,使用execution_status
as 参数也使用 run_status/status 但它没有返回任何值。
请帮忙
解决方案
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(
CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [NextRunDateTime]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (
SELECT
[job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
AND [Jobname] = @Jobname
ORDER BY [JobName]
推荐阅读
- ruby - 从 Foo::MyClass 调用模块 Foo 中的 Ruby 方法
- python - 训练损失正在减少,但验证损失没有
- twig - 如何设置树枝模板的权限
- json - “String”类型不是 FLUTTER 中“Widget”类型的子类型
- git - Git 责备查看所有提交历史
- android - Plot graph // 无法将 java.util.HashMap 类型的值转换为 String
- python - 将背景颜色添加到单元格 Google 工作表(python)
- tensorflow - 如何在 tensorflow 2.x 上训练大型数据集
- amazon-web-services - Terraform 更新之前创建的现有 Lambda 函数环境变量
- python - 如何在google colab上循环编写和保存csv文件?