I am unable to use the @server_name param of this sp. I can do this
exec msdb..sp_start_job
@server_name='shanghai\shanghai1',@job_id='7C256C70-D64D-4C1F-8D98-CA44C51698FC'

what I need to do is run this job on shanghai\shanghai1 instance from
ServerB but it's not working. If the job exists on ServerB, it runs.
otherwise it says that 'the job_id' doesn't exist. please let me know if i'm
doing something wrong. thank you

The @server_name parameter of sp_start_job is used to execute a Job on a
Target Server.  The Target server must be enrolled in Multiserver
Administration (MSX) with the Master server where sp_start_job is being
executed in order for the job to be executed from the Master Server.

For more details regarding Multiserver Administration refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_automate_7ir2.asp

An easy way to execute a job on a remote server is to use SQLDMO:

Dim oSQLServer

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

oSQLServer.LoginSecure = True
' Set servername as the server where the job is to be executed
oSQLServer.Connect "serverName"

' Set JobName as the name of the job to execute
oSQLServer.JobServer.Jobs("JobName").Start

oSQLServer.DisConnect
Set oSQLServer = Nothing


HTH

- Peter Ward
WARDY IT Solutions


Show quoteHide quote

smalldatetime or integer
Activity Monitor
Problem with SET QUOTED_IDENTIFIER ON
Theory on composit key
Data is truncated /SQL query help