Monday, June 6, 2011

TSQL : Updating jobs that originated from an MSX server

If you try to update a SQL Agent Job or Maintainence plan that was set up using an MSX server (Master/Target environment) you get this error.


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

If an MSX server has been decommissioned, renamed or is unrecoverable you have a genuine need to override this message. Providing you have permissions on the local server, you can update the entries in msdb directly.

This query updates all jobs, making their 'owner' (originating_server). the local server.

UPDATE msdb.dbo.sysjobs
SET originating_server = CONVERT(nvarchar, SERVERPROPERTY('servername'))
WHERE originating_server <> CONVERT(nvarchar, SERVERPROPERTY('servername'))

Once run, you can update the jobs once more.

ref : MSX Error

2 comments:

claudio said...

Thank you!! it really works!!!!!

alissa914 said...

Yes, I concur. We just recreated a server from backups and I was having issues updating jobs. It's good to know the solution is a simple one. :)