You may want to start a job on a second server (SQL02) when a job completes on a primary server (SQL01).
The SQL02 may be a reporting or staging sever, and the SQL01 may be the transnational server.
Sql Server has a T-SQL statement - sp_start_job - to start a job on the server where it is executed.
From the Microsoft Documentation, sp_start_job has the following parameters:
sp_start_job
{[@job_name =] 'job_name' | [@job_id =] job_id }
[ , [@error_flag =] error_flag]
[ , [@server_name =] 'server_name']
[ , [@step_name =] 'step_name']
[ , [@output_flag =] output_flag]
The @server_name parameter is relative to the server where it is executed.
If the parameter supported executing a job from another server it would be great, but no such luck!!!v
The solution is to set a SQL02 as a linked server, this allow that Sql would recognize the remote server as being local.
To configure a linked server on Sql 2014 (It may change from Sql Sever version to Sql Sever Version), follow the next steps:
1) Open Sql Server Management Studio (SSMS)
2) Expand the SQL01 node name
3) Expand "Server Objects"
4) Press the right mouse button on the ""Server Objects"
Example:
5) Write the Linked Server Name: SQL02 in this example
6) Set the server type to SQL Server
7) In Security set the your security settings
In this example the Local login is "sa" and the "Impersonate" checkbox is active. Also the "For a login not defined in the list above, connection will:" option is set to "Be made using this security context", defining the Remote login and password.
Example:
8) On "Server Options" allow "RPC Out"
Example:
9) Press OK and if the everithing is correctly defined the linked server is configured
After configuring the linked server the job can be started on the SQL02 from the SQL01.
To test open a query window on SSMS in the SQL01 server and execute the SP to start the job:
EXEC [SQL02].[msdb].[dbo].[sp_start_job] @job_name = N'MyJob';
Where "MyJob" is the name of the job to start on SQL02
Note that the job is started, but the sp_start_job does not wait for the job "MyJob" to complete the execution.
