April 10, 2008 at 1:39 pm
Here is the scenario: I have a JOB 1 executing on Server A. Upon completion -as a last step on JOB 1- I'd like to kick off another job -JOB2- on Server B.
As a side note, I know that sp_start_job can take @server_name variable. However, BOL is unclear when it caveats its use like: "The target server on which to start the job. server_name is nvarchar(128), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted."
Thanks in advance!
April 10, 2008 at 1:49 pm
I believe you need to have your source server set up as a MASTER JOB SERVER and the destination server one of the nodes assigned to the Master Server. Then you can use the @server_name parameter.
The other option is create a user with rights needed to execute sp_start_job on the linked server and call it using 4 parted naming like:
Exec linkedserver.msdb.dbo.sp_start_job parameters...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 10, 2008 at 2:12 pm
Thanks Jack! Well, the solution turned out to be quite simple -though probably not optimal.
Like you said, I can EXEC a stored procedure remotely via a linked server -just make sure RPC / RPC Out options are enabled on the linked server, otherwise, you'll run into a little bit of a pickle.
So, the final step on JOB 1 looks like this:
EXEC [linkedserver].[msdb].[dbo].sp_start_job @job_name="the name of the job on server 2"
The rest is history. Once more, thanks for pointing me in the right direction!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply