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.

« SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SQL SERVER – Shrinking Truncate Log File – Log Full »

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.

December 20, 2006 by pinaldave

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

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 92 Comments

92 Responses

on March 9, 2007 at 12:58 pm | Reply James

Dave,

Excellent write-up!

Got me out of a pretty good jam!

Thanks!!
on April 26, 2007 at 11:52 pm | Reply Malik

Thanks Dear,

It solved my problem. Wish you a very happy life.

Regards,
Malik
on May 7, 2007 at 7:48 am | Reply Gabriel

Beautiful doc, excelent tip!

tks a lot!
Gabriel
on May 11, 2007 at 12:51 pm | Reply Max

I have to agree… Excellent, short and precise documentation.

Thanks,
Max
on May 13, 2007 at 8:34 am | Reply Marcus - Orange CA

This worked well… I was relieved to a final clean-up of the olf jobs I couldn’t remove.
Spent and hour searching for alternatives on microsoft.
found this in 2 minutes. Ran it in 1 minute.
10,000 thank you’s!
on May 31, 2007 at 11:02 pm | Reply udo

Thank you very much! Excellent document Have a long live!! :-))
on June 14, 2007 at 10:56 am | Reply Jose Luiz (Sao Paulo - Brazil)

Great tip, solve my problem
Thank you very much
on June 23, 2007 at 4:41 am | Reply Tudor

Dave,

Excellent and crystal article. THis solve my problem.
on July 15, 2007 at 5:30 pm | Reply Marcelo Fernandes (Brasilia- Brazil)

Thank you very much ! I solve my problem.
I hope you you have a very nice life
and God blesses you.
on July 30, 2007 at 4:46 am | Reply Càssio Dias - ( Brasíla - Brazil )

Thanks !!!!!!!!!!

It solved my problem!!!!!!!!!!

This document is very simple and very efficient.
on August 2, 2007 at 1:29 am | Reply Kevin Kemp- (England)

Thank you,

So simple and yet so effective. Having read what Microsoft suggested and then doing this. I am speachless.

Thanks again you saved my day
on August 16, 2007 at 5:26 pm | Reply Sathya

Great trick Boss! It helped fix our problem!
on September 6, 2007 at 6:51 am | Reply luca zaffarano

Thank you for you solution, good job!
Luca
on September 17, 2007 at 11:22 am | Reply Jim

Many thanks. You answered what many could not.
on September 21, 2007 at 9:28 am | Reply Amin Jamalomidy

It was wonderful. To be honest I am Oracle DBA but our company has few sql servers for support and I solved the problem with your tip. I wish a great life for you.
on December 13, 2007 at 3:10 pm | Reply Praseedha S

This blog was a big saviour!
Thanks a lot!
Praseedha S-India
on January 4, 2008 at 9:21 pm | Reply siva

Good artilce, it worked for me!

Thanks
Siva
on February 1, 2008 at 12:24 am | Reply Josh

Well done. Much better then MS fix!

Cheers,

Josh
on February 9, 2008 at 11:11 pm | Reply AMAVELI

congratulations you are my best friend…
on February 15, 2008 at 2:26 am | Reply In-Chang

Good solution – precise and direct. Thank you very much.

Microsoft should pay you for your support.
on March 12, 2008 at 12:36 pm | Reply Ashraf

That was straight forward and precise and yes it did solve the problem and saves my day man….thanks a lot to all.
on March 15, 2008 at 12:47 am | Reply Jon

Thanks, I had renamed a SQL server and this helped me fix sql agent jobs afterwards.
on April 5, 2008 at 4:08 am | Reply Dufftime

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.
on April 10, 2008 at 9:07 pm | Reply Dan

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
on April 12, 2008 at 10:27 am | Reply Don Adams

What a time saver!
on May 15, 2008 at 3:30 am | Reply marilyn stokes

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.
on May 28, 2008 at 9:38 am | Reply Manuel

Thanks you very much !!!
on May 29, 2008 at 5:37 pm | Reply shailesh

Excellent Article! Keep it up.

Thanks
on June 10, 2008 at 2:19 pm | Reply Nadear

Thanks a lot !
I’m Thai people..
This good blog..
I will come agin..
Bye Bye ^_____^
on June 26, 2008 at 1:21 pm | Reply Nur

Thank you very much. have a great day! :)
on June 30, 2008 at 10:43 pm | Reply Ramesh S

Hi Thanks a lot…..
on July 24, 2008 at 7:43 pm | Reply Murali

Excellent write-up!!!!
on July 29, 2008 at 5:37 pm | Reply kc

It worked. Thanks.
on August 19, 2008 at 7:51 pm | Reply mane

thank you very much. helped me to solve my problem.
on October 7, 2008 at 3:49 am | Reply Juan C

You are the Best!!!!
on October 23, 2008 at 7:48 am | Reply Nino

really helpfull!
on December 16, 2008 at 6:29 pm | Reply sveroa

Nice post…got me thinking and I could clean up some jobs that was copied from one server to another.

Thanks :)
on December 19, 2008 at 10:47 am | Reply George G

Thanks heaps mate – help me overcome my problem

many blessing to you and your family
on January 15, 2009 at 12:43 pm | Reply Roel

Dave, Thank you for this solution.
on January 29, 2009 at 1:14 am | Reply Toy

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.
on March 26, 2009 at 11:26 pm | Reply jchen

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
on April 17, 2009 at 6:15 am | Reply lalo

beautiful…… thanks a lot.. simply brilliant
on April 24, 2009 at 10:35 pm | Reply Craig Greenwood

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.
on April 30, 2009 at 12:36 pm | Reply san

Thanks, it solved the problem as soon as the steps were performed.
on May 1, 2009 at 5:32 pm | Reply ashishtilak

Thanks a lot… It saved me!!!
on May 19, 2009 at 8:47 pm | Reply Bill T

This was very easy and concise. Great write up. It helped out a lot
on July 7, 2009 at 6:30 pm | Reply gavin

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.
on July 8, 2009 at 6:09 am | Reply Genghis Khan

Gorgeous solution.
Thousand kisses from me.
on August 5, 2009 at 4:32 am | Reply Edivaldo Costa

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!!!
on August 11, 2009 at 2:39 am | Reply Kali

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
on August 13, 2009 at 11:23 pm | Reply yoodles

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.
on September 29, 2009 at 1:53 am | Reply ceoj

Dude! You are a GENIUS!
on October 15, 2009 at 7:56 pm | Reply aymeric

Thanks, solved my problem
on October 20, 2009 at 5:27 pm | Reply P.L.

THANKS a million!
on October 23, 2009 at 3:04 pm | Reply Jane

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
on October 30, 2009 at 3:57 pm | Reply Paul Hills

Absolutely spot on – many thanks!!
on November 6, 2009 at 3:47 am | Reply michael

Thank you for this, saved me!
on November 25, 2009 at 10:56 pm | Reply Geoff

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
on January 28, 2010 at 11:40 pm | Reply Alex

Thanks, worked like a charm!
on February 3, 2010 at 11:49 pm | Reply zaty

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
on February 23, 2010 at 4:49 am | Reply David Chief

Thanks so much!
Worked perfectly
on February 26, 2010 at 1:25 am | Reply Tina

Thanks so much. Fixed my problem too.
on April 20, 2010 at 1:20 pm | Reply Jasmin Hadzigrahic

Really, really, great work! You helped me sooo much!

Thanks a lot!
JSK
on August 13, 2010 at 11:42 am | Reply kunal

Great…Thanks for your help
on September 2, 2010 at 7:46 pm | Reply Bryan

Thanks a load… You’re GREAT
on September 23, 2010 at 7:49 pm | Reply Lori

Just what I needed! You’re the best!
on September 28, 2010 at 6:03 am | Reply ott

I just ran it like this
DELETE
FROM msdb..sysjobs

and it took them all off for me, if you don’t want to remove them all then put the name of the backup like

DELETE backup1
FROM msdb..sysjobs
on November 3, 2010 at 1:55 am | Reply Richard Tracy

This worked really well for me

Thank you very much
on January 14, 2011 at 9:49 pm | Reply Michele Wu

This works perfectly. Thanks a lot for always sharing useful stuff!!
on February 1, 2011 at 12:23 pm | Reply Kaushal

Thanks Pinal. Worked well and saved a lot of time.
on February 25, 2011 at 1:28 pm | Reply RAJU BANDARU

TY Pinal.
on March 16, 2011 at 7:07 pm | Reply Catherine Sulit

Thank you. This is just one of your posts that really helped me.
We restored an image of our server so all jobs goofed up because of server renamed. And your solution just worked like a charm.

Best wishes to you.
on March 21, 2011 at 1:17 pm | Reply Vishal

You are superb.. Pinal

Thanks a lot..
on March 25, 2011 at 7:39 am | Reply RD

Thanx Matey!
on June 9, 2011 at 11:13 pm | Reply BY

This is still very effective after all these years. Thank you for writing it.
on July 24, 2011 at 6:37 am | Reply JB

Thanks!!
on July 29, 2011 at 9:36 pm | Reply Ed Kamarauskas

This worked perfectly. Many thanks! Both my production server and standby server names have been set properly. Thanks Again, Ed K
on September 21, 2011 at 7:21 pm | Reply Saravana Kumar

Thanks
on September 27, 2011 at 4:15 am | Reply k

Pinal, by any chance do you happen to know Sumit Dave?
He looks remarkabally like you.. just like your long lost brother. Have you been to kumbh?
- KK
on November 15, 2011 at 10:04 pm | Reply dan

Fabulous… worked just as expected… 5 stars for this effort
on November 17, 2011 at 6:20 pm | Reply Vramby

Worked like a charm for meon a clone of an existing server with another name.
on May 23, 2012 at 1:00 am | Reply Joao

Thanks a lot
on August 7, 2012 at 3:14 pm | Reply Rajesh

Great Job!! Problem solved. Thanks a lot.
on September 5, 2012 at 3:31 pm | Reply Ning Xu

I’ve bumped this issue just today while maintaining an acient sql2000 instance. Thanks again!
on September 5, 2012 at 3:32 pm | Reply Ning Xu

Bumped and resolved the same issue today while maintaining an ancient SQL2000 server. Thanks again!
on September 13, 2012 at 12:09 am | Reply Clayton Santos

Hi, Thank you for help.

Excelent.

Santos, Clayton A
on October 16, 2012 at 11:18 am | Reply software company in bhubaneswar

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.
on November 6, 2012 at 2:26 pm | Reply tuandat

Thank so much! This’s very helpful :)
on November 9, 2012 at 4:37 pm | Reply Shashikant

Hi Pinal,
It worked awesome for me.
Thanks a lot for this type helpful documents.
on December 9, 2012 at 3:52 pm | Reply Ben Joyce

Perfect. Thank you.
on December 22, 2012 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

[...] SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) th… This is one error every single developer who have ever upgraded SQL Server to the latest version would have faced it – I was glad to have the solution of this out early in my career as I had upgraded my system around the same time from SQL Server 2000 to SQL Server 2005. [...]
on January 11, 2013 at 11:42 am | Reply SQL SERVER – Find Stored Procedure and View Related to Table in Database – Search in All Stored Procedure « inforakesha

[...] « SQL SERVER – Cursor to Kill All Process in Database SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) th… » [...]