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
I ran your script but it gave me another error.
here is your script:
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = ‘’
here is the error:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column ‘originating_server’,
table ‘msdb.dbo.sysjobs’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Can you help
Thank you so much,
chen
beautiful…… thanks a lot.. simply brilliant
As a way of improvement….
use msdb
UPDATE sysjobs SET originating_Server = ‘new_servername’
Does the same thing as:
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = ”
but it’s shorter. Less potential syntax issues as well.
Thanks, it solved the problem as soon as the steps were performed.
Thanks a lot… It saved me!!!
This was very easy and concise. Great write up. It helped out a lot
hi great write up,
what i did was a select:
SELECT *
FROM msdb..sysjobs
and then an update:
update msdb..sysjobs
set [originating_server] = ‘new_server_name’
where [originating_server] ‘new_server_name’
And then deleted it from the job schedule.
again good write up.
Gorgeous solution.
Thousand kisses from me.
Thanks a lot…
My case resolved too, after to apply last sugestion . This is error my jobs:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column ‘originating_server’,
table ‘msdb.dbo.sysjobs’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Solution:
use msdb
UPDATE sysjobs SET originating_Server = ‘new_servername’
Thanks again!!!
To people getting the error message:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column ‘originating_server’,
table ‘msdb.dbo.sysjobs’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Please do not alter the line:
SET @server = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)
You should leave ‘ServerName’ as it is. You should not replace it with your own server name.
Regards
Great work, this really helped me out. Short and precise, do not find that much with us geeks, always want too much info in there! (myself included) Thanks again.
Dude! You are a GENIUS!
Thanks, solved my problem
THANKS a million!
Have just realised this seems to be a bug introduced in SQL2000 SP3 and not fixed in SP4.
Just waiting for permission to restart SQL on the affected box!
many thanks
J
Absolutely spot on – many thanks!!
Thank you for this, saved me!
Hey this was great, I did a small modification which takes care of all jobs that where not on this server by doing the following:
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)
PRINT @server
SELECT *
FROM msdb..sysjobs WHERE originating_server @server
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server @server
the change is minor but it guarantees only changing those jobs that were not created on the new server
Thanks, worked like a charm!
Hi Dave,
I still having the following error when trying to delete a job even though the value of @@servername and originating_server is the same :
“Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server”
select @@servername
AMCDE\VAULINETIX
SELECT originating_server FROM msdb..sysjobs
AMCDE\VAULINETIX
appreciate yr help. thanks