1

I renamed SQL instance

exec sp_dropserver 'oldname'
go
excec add_linkedserver 'newname'

Rebooted server

select @@servername shows new server, but, exec sp_helpserver shows old and instance names.

In the registry, i updated name under SQL server keys but it didn't change anything

EDIT

As soon as i run sp_dropserver 'oldname', 'droplogins'

and run exec sp_helpserver

it shows only new name but if i run sp_helpserver again, old one appears too in second row

2 Answers 2

4

According to your post, you've used sp_addlinkedserver which is not the partner procedure for sp_dropserver. The correct partner is sp_addserver. Please note that depending on the setup, configuration, and feature use, changing the server name may cause unintended side effects.

4
  • sp_addserver is not supported, getting error and sugestion to use sp_addlinkedserver
    – overflowed
    Sep 22, 2023 at 14:25
  • How is addserver not supported? That's the proper way. Adding a linked server is incorrect. Sep 22, 2023 at 14:28
  • nakulvachhrajani.com/2012/05/28/…
    – overflowed
    Sep 22, 2023 at 14:29
  • 2
    I'd suggest you read the article, "Replace remote servers by using linked servers." this isn't a REMOTE server. Sep 22, 2023 at 14:33
-3

Run the below query to update the server name in the sys.servers catalog:

USE master;
GO

-- Update the server name in sys.servers
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'allow updates', 1;
RECONFIGURE;

UPDATE sys.servers SET data_source = 'newname' WHERE server_id = 0;

-- Turn off the ability to update system catalogs
EXEC sp_configure 'allow updates', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
3
  • 1
    You should never update or attempt to update any system tables directly. Sep 22, 2023 at 14:22
  • ok, what is other solution to my problem ?
    – overflowed
    Sep 22, 2023 at 14:31
  • Ad hoc updates to system catalogs are not allowed error
    – overflowed
    Sep 22, 2023 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.