SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Cannot add, update, or delete a job (or its steps
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rosetulip
Starting Member

9 Posts

Posted - 06/28/2013 :  23:58:43  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/29/2013 :  10:26:50  Show Profile  Reply with Quote
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/
Go to Top of Page

prett
Posting Yak Master

USA
200 Posts

Posted - 06/30/2013 :  23:38:28  Show Profile  Visit prett's Homepage  Reply with Quote
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.
Go to Top of Page

rosetulip
Starting Member

9 Posts

Posted - 07/01/2013 :  21:25:28  Show Profile  Reply with Quote
I followed the links to update server name, I still have the same error.
Go to Top of Page

gmartinnc
Starting Member

USA
2 Posts

Posted - 11/12/2013 :  16:53:35  Show Profile  Reply with Quote
@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
Go to Top of Page

gmartinnc
Starting Member

USA
2 Posts

Posted - 11/12/2013 :  17:00:37  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
368 Posts

Posted - 11/12/2013 :  17:30:48  Show Profile  Reply with Quote
Whenever I had that problem on SQL 2000, I just UPDATEd the system table column to change it to the correct server name .

Edited by - ScottPletcher on 11/12/2013 17:31:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000