Sample Header Ad - 728x90

Chaining remote SQL Server Agent Jobs with success/failure feedback

0 votes
1 answer
309 views
I have 2 Microsoft SQL Server 2014 (SP3-CU4) instances, residing on different servers. ServerA has a linked server, pointing to ServerB\InstanceB. Both instances have a job, and JobA triggers the remote execution of JobB. So the execution of the jobs is chained. Schematic of setup - ServerA\InstanceA - Server Objects - Linked Servers - LinkedServerToB (target: ServerB\InstanceB) - SQL Server Agent - Jobs - JobA - Step1 = Execute some local code - Step2 = EXEC [LinkedServerToB].[msdb].[dbo].sp_start_job @job_name="JobB" - ServerB\InstanceB - SQL Server Agent - Jobs - JobB This all works fine. Except for when JobB fails. When that happens, JobA.Step2 doesn't know about that. All JobA.Step2 did was *trigger* the remote execution of JobB, and then it's done. So JobA in its entirety runs successfully, despite the failure of JobB. Although I understand why it's behaving that way, its not the outcome I was looking for. Instead, I'd like the parent job (JobA) to fail when a child job (JobB) fails. How can I get JobA to fail when JobB fails? Maybe some sort of success/failure feedback between them, but I don't know how to implement that.
Asked by Bjorn Mistiaen (101 rep)
May 29, 2020, 03:27 PM
Last activity: May 5, 2025, 02:03 PM