Microsoft KB Archive/274098

= Adding a linked server causes error 15028 in SQL Server =

Article ID: 274098

Article Last Modified on 3/16/2007

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q274098



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.

Note Do not run the script on the repl_distributor remote server. If you do, you will receive one of the following error messages:

Message 1

Error 627 &quot;Cannot use SAVE TRANSACTION within a distributed transaction&quot;

Message 2

Error 7391 &quot;The operation could not be performed because the OLE DB provider &quot;SQLOEDB&quot; was unable to begin a distributed transaction&quot;



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 &quot;Workaround&quot; 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.

Keywords: kbprb KB274098

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.