SQL Server Error 14274 – Cannot add, update or delete a job (or its steps or schedules) that originated from an MSX server

Error 14274 occurs when you try to update a sql agent job after renaming server name.
If sql server is running on the server then after renaming windows server, you need to update sql server name.

Otherwise when you will try to add, update or delete a job on sql server, it will throw below error.

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

Because MSDB stores server name in originating_server field under sysjobs table. So now whenever you will try to access jobs information it will refer to old server.

SQL server 2000 supports multiple instances so for default instance also it store server name instead of local.

Fix-Workaround-Solution

To fix this problem, one option to drop old server name using system stored procedure in sql server and then add new server name and restart the services.
a. Run the below query

SELECT @@servername

b. Run system stored procedure to drop server

sp_dropserver ‘old server name’

c. Run system stored procedure to drop server

sp_addserver ‘new server name’

d. Restart sql server services to takes change

e. Again perform the step 1 to verify the updated server name.
Now you can run query against sysjobs table to see the changes in originating_server field.
2. To resolve this problem by using manual method (script out)

1. Revert the server back to old name.
2. Generate script for all sql jobs and delete them.
3. Rename the server to the new name.
4. Execute the script generated by step 2.

If all above solution doesn’t work for you then run update command to server name in sysjobs table.

UPDATE sysjobs
SET originating_server = ‘New Server Name’
Where originating_server = ‘old Server name’

 

Leave a Reply

  

  

  


+ 2 = 8