1

Environment: Merge pull subscription with anonymous subscribers, using web sync, publisher is 2008R2, subscribers are SQLEXPRESS 2008R2.

Issue: I run sp_dropmergepullsubscription on subscriber and sp_dropmergesubscription on publisher, subscription is removed on subscriber, but why do I see an entry in sysmergesubscriptions on publisher, for that subscriber, with status 1 still?

I found a way here which suggests to manually delete entries, but why do I have to manually delete stuff?

Is it required to run sp_dropmergesubscription on publisher after running sp_dropmergepullsubscription on subscriber? Just deleting on subscription on subscriber side is not enough?

Community
  • 1
  • 1
Brian
  • 1,337
  • 5
  • 17
  • 34

1 Answers1

3

Do not manually delete the entries in sysmergesubscriptions. Manually deleting these entries can result in not being able to add new subscribers down the road.

To delete a pull subscription to a Merge publication at the subscriber on the subscription database execute sp_dropmergepullsubscription. Then at the publisher on the publication database execute sp_dropmergesubscription. This is covered in How to: Delete a Pull Subscription.

Executing sp_dropmergesubscription at the publisher will not remove the entry from sysmergesubscription but will set the status to 2, which indicates it has been deleted.

If the status is still 1 in sysmergesubscriptions then perhaps you executed sp_dropmergesubscription incorrectly. Make sure to specify @publication, @subscriber, and @subscriber_db. Also, specify a value of pull for @subscription_type.

Brandon Williams
  • 3,695
  • 16
  • 19
  • Thank you. Too late I deleted them manually. Before that, I did run the stored procedures correctly and no matter what it wouldn't set the status to 2 though the command runs successfully on the publisher side. Any thoughts? – Brian Mar 07 '13 at 17:47
  • I just noticed you have anonymous subscriptions. Have you tried specifying @subscription_type of anonymous for sp_dropmergesubscription? – Brandon Williams Mar 07 '13 at 17:59
  • hm. no. I will try and let you know. btw, running the drop on publisher side is a must do step2 or the subscription removal process? – Brian Mar 07 '13 at 18:05
  • Yes. Need to run sp_dropmergepullsubscription on the Subscriber then sp_dropmergesubscription on the Publisher. – Brandon Williams Mar 07 '13 at 18:56
  • "Manually deleting these entries can result in not being able to add new subscribers down the road". Hm. I just did that.. can you please elaborate on this? How is this going to screw me later? Is there anything I can do now to remedy what I did? – Brian Mar 08 '13 at 15:00
  • It might have worked since you are using anonymous subscriptions. Strictly speaking, directly editing system tables leaves you in an unsupported state and is not recommended. – Brandon Williams Mar 08 '13 at 16:52
  • i also tried everything with no luck. i set Status=2 in sysmergesubsciptions for the subscription, which allowed me to recreate it (same name but now Status=0). i am using the sub, but I imagine you could delete it normally through SSMS after doing these steps. – smoore4 Apr 20 '23 at 16:25
  • 1
    Nice! 10 years later and a little more wiser, I no longer shy away from editing system tables, especially if that's what's required to solve a possibly tragic situation. It's nice to hear that there is someone else besides me still using Merge replication today. – Brandon Williams Apr 21 '23 at 01:00