Synchronous Job Execution on a different Server
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. 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. 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. 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.
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: And this is the code for the new job step that waits for completion of the job on Server 2: 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.