Thomas Rahier

Recently I had the problem that the SQL Server which I mainly use is not allowed to connect to an external server from which I have to load data that I then transform and load into a cube. But fortunately another SQL Server that I also use is allowed to connect to that external server and it is also allowed to my main server.
SyncJobExecOnDifferentServer_01

So my first idea was to create the job to load data from the External Server on Server 2 and then start this job from another job on Server 1 by using system stored procedure sp_start_job.
SyncJobExecOnDifferentServer_02

SyncJobExecOnDifferentServer_03

SyncJobExecOnDifferentServer_04

I executed the job and it ran sucessfully, but then I checked the job history on Server 1 and I saw that it took less than one second to execute the first step.
SyncJobExecOnDifferentServer_05

This seemed quite strange, because the job step is meant to load over 50 million records. I then checked the job history on Server 2 and it turned out that the job there needed much longer.
SyncJobExecOnDifferentServer_06

So it looked like sp_start_job created an asynchronous execution of the job on Server 2. This was bad, because I had to transform the data from the External Server and eventually load this data into my cube. After some research on the internet it turned out that sp_create_job offers no possibility to execute another job synchronously.

However, I found an indirect possibility to execute the job synchronously on Server 1. The trick is to create another job step that runs directly after the step that starts the job on Server 2. This new step creates and executes a temporary stored procedure that checks every 10 seconds in the msdb database on Server 2 if the job is still running there. And it stops when the job on Server 2 is finished. So this was the new job:
SyncJobExecOnDifferentServer_06

SyncJobExecOnDifferentServer_07

And this is the code for the new job step that waits for completion of the job on Server 2:
SyncJobExecOnDifferentServer_08

Now I executed the new job on Server 1 and checked the history after execution. And this time, the transformation steps and also the processing of the cube waited for the load from the External Server to complete and I had the correct data in the cube.
SyncJobExecOnDifferentServer_09

SyncJobExecOnDifferentServer_10