How to update sys.servers in sql server

You can update sys.servers table using sp_dropserver and sp_addserver for stand alone instance. For adding new server name to local instance of the server basically these issues occurs while renaming of windows server. If you are renaming server, it leaves some entries in sys.servers that give problem later on.

So after renaming windows server, you should use sp_dropserver statement to drop old server name from sys.servers table.

use master
sp_dropserver ‘DRLOYALTY’

Execute sp_addserver to add new server name in the list(sys.servers)
sp_addserver ‘DRBIZTALKPROD2’, local;

restart sql server to reflect the changes

Now you can verify the server name using @@servername statement.

select @@servername

If we will try to update sys.servers directly using DML statement,it will throw error
Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

refer the Microsoft arcticle on this :

Leave a Reply




+ 6 = 11