Error 14274 Cannot Add Update Delete Job Msx Server
Contents |
SERVER - Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated
Sp_add_jobserver
from an MSX server. The job was not saved. sp_delete_job December 20, 2006Pinal DaveSQL, SQL Server, SQL Tips and Tricks97 commentsTo fix the error sp_dropserver which occurs after the Windows server name been changed, when trying to update or delete the jobs previously created in a SQL Server 2000 instance, or attaching msdb database.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.Reason: 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.Fix/Workaround/Solution:In order to solve the problem you should perform the following steps: From the Query Analyzer run following steps in order: SELECT @@servernameand verify if it shows the correct SQL server name.a) If not, run: sp_dropserver <'name_returned'>and then: sp_addserver <'correct_servername'>, 'local'to change the SQL server name.Please restart SQL server service to let the new configuration takes effect.b) If yes,Please check the originating_server column in msdb..sysjobs by running: 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/ All Forums SQL Server 2008 Forums Transact-SQL (2008) Cannot add, update, or delete a job (or its steps Reply to Topic Printer Friendly Author Topic rosetulip Starting Member 15 Posts Posted-06/28/2013: 23:58:43 I have a trouble to merge two jobs into a master script file by sharing the http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186495 same globalization declaration. If I run separately, it works fine.The two jobs are testdbname jobtestnewdb jobIt comes up with the following errorsI got many these messageMsg 14274, Level 16, State 1, Procedure sp_add_job, Line 132Cannot 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 132Cannot 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 scriptUSE [msdb]GODECLARE @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=1IF 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 TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Ob
add, update, or delete a job (or its steps or schedules) that originated from an MSX server. http://www.sqlbackuprestore.com/error14274.htm The job was not saved. Reason: SQL Server 2000 supports multi-instances, the originating_server field contains the instance name in error 14274 the format server\instance. Even for the default instance of the server, the actual server name is used instead of (local). After the Windows server is renamed, these jobs still reference the original server name and may not be updated or error 14274 cannot deleted by the process from the new server name. It’s a known problem with SQL2000 SP3. Fix --Verify Server Name SELECT @@servername If SQL name is not correct run. -- if no sp_dropserver <'nameReturned'> -- and then sp_addserver <'correctServername'> , 'local' Print 'Your server name has now been changed, restart SQL Service so changes will take effect.' If SQL name is correct run. --check originating_server column SELECT * FROM msdb..sysjobs --if not USE msdb GO DECLARE @server sysname SET @server = CAST(SERVERPROPERTY('ServerName')AS sysname) UPDATE sysjobs SET originating_server = @server WHERE originating_server = '
transaction log Backing up the tail Inside the transaction log So, what's in a backup file? Test: A full backup does not contain deleted data Verifying backup files Verifying backup files on a budget Cumulative backups Recovering individual tables Backup and restore history details Backup reads and writes Speeding up backups Backup speed details Speeding up restores Restore state affects speed too Backup and restore rights Log shipping Log shipping in SQL Server 2000 Setting up log shipping using Enterprise Manager Checking the set up Failover Log shipping in SQL Server 2005 Setting up log shipping using Management Studio Checking the set up Log shipping status report Failover Log shipping in SQL Backup Using the CopyTool utility Failover 3rd party backup applications VDI VDI versions VDI errors SQL Backup - beyond compression Restoring a chain of transaction log backups Restoring to the latest possible state Backing up multiple databases Backup retention Making a copy of the backup file Backup file naming conventions Restoring the latest backup set Network resilience Encryption Integrated database verification Database file relocation Improved backup retention RESTORE HELP High-availability group support Common SQL Backup issues Installation checklist Setting up rights Configuring service rights Backup data Hanging issues Common backup and restore errors Error 3201 - when performing a backup to a network share Full database backup file is larger than database size Error 3205 - Too many backup devices specified for backup or restore Error 4305 - an earlier transaction log backup is required Bringing a database that is in recovery or read-only mode online Using bulk-logged recovery model but transaction log backup is still large Error 14274 - unable to delete SQL Server Agent job Error messages when restoring from different versions of SQL Server. Pending vdi error codes Restore speed details Help, my transaction log file is huge! Mirror or log ship What When you attempt to edit or delete a SQL Server Agent job, you receive the following error: Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. and you do not have MSX