Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
possible the same as this question –  shibormot Apr 25 '13 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). –  darin strait Apr 25 '13 at 15:50
add comment

migrated from stackoverflow.com Apr 25 '13 at 10:14

This question came from our site for professional and enthusiast programmers.

2 Answers

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

EXEC Server2.msdb.dbo.sp_start_job @job_name = N'Job2';
share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.