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
Last activity: May 5, 2025, 02:03 PM