locked
How to start a SQL job on another server? RRS feed

  • Question

  • Here actually are 2 questions.

    1. After a SQL Job finish on server 1 I want to trigger a SQL Job on Server 2.

    Is there any typical solutions from Microsoft?

    2. what are some popular ways to start a SQL job on different server.

     

    Friday, September 23, 2011 7:27 PM

Answers

  • Hi,

     

    Could you use a link server between server 1 and 2. Then call from the step 2 of your job the link server with  the stored procedure : EXEC msdb.dbo.sp_start_job ....

    http://msdn.microsoft.com/en-us/library/ms186757.aspx

     


    Michel DEGREMONT | Microsoft | PFE Team , my blog SQL Server
    • Proposed as answer by Janos BerkeMVP Friday, September 23, 2011 11:20 PM
    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 4:40 AM
    Friday, September 23, 2011 7:50 PM
  • If the command shell is enabled use EXEC xp_cmdshell 'SQLCMD -<ServerName> -E -Q "EXEC msdb..sp_start_job <jobname>"'
    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 4:40 AM
    • Edited by NBOsm Friday, May 31, 2013 3:52 PM
    Friday, September 23, 2011 10:19 PM

All replies

  • Hi,

     

    Could you use a link server between server 1 and 2. Then call from the step 2 of your job the link server with  the stored procedure : EXEC msdb.dbo.sp_start_job ....

    http://msdn.microsoft.com/en-us/library/ms186757.aspx

     


    Michel DEGREMONT | Microsoft | PFE Team , my blog SQL Server
    • Proposed as answer by Janos BerkeMVP Friday, September 23, 2011 11:20 PM
    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 4:40 AM
    Friday, September 23, 2011 7:50 PM
  • If the command shell is enabled use EXEC xp_cmdshell 'SQLCMD -<ServerName> -E -Q "EXEC msdb..sp_start_job <jobname>"'
    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 4:40 AM
    • Edited by NBOsm Friday, May 31, 2013 3:52 PM
    Friday, September 23, 2011 10:19 PM
  • Create an SSISDB package on Server1 that calls the job on Server2

    For the SqlStatement, use: EXEC msdb.dbo.sp_start_job N'NameOfYourJobOnServer2'

    In your Job on Server1, add the following step 

    Type:Sql Server Integration Services Package

    RunAs: SqlServer Agent Account

    Package source:SSIS Catalog

    Server: Server1

    ..then select the SSIDB package you just created


    • Edited by NeilBe Wednesday, October 9, 2019 6:16 PM
    Wednesday, October 9, 2019 6:14 PM