Start job on another server

Question

Start job on another server

shahgols

SSCrazy Eights

Points: 9874
More actions
March 10, 2014 at 5:37 pm

#304923

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.

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

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

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]

jeffgonnering SSChasing Mays Points: 659 More actions · Answer 2

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()

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

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

shahgols SSCrazy Eights Points: 9874 More actions · Answer 4

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!

shahgols SSCrazy Eights Points: 9874 More actions · Answer 5

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.

shahgols SSCrazy Eights Points: 9874 More actions · Answer 6

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