0

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.

  • 1
    Does JobB need to be a SQL Agent job? Could you instead implement the work done by it as a stored procedure, or series of stored procedures? That should enable synchronous calls and handling of returned errors. – Philip Kelley May 29 at 16:55
  • @PhilipKelley JobB doesn't need to be a SQL Agent job. It acutally just calls a bunch of stored procedures on the remote machine. So you mean I should call those remote procedures within JobA? – Bjorn Mistiaen May 29 at 17:11
  • 1
    That is definitely an option. It should permit better control, since execution should be synchronous (i.e. you don't "launch a job and done", as you pointed out). Note that I use "should" a lot--when it comes to Linked Servers, things can get very complex very quickly. – Philip Kelley May 29 at 18:15
  • Are there other options that don't get very complex very quickly? – Bjorn Mistiaen May 29 at 19:02
  • Possibly not. I found Linked Servers ugly to configure and hard to support. Another option is to have something outside the databases manage your multi-database task list (I've used Windows Scheduler in the past--ugly, but functional)... but then you have to write it, configure it with appropriate access, and everything else. Which turns into a big pile of "it depends on what you're actually trying to do", to determine how best to tackle it. It could be your current plan is a "best fit". – Philip Kelley May 29 at 20:36
0

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/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.