英文:
Run a job in management studio when another two jobs have been successful?
问题
是否有可能仅在我的服务器上的另外两个作业成功运行时，才在SQL Server Management Studio中运行一个与它们有依赖关系的作业？
提前致谢！
在互联网上我找不到相关信息。
英文:
I have a problem related to a job that has dependencies on two other jobs. My question is whether there is any possibility to run a job in SQL Server Management Studio only when another two jobs stored on my server have been successful.
Thanks in advance!
I can´t find anything in the internet.
答案1
得分: 2
基于https://dba.stackexchange.com/questions/279117/query-to-view-failure-detail-for-a-given-sql-agent-job-step中的答案，由"Scott Hodgin - retired"提供，可以通过查询msdb.dbo.sysjobhistory来检查作业的成功或失败情况。
msdb.dbo.sysjobhistory的文档位于https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-ver16，并指示[run_status]是一个整数列，当它等于1时表示成功。
作业名称存储在msdb.dbo.sysjobs中（https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobs-transact-sql?view=sql-server-ver16），您应该可以对其进行筛选。
您将需要运行一个查询（可能是一个存储过程），每4小时查看这些值，就像您在评论中请求的那样；如果两个条件都为真，则执行所需的操作。我尝试修改该查询以获取2个成功的作业事件，如下所示：
-- 每4小时运行一个 SQL Agent 作业，根据 OP 的要求
DECLARE @jobname1 sysname = 'thejobname1'
,@jobname2 sysname = 'thejobname2'
IF (
SELECT TOP 1 jh.run_status
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE j.name = @jobname1
ORDER BY jh.run_date DESC, jh.run_time DESC
) = 1
AND
(
SELECT TOP 1 jh.run_status
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE j.name = @jobname2
ORDER BY jh.run_date DESC, jh.run_time DESC
) = 1
BEGIN
SELECT 'success' AS [message]
END
英文:
Based off an answer from https://dba.stackexchange.com/questions/279117/query-to-view-failure-detail-for-a-given-sql-agent-job-step, by "Scott Hodgin - retired", there is a way to check success or failures of a job by querying msdb.dbo.sysjobhistory.
The documentation for msdb.dbo.sysjobhistory is at https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-ver16, and indicates that the [run_status] is an integer column and indicates success when it equals 1.
The job name is stored in msdb.dbo.sysjobs (https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobs-transact-sql?view=sql-server-ver16), which you should be able to filter for.
You would have to run a query (probably a stored procedure) looking at those values every 4 hours like you requested in comments; and if both conditions are true then you perform your desired actions. My attempt at modifying that query for 2 successful job events is below:
--Run a SQL Agent job every 4 hours according to OP
DECLARE @jobname1 sysname = 'thejobname1'
,@jobname2 sysname = 'thejobname2'
IF (
SELECT TOP 1 jh.run_status
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE j.name = @jobname1
ORDER BY jh.run_date DESC, jh.run_time DESC
) = 1
AND
(
SELECT TOP 1 jh.run_status
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE j.name = @jobname2
ORDER BY jh.run_date DESC, jh.run_time DESC
) = 1
BEGIN
SELECT 'success' AS [message]
END
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论