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条答案
按热度按时间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: