February 24, 2005 at 9:22 am
I have encountered an installation of SQL Server which has had the name of the server changed. SQL Server stores the name of the pc its installed in sysservers, I have changed the system tables to match the new name of the server. Does anybody know of any repurcussions from this, it seems to have fixed an issue we had where an app was auditing using the internal name of the server (@@servername).
Phil Nicholas
February 25, 2005 at 1:29 am
What is in syservers ?- usually it is best to do an sp_dropserver and an sp_addserver (with the local option).
Hope this helps.
February 25, 2005 at 2:38 am
Thanks, didnt know there was a way of changing the local name of the server via sps.
Phil Nicholas
February 25, 2005 at 7:09 am
If you have jobs you also need to change the name of the originating server or SQL won't let you edit the jobs - it thinks they were from another server. This is the script I use when renaming a server:
-- Run on SQL 2000 after server name change.
-- To correct the sysservers system table. Note the following excerpt from online help about remote users and logins:
-- If the computer has any remote logins, for example, if it is a
-- replication Publisher or Distributor, sp_dropserver may generate
-- an error similar to this:
-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
-- There are still remote logins for the server 'SERVER1'.
-- To resolve the error, you may need to drop remote logins for this server.
-- If replication is installed, disable replication on the server before
-- running the sp_dropserver stored procedure.
-- Side note:
--
--SQL Books Online is slightly incorrect with regard to sp_addserver when renaming a server. The correct syntax is
-- exec sp_addserver '<NEWSERVERNAME>', 'local'
--
-- If you do it the way it is specified in books online, the server is added with a new server id which is an identity field.
-- If you try to replicate from (and possibly to) this server, replication setup will fail because it is looking for a server
-- with an ID of 0 which won't be there. For some reason it uses this to determine something about the local computer
-- and bombs.
exec sp_dropserver <old servername>
go
exec sp_addserver <new servername>', 'local'
go
-- To correct orginating server on JOBS: If this isn't done, the jobs on
-- the server can't be edited, SQL thinks they came from another server
update msdb.dbo.sysjobs
set originating_server = '<new servername>'
where originating_server = '<old servername>'
go
February 25, 2005 at 8:36 am
The more I use this site the better it gets! I'm more of a developer than dba, actually more of a hacker by the sound of it! Thanks for your help. Luckily this time it was a QA laptop rather than a live server.
Phil Nicholas
February 25, 2005 at 5:05 pm
A stop/restart of SQL Server may be required too. If I remember correctly @@servername will still reflect the old name until this occurs even after the correct SP sequence to rename things correctly in sysservers.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 28, 2005 at 3:54 am
Thanks, this is definately the case, once restarted all was well.
Phil
Phil Nicholas
March 1, 2005 at 6:38 am
Thanks I had forgotten the restart instructions.
October 26, 2006 at 4:43 pm
The restarts weren't required just to change the job names back... and since there were all broken, I just changed them back and deleted them (after scripting them, just in case).
My environment is mirrored with third party software... and all the jobs say they are on node 3, when in reality they are now, failed over to node 4...
Not sure of the implications of the servername being wrong...
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply