SQL Server Performance Forum – Threads Archive
Error 14040 – the server is already a subscriber.
Hello all,<br /><br />I have a new transactional publication. When I try to push a subscription from this publication to another server I get this error:<br />"Error 14040: the server is already a subscriber."<br /><br />The respective server is present in the Subscribers list (Publisher and Distributor properties — Subscribers tab) and it’s not enabled. I’ve got the same error message when I tried to enable it.<br /><br />This is the outcome of "select sub, srvname from sysservers where srvname=’Myserver’":<br /><br />sub srvname <br />—- ——————- <br />1 Myserver<br /><br />Will this solve the problem?<br /><br />USE <<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ublication database name><br />GO<br />EXEC sp_dropsubscription @publication = N’<<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ublication name>’, @article = N’all’, @subscriber = N’all’, @destination_db = N’all'<br /><br />Thank you,<br />peti<br />Basically appears that the distribution database has been corrupted, try to perfom the following: 1. From Query Analyzer, run this SELECT statement: SELECT * from msdb..msdistributiondbs
2. If a row is returned delete it.
3. Then, reconfigure replication. From Enterprise Manager, go to Tools->Replication-> Configure publishers, subscribers,and distribution.
4. Run Step 3 again, and you should then be able to create the distribution If that doesn’t work, you may need to remove replication from the server and reestablish replication. Please be aware that these steps will remove replication from the server. If you are uncertain about how to re-establish replication, refer to books online for more information. 1. From Query Analyzer, run the following commands:
sp_configure ‘allow’,1 go
reconfigure with override go
DELETE master..sysservers WHERE srvname = ‘repl_distributor’ 2. Re-run the replication wizard and re-establish replication on the server. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
In addition to reconfiguring replication on the server, you may need to delete entries from sysservers and sysxlogins that correspond to the subscriber:
declare @srvid int select @srvid = srvid from master..sysservers where srvname = ‘YourSubscriberDB’ delete from master..sysservers where srvid = @srvid delete from master..sysxlogins where srvid = @srvid
]]>