Run job from server after completion of first job from different server.

Question

Run job from server after completion of first job from different server.

EasyBoy

SSCertifiable

Points: 5301
More actions
February 21, 2019 at 11:00 am

#377065

Hello,
We have two snapshot replication job running from two different server (domain).
UAT - Snapshot 1
DEV - Snapshot 2.
We need to run Snapshot job 2 on DEV only when Snapshot 1 completes on UAT. Both servers are configured through linked server using SQL account. Can anyone please help?
Thanks for your help in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

I had to do something similar. I ended up having job2 (that's dependant on job1) scheduled to run at frequent intervals, the first step would test the latest row on a table. If the row was from job1, job2 would run, if it wasn't it would not go on to the next step. When job2 started it would write a row to the table so it wouldn't run again until job1 had run again.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

one other option is for the job that does the first snapshot to start the second job on the other server as the last step on the job.

EasyBoy SSCertifiable Points: 5301 More actions · Answer 3

frederico_fonseca - Thursday, February 21, 2019 11:18 AM
one other option is for the job that does the first snapshot to start the second job on the other server as the last step on the job.

Would it be possible for you to provide any script for that?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

EasyBoy - Thursday, February 21, 2019 11:20 AM
frederico_fonseca - Thursday, February 21, 2019 11:18 AM
one other option is for the job that does the first snapshot to start the second job on the other server as the last step on the job.
Would it be possible for you to provide any script for that?

Sorry - don't have any that I can supply.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

EasyBoy - Thursday, February 21, 2019 11:20 AM
frederico_fonseca - Thursday, February 21, 2019 11:18 AM
one other option is for the job that does the first snapshot to start the second job on the other server as the last step on the job.
Would it be possible for you to provide any script for that?

You need to setup a linked server to execute a job on another server. Or use Powershell. Or use sqlcmd. Or use SSIS, etc

For a linked server, you would need to enable RPC for the linked server and setup the security context to support executing the job.
Then you would just call it using:
EXEC LinkedServerName.msdb.dbo.sp_start_job 'YourJobNameOnLinkedServer'

Sue

EasyBoy SSCertifiable Points: 5301 More actions · Answer 6

Sue_H - Thursday, February 21, 2019 11:56 AM
EasyBoy - Thursday, February 21, 2019 11:20 AM
frederico_fonseca - Thursday, February 21, 2019 11:18 AM
one other option is for the job that does the first snapshot to start the second job on the other server as the last step on the job.
Would it be possible for you to provide any script for that?
You need to setup a linked server to execute a job on another server. Or use Powershell. Or use sqlcmd. Or use SSIS, etc
For a linked server, you would need to enable RPC for the linked server and setup the security context to support executing the job.
Then you would just call it using:
EXEC LinkedServerName.msdb.dbo.sp_start_job 'YourJobNameOnLinkedServer'
Sue

Thanks for your quick help. It works for me.
Just add at last step of the first job
EXEC LinkedServerName.msdb.dbo.sp_start_job 'YourJobNameOnLinkedServer'