3

I have changed the computer name of the server where SQL Server 2008 R2 is installed.

  • Old name was WIN-OUJEKRMGXXX
  • New server name is KASURDYYY

When I try to configure replication, the following error occurs:

SQL Server Replication requires actual server name to make a connection to the server ... Specify the actual server name, WIN-OUJEKRMGXXX

I updated the server name in SQL Server in order to try to resolve the issue. I ran the following queries:

execute sp_dropserver 'WIN-OUJEKRMGXXX';
execute sp_addserver 'KASURDYYY', 'local';

...but the same error still occurs. I checked the server name :

select @@SERVERNAME -- returns WIN-OUJEKRMGXXX
select SERVERPROPERTY('SERVERNAME') -- returns KASURDYYY
0
3

sp_dropserver 'real-server-name' was giving an error about remote logins:

There are still remote logins or linked logins for the server ...

Therefore, I executed the following query:

execute sp_dropremotelogin old_physical_server_name;

...but this gave the following error because of replication:

There is no remote user '(null)' mapped to local user '(null)' from the remote server ...

Then I executed:

execute sp_removedbreplication 'database-name-on-which-publication-exist';
execute sp_dropdistributiondb 'distribution-database-name';

...still the same error. Finally I tried:

execute sp_dropdistributor @no_checks = 1, @ignore_distributor = 1;
execute sp_dropserver 'real-server-name';
execute sp_addserver 'nwe-server', 'local';

This resolved the issue.

1
  • What are the implications to any: live applications, SSIS packages, SSRS reporting, etc that may already be connected to the server? Is this going to interfere in any way? Will they all need to be updated? Nov 19 '18 at 15:42
0

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME    
go

This should return the name of the server. If it does not then follow the procedure below to correct it.

To Resolve the issue

To resolve the problem the server name needs to be updated. Use the following:

sp_addserver 'real-server-name', LOCAL

if this gives an error complaining that the name already exists then use the following sequence:

sp_dropserver 'real-server-name'    
go
sp_addserver 'real-server-name', LOCAL    
go

If instead the error reported is 'There is already a local server.' then use the following sequence:

sp_dropserver old-server-name 
go  
sp_addserver real-server-name, LOCAL
go

Where the "old-server-name" is the name contained in the body of the original error.

Stop and restart SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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