Start job on another server

  • Hi everyone, I am trying to start a job on a sql 2008 r2 server from a sql 2012 server. I have the following code:

    exec msdb.dbo.sp_start_job @job_id = '74882103-8596-4578-9C58-C43806113E17'

    ,@server_name = 'Server 2';

    And I get the following error:

    Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41

    The specified @job_id ('74882103-8596-4578-9C58-C43806113E17') does not exist.

    I have also tried @job_name to no avail. Even tried using a 4 part name "Server1.msdb.dbo.sp_start_job" and I get the same error. Any help would be appreciated! Thanks.

  • shahgols (3/10/2014)


    Hi everyone, I am trying to start a job on a sql 2008 r2 server from a sql 2012 server. I have the following code:

    exec msdb.dbo.sp_start_job @job_id = '74882103-8596-4578-9C58-C43806113E17'

    ,@server_name = 'Server 2';

    And I get the following error:

    Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41

    The specified @job_id ('74882103-8596-4578-9C58-C43806113E17') does not exist.

    I have also tried @job_name to no avail. Even tried using a 4 part name "Server1.msdb.dbo.sp_start_job" and I get the same error. Any help would be appreciated! Thanks.

    How is your PowerShell? 🙂 Seriously this would be a great project to start learning PoSH and you would be able to do what you need with it.

    You have a couple of other options too, but since you would like to do it with T-SQL you could use a linked server and run the following command:EXEC [linkedServerName].msdb.dbo.sp_start_job @job_id = '74882103-8596-4578-9C58-C43806113E17' =



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I use PowerShell in a job step, it is very easy...

    $sqlConnection = new-object System.Data.SqlClient.SqlConnection

    $sqlConnection.ConnectionString = 'server=myserver;integrated security=TRUE;database=msdb'

    $sqlConnection.Open()

    $sqlCommand = new-object System.Data.SqlClient.SqlCommand

    $sqlCommand.CommandTimeout = 0

    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText= "exec dbo.sp_start_job myjobname"

    $result = $sqlCommand.ExecuteNonQuery()

    $sqlConnection.Close()

  • shahgols (3/10/2014)


    Hi everyone, I am trying to start a job on a sql 2008 r2 server from a sql 2012 server. I have the following code:

    exec msdb.dbo.sp_start_job @job_id = '74882103-8596-4578-9C58-C43806113E17'

    ,@server_name = 'Server 2';

    And I get the following error:

    Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41

    The specified @job_id ('74882103-8596-4578-9C58-C43806113E17') does not exist.

    I have also tried @job_name to no avail. Even tried using a 4 part name "Server1.msdb.dbo.sp_start_job" and I get the same error. Any help would be appreciated! Thanks.

    Yeah, you need to create a linked server for Server1 and then execute.

    --

    SQLBuddy

  • Hi everyone, thanks for the responses!

    I did set up a linked server, gave the user db_owner in msdb (just for testing purposes) and used a 4 part name to execute the job (using both a job id and a job name) and I had the same issue. I also created a Stored Proc in server 2 that calls the job...when I call the SP locally, it executes the job, but when I call it from server 1, it gives me the same error. So I made the linked server user a sysadmin on server 2 and now it works....my question is, what permission does this user need to be able to run the job in server 2? I thought it needs execute on sp_start_job in msdb and that should be fine, and like I said, I even gave it db_owner in msdb and it still failed, so what else should I give this user?

    Thanks again!

  • OK, 2 minutes after I posted, I checked BOL for sp_start_job and it said the following:

    "By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

    SQLAgentUserRole

    SQLAgentReaderRole

    SQLAgentOperatorRole

    "

    Thanks.

  • Hi Keith, which book/site/etc. do you recommend for a DBA to start learning PowerShell? Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply