none
Catch 22 Error

    Question

  • I am trying to create a push subscription for a transactional publication.  When I get to the finishing stage of the wizard I get an error during the 'Creating subscription...' action.  The details of the error tell me that:

    The server '<SubscriberServer>' is already a Subscriber.

    The server '<SubscriberServer>' is not a Subscriber.

    Changed database context to 'master'. (Microsoft SQL Server. Error: 14040)

    That's right, it already is but it isn't.  So, I'm damned if I do, damned if I don't.  How do I clear this up so that I get one or the other error?  Hopefully, in doing so I won't have to worry about any errors.

    Tuesday, July 03, 2012 9:53 PM

Answers

  • Hi NHOutback2012,

    >>The server '<SubscriberServer>' is not a Subscriber. The server '<SubscriberServer>' is not a Subscriber.

    Regarding to the error message, basically might be related to 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. Delete existing replication setup (Distribution database\Subscription\Publication etc.)
     From Query Analyzer, run the following commands:
    sp_configure 'allow',1 go
    reconfigure with override go
    DELETE master..sysservers WHERE srvname = 'repl_distributor'

    2. recreate Distribution Database, create Publication. Now, while creating publication you will be prompted to select the articles to replicate.

    3. Create New Subscription.


    Regards, Amber zhang

    Thursday, July 05, 2012 3:18 AM
    Moderator
  • First off, I was mistaken about the servers in question.  They are indeed running SQLSvr2k.  So I was able to delete the repl_distributor on the publisher server.

    Once that was complete I could remove replication on both Publisher and Distributor.  Now everything is running fine.

    Thursday, July 05, 2012 5:33 PM

All replies

  • Hi NHOutback2012,

    >>The server '<SubscriberServer>' is not a Subscriber. The server '<SubscriberServer>' is not a Subscriber.

    Regarding to the error message, basically might be related to 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. Delete existing replication setup (Distribution database\Subscription\Publication etc.)
     From Query Analyzer, run the following commands:
    sp_configure 'allow',1 go
    reconfigure with override go
    DELETE master..sysservers WHERE srvname = 'repl_distributor'

    2. recreate Distribution Database, create Publication. Now, while creating publication you will be prompted to select the articles to replicate.

    3. Create New Subscription.


    Regards, Amber zhang

    Thursday, July 05, 2012 3:18 AM
    Moderator
  • Amber, thanks for the response. 

    I saw that post as well and followed the steps.  However, this probably works for SQLServer2k but I am using 2k5 all the references to Query Analyzer are outdated.  Plus they create problems down the line. 

    For example, after deleting all entries in msdb..msdistributions, you cannot simply go back and reconfigure replication.  You have to manually drop the distribution database (using alter database distribution set offline; drop database distribution;) and delete the logical files as well.  It gets to be messy.

    Any other ideas on how to address this issue, namely, I get two error causes and they are diametrically opposite each other: One says it is already a subscriber and the other says it isn't.  I'm thinking there are two tables involved but which ones?

    Thursday, July 05, 2012 2:25 PM
  • Actually, I think I stumbled on to something.

    I looked at sysservers on the publisher server and I see both the Publisher server (S1) and Distributor/Subscriber server (S2) under svrname.  But there is also another svrname called repl_distributor.  This is even when I have completely cleaned off any replication on the distributor server.  So I think that's the problem.  But how to correct it...

    I tried to run:

    sp_dropdbreplication '<databasename>'

    on the publisher and it executed fine.  I then ran:

    sp_dropserver 'repl_distributor', 'droplogins';

    on the publisher but it returns

    Cannot drop server 'repl_distributor' because it is used as a Distributor in replication.

    So I ran the following on the publisher

    sp_configure 'allow', 1;

    Reconfigure with override;

    Delete from sysservers where srvid = '1' (which is the srvid for repl_distributor)

    But I get the 'Ad hoc updates are not enabled...' error.

    Any other ideas?
    Thursday, July 05, 2012 3:13 PM
  • First off, I was mistaken about the servers in question.  They are indeed running SQLSvr2k.  So I was able to delete the repl_distributor on the publisher server.

    Once that was complete I could remove replication on both Publisher and Distributor.  Now everything is running fine.

    Thursday, July 05, 2012 5:33 PM
  • Just in case anyone else has this issue, I have another variation for a fix -

    Check your Linked Servers!

    I had this exact same problem and it had me stumped for a good couple of hours. I stumbled across a Linked Server on the Publisher with the same name as the Subscriber so (after scripting it out) I dropped it and ran the sp_addsubscriber '{SubscriberName}', @type=0, @security_mode=1 and re-ran my replication scripts. Bingo.

    Thought I'd put this up here in case any poor soul still has to create replication on SQL2000!!!

    Regards,

    Paul

    • Proposed as answer by Paul Duffett Thursday, October 03, 2013 3:47 PM
    Thursday, October 03, 2013 3:46 PM