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
That was straight forward and precise and yes it did solve the problem and saves my day man….thanks a lot to all.
Thanks, I had renamed a SQL server and this helped me fix sql agent jobs afterwards.
Thanks for the nice tip. There’s been so many times where I’ve renamed a SQL server and forgotten about the jobs. My only known solution was to rename it back temporarily just to delete the jobs.
Thanks a lot for this! It was very helpful.
Just a note though, if you have MSDE, you might run into issues creating and using a variable. You can also use:
USE msdb
UPDATE sysjobs SET originating_server = @@servername
…IF you only have 1 instance and IF you correctly set the servername as the OP mentioned in part A.
Cheers,
Dan
What a time saver!
Thanks, I just migrated jobs from one server to another, and needed to quickly disable the jobs….and received an error. This has fixed my problem…thanks so much.
Thanks you very much !!!
Excellent Article! Keep it up.
Thanks
Thanks a lot !
I’m Thai people..
This good blog..
I will come agin..
Bye Bye ^_____^
Thank you very much. have a great day! :)
Hi Thanks a lot…..
Excellent write-up!!!!
It worked. Thanks.
thank you very much. helped me to solve my problem.
You are the Best!!!!
really helpfull!
Nice post…got me thinking and I could clean up some jobs that was copied from one server to another.
Thanks :)
Thanks heaps mate – help me overcome my problem
many blessing to you and your family
Dave, Thank you for this solution.
Thanks! I have implemented this fix successfully on SQL 2000 servers and now need to apply to a Sql 2005 server that was copied to another server. Unfortunately, the msdb.sysjobs table does not have an originating_server column in sql 2005. It is called originating_server_id. Is there a similiar fix for SQL 2005? Your assistance is greatly appreciated.