SSMS – Scripting – Job – Error – “msdb.dbo.sp_add_jobserver” – “is already targeted at server”

 

Background

Using SSMS, Scripted SQL Server Job so that I can apply it against other SQL Servers, but ran into error.

Scripting

Script SQL Server Agent Job

scriptJob_20180612_0128PM.PNG

 

Error

Error Message


Msg 14269, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 101 [Batch Start Line 8]
Job 'SharePublicly' is already targeted at server 'LAB'.

Troubleshooting

When a job is scripted, the code generated includes a portion that invokes sp_add_jobserver.

The sp_add_jobserver procedure binds the job to the targeted server.


EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
         @job_id = @jobId
       , @server_name = N'(local)'

IF (@@ERROR  0 OR @ReturnCode  0)
      GOTO QuitWithRollback

Remediation

Outline

  1. Check msdb system tables
    • dbo.sysjobservers
    • dbo.systargetservers
  2. If matching records
    • Here are the tables that needs to be considered
      • dbo.sysjobservers.jobName ( @jobID )
      • dbo.systargetservers.serverName ( @servername)
    • Conditions satisfied
      • Local variable @skipWhenJobExist is enabled ( set to 1 )
  3. Action
    • Skip invoking msdb.dbo.sp_add_jobserver

Code Snippet


DECLARE @jobId   BINARY(16)
DECLARE @jobName sysname
DECLARE @serverName sysname
DECLARE @serverNameLocal sysname
declare @commit bit
declare @skipWhenJobExist bit

set @jobName = 'SharePublicly';
set @servername = N'(local)'
set @serverNameLocal = N'(local)'

--set @commit = 0
set @commit = 1

set @skipWhenJobExist = 1
--set @skipWhenJobExist = 0

/*
	Check if Job is already attached to Target Server
*/
if not exists
(

	select
			  [src] = 'dbo.sysjobservers'
			, tblSJS.* 

	from   dbo.sysjobservers tblSJS

	left outer join dbo.systargetservers tblSTS
			on tblSJS.server_id = tblSTS.server_id

	where  tblSJS.job_id = @jobId

	and  

		(

			(
					 ( tblSJS.server_id = 0)
				 and ( @servername = @serverNameLocal )

			)

			or
			(
					 ( tblSJS.server_id = tblSTS.server_id)
				 and ( tblSTS.server_name = @serverName )
			)

		)
	and (@skipWhenJobExist = 1)
)
begin

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
			  @job_id = @jobId
			, @server_name = @serverName

	IF (@@ERROR  0 OR @ReturnCode  0)
	begin
		GOTO QuitWithRollback
	end

end

Source Control

GitHub

DanielAdeniji/SQLServerScriptingAgentErrorJobAlreadyTargeted
Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s