SQL Server Performance

start a job from a different server

Discussion in 'General DBA Questions' started by buenowight, Jul 12, 2007.

  1. buenowight New Member

    Hi.

    let us say I have a server A and server B.
    I want to know if i can kickoff a SQL server job on server B from server A.

    Any help is highly appreciated,

    Thanks,
    Bueno

  2. dineshasanka Moderator

    u can use sp_start_job

    sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id
    [,[@error_flag =] error_flag]
    [,[@server_name =] 'server_name']
    [,[@step_name =] 'step_name']
    [,[@output_flag =] output_flag]



    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. buenowight New Member

    Hi ,

    Thanks for you reply.

    Using your suggestion I tried to run a job on the different server, when i executed this code it shows that the job has been successfully started but in reality when i go to the othere server and check the job didn't start at all.

    Any clue??
  4. MohammedU New Member

    You can accomplish this in two ways...
    1. Create a dts package with destination server connection and run the sp_start_job as Dinesh mentioned...
    OR
    2. Create a linked server and execute sp_start_job procedure as linked server call..


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. alzdba Member

    just to add ...

    If you encounter restrictions, like you would only be authorized to launch a certain job, and you don't own the job, you can always ask the admin of the second server to define an alert that starts the job.

    All you have to do is provide a connection to the second server and raise the errornumber.



    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1032
  6. satya Moderator

    I believe you are talking about the Enterprise Manager, have you checked the processess (SP_WHO2) to see whether this is running or not.

    quote:Originally posted by buenowight

    Hi ,

    Thanks for you reply.

    Using your suggestion I tried to run a job on the different server, when i executed this code it shows that the job has been successfully started but in reality when i go to the othere server and check the job didn't start at all.

    Any clue??

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. Shri S New Member

    This reply is to help anyone else who may have the same question. If you run into permissions issues using sp_start_job use the following assuming you have xp_cmdshell enabled.
    EXEC xp_cmdshell 'SQLCMD -S <servername> -E -Q "EXEC msdb..sp_start_job ''<jobname>''"'

Share This Page