Running a Job from a Stored Procedure in another server?

Question

How can I run a job from another server using a stored procedure?

Let's say

• server 1 : db1
• server 2 : db2
• username : testssis
• password : testssispass
• sqljob found in db2: job1

Here is the part im lost with how can I make this work?

xp_cmdshell + osql command -U: -P: + sp_startjob

Answer 1

With a linked server - much more reliable and secure than xp_cmdshell:

EXEC Server2.msdb.dbo.sp_start_job @job_name = N'Job2';

Answer 2

You can have the Sql job in a stored Procedure in server B. Add server B as linked server to Server A, Execute the stored Procedure from server A:

EXEC [RemoteServer].DatabaseName.DatabaseOwner.StoredProcedureName

Answer 3

Create a small SSIS Package that executes the Procedure. Deploy the Project to your Agent Server. Execute the Package using an Agent Job Step.

My use case: I had a Procedure to prune the tail of a large database of event records and wanted to be sure that it would not interfere with writing to the Head. I made sure the deletion Procedure would halt itself after a short time window, then added an Agent step to follow after the one that writes to the Head.