Replication Issue after Rename the server - Let's fix happily

While trying to setup a new subscription for SQL Server 2005 Replication using Management Studio you receive this error:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'servername\instance'. (Replication.Utilities)

Fix :

To fix this error you need to correct @@SERVERNAME so that it matches your actual machine\instance name.

-- Check that the internal @@SERVERNAME name machines the machine + Instance name SELECT @@SERVERNAME As [@@SERVERNAME],             CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('\' + CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName; -- Script to correct the @@SERVERNAME DECLARE @InternalInstanceName sysname; DECLARE @MachineInstanceName sysname; SELECT @InternalInstanceName = @@SERVERNAME,       @MachineInstanceName = CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128))                         + COALESCE('\' + CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), ''); IF @InternalInstanceName <> @MachineInstanceName BEGIN       -- Rename the instance       EXEC sp_dropserver @InternalInstanceName;       EXEC sp_addserver @MachineInstanceName, 'LOCAL'; END -- You now need to restart the server for the changes to take effect

  

What happen?

It seems to happen from time to time: your network engineers decide on a new network topology or naming scheme and they want to rename one or more of your SQL Server machines or worse your desktop machine!

Renaming a SQL Server instance is not as simple as renaming the computer in My Computer Properties. After you've restarted windows, you will find that while the instance is now available on the network with the new machine name, internally @@SERVERNAME will probably still be using the old name. This will upset a number of features within SQL Server and Management Studio and some 3rd party tools.

Using the following sql, you can see if @@SERVERNAME has the incorrect value:

SELECT @@SERVERNAME As [@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

Both @@SERVERNAME and RealInstanceName should be identical. After a recent name change my results looked like this:

@@SERVERNAME                      RealInstanceName

--------------------------------- ---------------------------------

issue-pavel                      pavel-server

To correct @@SERVERNAME for a default instance use the following commands:

exec sp_dropserver old_name
GO
exec sp_addserver new_name, 'local'
GO

To correct @@SERVERNAME for a named instance:

exec sp_dropserver old_nameinstancename
GO
exec sp_addserver new_nameinstancename, 'local'
GO