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

About these ads

96 thoughts on “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.

  1. 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!

  2. Thank you very much ! I solve my problem.
    I hope you you have a very nice life
    and God blesses you.

  3. Thanks !!!!!!!!!!

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

    This document is very simple and very efficient.

  4. 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

  5. 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.

  6. 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.

  7. 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

  8. 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.

  9. 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.

  10. 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

  11. 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.

  12. 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.

  13. 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!!!

  14. 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

  15. 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.

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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.

  21. This worked perfectly. Many thanks! Both my production server and standby server names have been set properly. Thanks Again, Ed K

  22. 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

  23. 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.

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  25. Pingback: SQL SERVER – Find Stored Procedure and View Related to Table in Database – Search in All Stored Procedure « inforakesha

  26. 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

  27. Ildefonso Pineda : desde caracas muchas gracias, mi problema ha sido solventado. thanks a lot, my problem has been solved. best regards !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s