Adding a linked server causes error 15028 in SQL Server (274098)

SYMPTOMS

When configuring new subscribers for replication, Microsoft SQL Server adds the subscriber as a remote server and stores its information in the master..sysservers system table. Subsequent attempts to add a linked server using the same name as an existing subscriber may cause error 15028 to occur:

Server: Msg 15028, Level 16, State 1, Line 0
The server 'SQL1' already exists.

CAUSE

Information for both linked servers and remote servers is stored in the master..sysservers system table. Because the sp_addlinkedserver stored procedure requires the server names to be unique, creating a linked server of the same name as an existing remote server causes the 15028 error to occur.

WORKAROUND

A workaround to this problem is to modify the remote server status to allow data access, which effectively changes a remote server into a linked server.

This can be accomplished by running the following script:

USE master
go

sp_configure 'allow updates', 1
go

RECONFIGURE WITH OVERRIDE
go

CREATE PROCEDURE changestatus @servername sysname AS
   DECLARE @srvstat smallint

   SELECT @srvstat = srvstatus
      FROM master.dbo.sysservers
      WHERE srvname = @servername

   -- Configure the remote server for 'data access'
   SELECT @srvstat = @srvstat | 32 | 128

   UPDATE master.dbo.sysservers
      SET srvstatus = @srvstat
      WHERE srvname = @servername
go

EXEC changestatus 'yourservername'
go

sp_configure 'allow updates', 0
go

RECONFIGURE WITH OVERRIDE
go

After this script is run, the remote server involved in replication will also work as a linked server.

MORE INFORMATION

To understand what the stored procedure is doing, consider the following information. The status for a computer that is running SQL Server that is added by using sp_addlinkedserver is 225, and the status that represents a subscriber in replication is 4. The combination of these values yields a linked server that is also a subscriber (225 + 4 = 229).

When added as a subscriber in replication, a computer that is running SQL Server becomes a remote server with a status of 69. The result of running the stored procedure above on a remote server sets the status to 229, which matches the status of a linked server that is configured as a subscriber.

The limitation of this stored procedure is that it does not make login impersonations and customized mapping of local and remote logins. Use the sp_addlinkedsrvlogin in addition to the above stored procedure to map logins. For more information about this command, see SQL Server 7.0 Books Online.

Note If you use the workaround that is mentioned in the "Workaround" section on Microsoft SQL Server 2000 with replication, the value will be set to a different number. On SQL Server 2000, after the changestatus stored procedure is run on this remote server, the status changes to 1253.