Updating the MSDB..sysjobs Table

Question

Updating the MSDB..sysjobs Table

HookSQLDBA7

SSChampion

Points: 10709
More actions
November 23, 2004 at 1:12 pm

#84270

I received the following error:
"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."
I resolved this error by following this workaround from KB Article 281642:
1) Rename the server back to the original name.
2) Script out all of the jobs and then delete them.
3) Rename the server to the new name.
4) Add back the jobs by running the script generated from step 2.
My question is could I have resolved this error by updating the originating_server name in the msdb..sysjobs table? Is it a 'good and safe' practice to update the system tables in the msdb?
Thanks in advance, Kevin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mybid2003 SSCertifiable Points: 6620 More actions · Answer 1

If I am not worng, I did modify like this to resolve one of my problem, when my primary server crashed and we moved to contingency server.

But could not remember!!!

.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Is it a 'good and safe' practice to update the system tables in the msdb?

No! I would stay away from it unless being told so and guided by Microsoft PSS.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

"My question is could I have resolved this error by updating the originating_server name in the msdb..sysjobs table?"

Yes, It resolves the problem. You need run sp_configure 'allow update', 1 before updating system tables and disable the update by sp_configure 'allow update', 0

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Okay, while this is technically correct, one shouldn't make this frequently. At least, one should make sure to have a current backup.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mybid2003 SSCertifiable Points: 6620 More actions · Answer 5

I completely agree with Frank on this. We should not make frequent updates.

.

mybid2003 SSCertifiable Points: 6620 More actions · Answer 6

I completely agree with Frank on this. We should not make frequent updates to any system tables. This is what been recommended by Microsoft. The reason being Microsoft can add new system tables or add relationships/referrential integrity between the system tables in any service pack.

Correct me if I am wrong.

.

philcart SSC-Forever Points: 47796 More actions · Answer 7

Just to echo the general consensus, you should not make it a practice of updating any of the system tables.

You could have achieved your fix by scripting out the jobs, making appropriate alterations to the resulting script and running it.

--------------------
Colt 45 - the original point and click interface