Cannot add, update, or delete a job (or its steps

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cannot add, update, or delete a job (or its steps

Author

Topic

rosetulip
Starting Member

15 Posts

Posted - 2013-06-28 : 23:58:43

I have a trouble to merge two jobs into a master script file by sharing the same globalization declaration. If I run separately, it works fine.

The two jobs are
testdbname job
testnewdb job

It comes up with the following errors

I got many these message

Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132

Here is two job script

USE [msdb]
GO
DECLARE @LLC_DATABASE_NAME NVARCHAR(100) = N'LLC'
DECLARE @LLC_LOG_IN_NAME NVARCHAR(100) = N'LLC'
DECLARE @Roll_Back_Server NVARCHAR(100) = N'(local)'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'testdbname')
EXEC sp_delete_job @job_name = N' testdbname' , @delete_unused_schedule=1

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'testnewdb')
EXEC sp_delete_job @job_name = N'testnewdb' , @delete_unused_schedule=1

/****** Object: Job [testdbname] Script Date: 06/25/2013 16:41:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 06/25/2013 16:41:15 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

-- testdbname job

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'testdbname',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
--@owner_login_name=N'LLC',
@owner_login_name=@LLC_LOG_IN_NAME,
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [db_name] Script Date: 06/25/2013 16:41:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'db_name',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N' select * from customer ',
--@database_name=N'LLC',
@database_name=@LLC_DATABASE_NAME,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,
--@server_name = N'(local)'
@server_name = @Roll_Back_Server
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

--GO

--- testnewdb job

--DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'testnewdb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'LLC', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [newdb] Script Date: 06/26/2013 16:20:56 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'newdb',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N' select * from supplier',
@database_name=N'LLC',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback1:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave1:

GO

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 10:26:50

This article may provide some helpful hints for you:
http://blog.sqlauthority.com/2006/12/20/sql-server-fix-error-14274-cannot-add-update-or-delete-a-job-or-its-steps-or-schedules-that-originated-from-an-msx-server-the-job-was-not-saved/

prett
Posting Yak Master

212 Posts

Posted - 2013-06-30 : 23:38:28

Cause of the error : SQL Server 2000 supports multi-instances, the originating_server field contains the instance name in the format ‘server\instance’. Even for the default instance of the server, the actual server name is used instead of ‘(local)’. Therefore, after the Windows server is renamed, these jobs still reference the original server name and may not be updated or deleted by the process from the new server name. It’s a known problem with SQL2000 SP3.

Please check this post for more information.

rosetulip
Starting Member

15 Posts

Posted - 2013-07-01 : 21:25:28

I followed the links to update server name, I still have the same error.

gmartinnc
Starting Member

2 Posts

Posted - 2013-11-12 : 16:53:35

@rosetulip - Add a GO between the calls to 'sp_delete_job' to separate these from the remaining code blocks. I ran into this error myself, and it seems up through 2008R2 at least, you can't run these within the same batch. So revised:

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'testdbname')
EXEC sp_delete_job @job_name = N' testdbname' , @delete_unused_schedule=1
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'testnewdb')
EXEC sp_delete_job @job_name = N'testnewdb' , @delete_unused_schedule=1
GO

DECLARE @LLC_DATABASE_NAME NVARCHAR(100) = N'LLC'
DECLARE @LLC_LOG_IN_NAME NVARCHAR(100) = N'LLC'
DECLARE @Roll_Back_Server NVARCHAR(100) = N'(local)'

...

Graeme

The Oracle

gmartinnc
Starting Member

2 Posts

Posted - 2013-11-12 : 17:00:37

I also see you are reusing @jobID OUTPUT. You need to NULL it before you EXEC sp_add_job the second time, according to this post:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b3c5fc56-e99c-42e8-9f67-9074bf0bf957/adding-a-job-to-sql-agent?forum=transactsql

SET @jobId = NULL;

Graeme

The Oracle

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-12 : 17:30:48

Whenever I had that problem on SQL 2000, I just UPDATEd the system table column to change it to the correct server name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources