SQL Server Run a job in management studio when another two jobs have been successful?

g0czyy6m  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(158)

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.

9o685dep

9o685dep1#

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:

  1. --Run a SQL Agent job every 4 hours according to OP
  2. DECLARE @jobname1 sysname = 'thejobname1'
  3. ,@jobname2 sysname = 'thejobname2'
  4. IF (
  5. SELECT TOP 1 jh.run_status
  6. FROM msdb.dbo.sysjobs AS j
  7. INNER JOIN msdb.dbo.sysjobsteps AS js
  8. ON js.job_id = j.job_id
  9. INNER JOIN msdb.dbo.sysjobhistory AS jh
  10. ON jh.job_id = j.job_id AND jh.step_id = js.step_id
  11. WHERE j.name = @jobname1
  12. ORDER BY jh.run_date DESC, jh.run_time DESC
  13. ) = 1
  14. AND
  15. (
  16. SELECT TOP 1 jh.run_status
  17. FROM msdb.dbo.sysjobs AS j
  18. INNER JOIN msdb.dbo.sysjobsteps AS js
  19. ON js.job_id = j.job_id
  20. INNER JOIN msdb.dbo.sysjobhistory AS jh
  21. ON jh.job_id = j.job_id AND jh.step_id = js.step_id
  22. WHERE j.name = @jobname2
  23. ORDER BY jh.run_date DESC, jh.run_time DESC
  24. ) = 1
  25. BEGIN
  26. SELECT 'success' AS [message]
  27. END
展开查看全部

相关问题