Error 14274 - Can't delete & then re-add job

Question

I'm trying to create a rather simple script for dealing with SQL Server Agent jobs. It performs 2 tasks:

1) If a given job exists, delete it 2) Create the job

(Due to business requirements I can't modify an existing job, the script must delete & re-create it.)

Running the script the first time works fine (creates the job). Running any times after that produces error 14274 "Cannot add, update, or delete a job that originated from an MSX server."

I've done lots of searching on this, and most solutions center around the server name being changed. My server name has not changed, nor has it ever.

Here's what I have:

USE [msdb];

SET NOCOUNT ON;

DECLARE @JobName NVARCHAR(128);
DECLARE @ReturnCode INT;
declare @errCode INT;

SET @JobName = 'AJob';

BEGIN TRANSACTION;

DECLARE @jobId uniqueidentifier;

SET @jobId = (SELECT job_id from msdb.dbo.sysjobs where name = @JobName);

IF(@jobId IS NOT NULL) -- delete if it already exists
begin
   EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_id=@jobId
   IF(@@ERROR <> 0 OR @ReturnCode <> 0)
    begin
        set @errCode = @@ERROR;
        GOTO QuitWithRollback;
    end
    print 'deleted job';   
end


-- create the job
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
        @enabled=1,
        @notify_level_eventlog=0, -- on failure
        @notify_level_email=0,
        @notify_level_netsend=0, -- never
        @notify_level_page=0,
        @delete_level=0,
        @description=NULL,
        @owner_login_name=N'sa',
        @notify_email_operator_name=NULL,
      @job_id = @jobId OUTPUT
IF(@@ERROR <> 0 OR @ReturnCode <> 0)
begin
    set @errCode = @@ERROR;
   GOTO QuitWithRollback;
end
print 'added job';

-- Server
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
   @job_id = @jobId
IF(@@ERROR <> 0 OR @ReturnCode <> 0)
   GOTO QuitWithRollback;

COMMIT TRANSACTION;
RETURN;

QuitWithRollback:
   IF(@@TRANCOUNT > 0)
      ROLLBACK TRANSACTION;
   print 'Err: ' + CAST(@errCode AS varchar(10)) + ' ret: ' + cast(@ReturnCode as varchar(10));

I'm running it on SQL 2008 SP1. Any help would be very much appreciated!

Answer 1

Actually I figured this one out after some fooling around.

It seems that it's necessary to set the @JobId variable to NULL between the statements that delete and create the job. Once you do that, the error goes away and it works.

I hope this helps someone!