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
104 Comments. Leave new
Worked like a charm for meon a clone of an existing server with another name.
Thanks a lot
Great Job!! Problem solved. Thanks a lot.
I’ve bumped this issue just today while maintaining an acient sql2000 instance. Thanks again!
Bumped and resolved the same issue today while maintaining an ancient SQL2000 server. Thanks again!
Hi, Thank you for help.
Excelent.
Santos, Clayton A
As a database working in SQL Server is tough and learning something new about some new errors will add an extra advantage to solve this error without much tiresome troubles.
Thank so much! This’s very helpful :)
Hi Pinal,
It worked awesome for me.
Thanks a lot for this type helpful documents.
Perfect. Thank you.
Hi, I need some hep regarding the SQL 2000.We have been using SQL2000 with CalSys (KROHNE Oil & Gas proprietry) whereby the SQL feeds certain fields in the autogenerated daily report. for some days, it is not updating one field out of 21. Tried to referesh the services, but no success, help is always apppreciated
Sir,
Its really great write – up,
helped me lot, as i started my career as SQL DBA
Thanks..
Seven years later, still works like a charm.
Ildefonso Pineda : desde caracas muchas gracias, mi problema ha sido solventado. thanks a lot, my problem has been solved. best regards !!!
Great write up. I had 1/2 my jobs with the old server name and 1/2 with the new server name. The ‘SELECT * FROM msdb..sysjobs’ helped the most to identify which ones where the problem. Since I only have a handful of jobs, I just went into the sysjobs table and manual fixed the entries. Thanks.
i cannot see the originating_server column there is originating_server_id column.
Bob – What is the actual problem?
Great post. Thanks for sharing
On Sql Server 2012 there is originating_server_id, not originating_server!
Sure. That’s a good information which would help others.
Hi Dave,
thanks a lot for your great post sharing solution :)
Thanks for the confirmation Max.
another reason for this error message: you alread set the output-parameter @job_id to something (e.g. NEWID()) when you execute the msdb.dbo.sp_add_job manually