Cannot Edit or Delete Job

Question

Post reply

Cannot Edit or Delete Job

cdun2

SSCertifiable

Points: 7893
More actions
May 14, 2004 at 4:24 pm

#359646

Hello,
I have a problem with EM and the msdb. I get an error message 14274 when I try to edit or delete a job. The data in the msdb for the jobs was generated by a restored backup that came from another machine, and a different instance of SQL Server. The old default server instance was called 'SQL_Server' and the new default instance is called 'SDO-SQL'.
The error message states:
Cannot add, update, or delete a job (or its steps or schedules) that
originated from an MSX server.
I don't have a master scheduling server. I get the error
message when attempting to change a job. eg. enable/disable a job,
change the time, delete a job, add a job.
Server is running w2k Server SP4
I tired the following:
USE MSDB
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('SDO-SQL') AS sysname)
UPDATE sysjobs SET originating_server = @srv
and got the following message:
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.
I can see from the properties of any job that the source still points to SQL_Server.
What can I do with these jobs so that I can edit and delete them as they are on the new server?
Thank you for your help!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 1

You don't need to get fancy with the update. You are on the right track though.

UPDATE sysjobs

SET originating_server = 'SD0-SQL'

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thanks for your help. I ran the update, and it worked. I can see from the properties of any given Job that the source has been changed to SDO-SQL. However, the problem remains.

I tried to see if stopping and starting the Agent Service would accomplish anything, but no luck.

What else can I do?

CSDunn

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 3

That's odd. It's always worked for me. I'm assuming you're not getting the same exact error message now. Can you post the new error message?

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Are you just wanting to get rid of these or do you have copies of what they should be. If you have copies then try sp_delete_job. If that fails then maybe you didn't import all the pieces to the tables properly. I did this once and acctually forgot to pull in the jobs steps in the proper table and was disallowed to open it because of that.

Make sure you imported the details for those job to all the following tables before you can edit from their original counterpart.

sysjobs

sysjobservers

sysjobschedules

sysjobsteps

cdun2 SSCertifiable Points: 7893 More actions · Answer 5

Thanks to all for your help. I came accross the following MS Knowledge Base article; http://support.microsoft.com/default.aspx?scid=kb;en-us;314546&Product=sql2k#5 . This article gives detail on how to move databases between instances of SQL Server.

I also discovered that there is a DTS task for moving jobs to another server, and it is similar to the Transfer Logins task. Since none of the user databases on the new server were in production yet, I detatched the user databases, uninstalled SQL Server, reinstalled SQL Server (with SP3) and followed the steps outlined in the KB article to transfer these items over.

I don't recall seeing anything in the KB article that addressed the Transfer Job task in DTS, but I ran one, and it worked. I was able to easily transfer the jobs through DTS, and could work with the jobs once they were in the new instance.

Thanks again!

CSDunn