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.

To fix the error 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 @@servername

and 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:
SELECT *
FROM msdb..sysjobs

and verify if all jobs have the correct server name for originating_server.

If not, update this value with the correct server name by running following script
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY('ServerName')AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = '<wrong_servername>'

Reference: Pinal Dave (http://www.SQLAuthority.com),
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642