1

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
2
  • possible the same as this question
    – shibormot
    Apr 25, 2013 at 4:33
  • Certainly does seem like a duplicate. to me, Rad's answer seems superior to Timbo's. Using osql and xp_cmdshell seems brittle and may lead to security problems (hardcoded, cleartext passwords). Apr 25, 2013 at 15:50

3 Answers 3

6

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

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

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.