Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unhappy Unanswered: Orpahaned Push Subscriptions

    I am very new to SQL replication and Enterprise manager, so please be specific where I run stuff.

    My Setup:
    Server A - Publisher and Distributer
    Server B - Subscriber

    I created a Push Merge publication on Server A to Server B and everything was working.

    I deleted the publication from Server A and I thought that would remove it from Server B.....NOT!

    So now I have this 'orphan' subscription on Server B and I can't get rid of it. It is locking me out of the replicated tables so I can't create a new publication that replicates the same tables as the first one.

    The subscription line on Server B looks like this:
    Name: EDL-ESDIII01:Apps:Manila Tables
    Database: Apps
    Type: Push
    Status: Succeeded
    Last action: No data needed to be merged
    Replication Type: Merge

    I've tried everything I know, which is little in this instance, I've shutdown replication on Server A, rebooted both servers, etc...

    Please Help! I'm under the gun to get this working.

    Thanks,
    Greg

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Orpahaned Push Subscriptions

    Did you drop all subscriptions in Server B before you Drop the publication in Server A?

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    No, actually it doesn't look like you can do that easily, the help in enterprise manager says just to delete it from server A.....

    Greg

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    OK. One more question! Did you do all the create and drop thru EM or you wrote some scripts to do it?

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    All through EM....I tried to do sp_dropmergesubscriptions on Server B, but I don't know enough about the syntax to know what to put in for some of the arguments.

    Thanks,
    Greg

  6. #6
    Join Date
    Mar 2004
    Location
    Ireland
    Posts
    3
    Hi, I have exactly the same problem as you.... Did you find a solution?

    Hoping for a reply.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Do you get any errors running the following command on your Subscriber?

    exec sp_dropmergesubscription 'ESDIII01', 'YOUR_SUBSCRIBER', 'Apps', 'push', 1, 0

  8. #8
    Join Date
    Mar 2004
    Location
    Ireland
    Posts
    3
    Thanks for the reply. I ran sp_helpmergesubscription and the offending subscription was shown. I ran sp_dropmergesubscription (not exactly as you typed) but it objected to (a) no publication and then (b) the non existence of the the publication (copied from the detail in sp_helpmergesubscription). Of course, that publication is not there - I dropped it in Enterprise Manager! It feels like catch22..... Q. What table/database holds the information that sp_helpmergesubscription uses to display its detail?

  9. #9
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    run it at SERVERB
    UPDATE sysobjects SET replinfo=0 WHERE xtype='U'
    --make sure ALLOW UPDATE SYSTEM CATALOG is checked in server
    --properties
    Take the backup b4 doing this. I squared it away once using this.

    Also if u restore a replicated database, without explicitly specifying KEEP_REPLICATION option, replication is not preserved. So u can also try backing up ur subscriber and the restoring it.


    Regards!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •