Chaining remote SQL Server Agent Jobs with success/failure feedback 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.
You need to write a loop which polls serverB and checks whether the job has finished and what the outcome was. On each iteration of the loop make the job sleep using waitfor delay '00:02:00' (a 2 minute delay).

To check if the status on a job see the link below

https://sqljunkieshare.com/2014/11/21/get-sql-agent-job-status/