@@SERVERNAME is NULL & SQL Server Replication

Issuing the following query should return the server name of the instance you are working with. In the scenario that is about to be described, the server had some work done on it which left the query below returning NULL.

This causes problems when attempting to run tools such as the replication monitors and wizards as they appear to use this query internally and thus complain that a connection cannot be made to a (NULL) server during the login process. Also various other errors start occurring using Server Management Studio.

SELECT @@SERVERNAME

SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

Change @@SERVERNAME

All the server names “registered” can be viewed with

sp_helpserver

In the returned table it was possible to see the Linked server, but there was no server with the ID column of 0. The row with the ID column of 0 is the server name returned when the @@SERVERNAME property is used. To set this add a server with the following;

sp_addserver '<YourServerName>','local'

The local parameter tells it that you are talking about this special server name. However attempting this in this case resulted in the server complaining that the server already existed, due to the LINKED server.

It should be possible to drop the existing linked server by issuing the following command;

SP_DROPSERVER '<YourServerName>','droplogins'

This didn’t work as it complained that the server was acting as a publisher. Argh!

Temporary Change to @@SERVERNAME

In order to “getinto” the SQL tools I creating a ficticous servername, adding that servername to the local hosts file of the server.

sp_addserver '<YourServerName>TEMP','local'

In the hosts file C:\WINDOWS\system32\drivers\etc\hosts

127.0.0.1       localhost
127.0.0.1    <YourServerName>TEMP

YOU MUST restart the SQL server service for the @@SERVERNAME variable to refresh, easy to forget!

So now @@SERVERNAME returned a false servername, however at least working on the server I could use Management Studio to connect to the server under this fake name and start accessing normal tools for replication.

Removing replication

At this point the aim was to totally remove replication and then add it all back in once it was all gone. To do this there is a guidance document How to manually remove a replication in SQL Server 2000 or in SQL Server 2005this seemed to work for 2008 too.

For each replicated database, this was used to rip out all replication from the databases.

exec master.dbo.sp_removedbreplication '<replicated_databse_name'

sp_dropdistributor @no_checks=1, @ignore_distributor=1

alter database distribution set offline
drop database distribution

master.dbo.sp_serveroption @server=N'<YourServerName>', @optname=N'dist', @optvalue =N'false'

Helpful help functions the last two seemed to be the final step needed in this case to allow the dropping of the various objects (linked server and replication database).

sp_msrepl_check_server
sp_helpdistpublisher
sp_get_distributor
sp_helpdistributor
sp_helppublication
select * from msdb..msdistpublishers
delete from msdb..msdistpublishers

/****** Object:  LinkedServer [<YourLinkedServerName>]    Script Date: 02/18/2011 02:43:09 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'<YourLinkedServerName>', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'<YourLinkedServerName>',@locallogin=NULL 
  @useself=N'False',@locallogin=NULL,@rmtuser=N'webreplication',@rmtpassword='########'
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'<YourLinkedServerName>',@locallogin=NULL 
 @useself=N'False',,@locallogin=NULL,@rmtuser=N'webreplication',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'dist',
 @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'data access',
 @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'sub', @optvalue=
N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'pub', @optvalue=
N'false'

Changing @@SERVERNAME to what it should be

Now it was possible to change the servername back to what is should have been in the first place, and remember to delete the entry in the hosts file.

SP_DROPSERVER '<YourServerName>TEMP','droplogins'
sp_addserver '<YourServerName','local'

sp_helpserver

SQL Jobs