Error 14274: Cannot Add, Update, Or Delete Job....

  • JuanBob

    SSCertifiable

    Points: 5054

    I am receiving this error on some of my jobs.  I can verify that the SQL Server was renamed from SERVERX to SERVER1.  I have read the MS article below, but I would like to not have to go through the rename if at all possible.  I found the steps below on another forum and wanted to verify whether this will work.  The server is Win 2003 Server, SQL 2000.

    Thanks!

    http://support.microsoft.com/default.aspx?scid=kb;en-us;281642

    1. Script out all of the jobs.

    2. Update the job source by:

    USE MSDB

    UPDATE sysjobs

    SET originating_server = 'New_Server_Name'

    3. Rename the jobs to be deleted.

    4. Delete the jobs.

    5. Add back the jobs by running the script generated from step 1.

  • EugeneZ-162636

    Default port

    Points: 1448

    USE MSDB

    UPDATE sysjobs

    SET originating_server = 'New_Server_Name'

     

    will do it, no need to delete jobs (unless you'd like)

    Just review code - DTS packs connections, etc

  • JuanBob

    SSCertifiable

    Points: 5054

    That's great, thanks so much for your post!

  • cross DBA

    Old Hand

    Points: 309

    Had similar problem when our system administrator renamed the production db server (win 2003) without telling me (the DBA).

    Check out BOL:

    sp_dropserver

    sp_addserver

    Although SqlServer (2000) continues running quite happily - SqlServer Agent got very confused - it carried on running all the scheduled backups etc. but I could not edit any of them (or stop, start, re-schedule them) when logged in as BUILTIN/Adinistrator.

    Changing the server name at NT admin level does not update the server names listed in the MSBD system database. Which explains why SqlServer Agent wasn't aware of the new server name.

    I hacked around in the MSDB system tables (which exists solely for the benefit of SqlServer Agent) and fixed the problem manually by changing column data (server_name) in one of the columns.

    THIS IS NOT RECOMENDED !!! but, I got away with it - and learned a lot more about SqlServer.

    THIS IS THE RECOMMENDED METHOD....

    I later found this in BOL (Sql 2000):

    When you change the name of the computer that is running Microsoft® SQL Server™ 2000, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name.

    You can connect to SQL Server using the new computer name after you have restarted the server. However, to correct the sysservers system table, you should manually run these procedures:

    sp_dropserver

    go

    sp_addserver

    go

  • Ren Hu

    Grasshopper

    Points: 15

    I had a case where the all datafiles were from a non-clustered server migrated to a clustered environment.

    We did all the sp_dropserver and sp_addserver stuff, we thought all was tuvsy turvy because all jobs, etc are working. But when it came the time to modify the jobs we got the error.

    After some analysis, we decided that the best way (and only way) to fix the error without affecting other subsystems using the servername is to directly modify the sysjobs table.

    Sometimes, doing it the 'un-official' way is the only way. And it pays to know the system tables.

    Cheers.

  • Régis Sajo Diniz

    Valued Member

    Points: 67

    In an environment where there are several instances in the same cluster, it is virtually impossible to rename the server. The update on sysjobs is the most suitable, but should be done by an experienced DBA. If you look at the procedure sp_delete_job doing the delete, we have reference to the field to be changed.

    begin tran

    declare

    @originating_server NVARCHAR(30)

    select @originating_server = lower(convert( NVARCHAR(30),SERVERPROPERTY('ServerName')))

    update

    msdb.dbo.sysjobs

    set

    originating_server = @originating_server

    where

    originating_server <> @originating_server

    commit tran

    select originating_server FROM msdb.dbo.sysjobs

    Régis Sajo Diniz - Brazil

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

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