September 15, 2006 at 4:03 pm
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.
September 16, 2006 at 1:36 pm
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
September 18, 2006 at 2:51 pm
That's great, thanks so much for your post!
September 19, 2006 at 2:44 pm
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
August 19, 2008 at 8:05 pm
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.
October 27, 2009 at 3:33 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply