none
spAddMergeSubscription fails with subscription already exists

    Question

  • When executing sp_addMergeSubscription it fails with the error 

        exec sp_addmergesubscription @publication = 'ArizonaCASH_Pub'
    	    , @subscriber = 'BALE-S1'
    	    , @subscriber_db = 'ArizonaCASH'
    	    , @subscription_type = N'Push'
    	    , @sync_type = N'None'
    	    , @subscriber_type = N'local'
    	    , @subscription_priority = 75
    	    , @description = null
    	    , @use_interactive_resolver = N'False'
    	    , @hostname = 'some_guid'
    	    ;

    Msg 14058, Level 16, State 1, Procedure sp_addmergesubscription, Line 381
    Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.

    Tried to run sp_dropmergesubscription with the proper parameters (tried ignoredistributor = 1 ), it completes instantly without error. 

    exec dbo.sp_dropmergesubscription 
    	  @publication = 'ArizonaCASH_Pub' 
    	, @subscriber = 'BALE-S1'
    	, @subscriber_db = 'ArizonaCASH'
    	, @subscription_type = 'All' 
    	, @ignore_distributor = 1 
    

    At the subscriber I already ran sp_removeDbReplication in the subscriber database, there appear to be no replication objects left there. A select from sysobjects returns nothing like msmerge% 

    At the publisher, the failed attempt at sp_addMergeSubscription appears to write lines in sysmergesubscriptions (for this subscriber). I removed those lines manually and retried to add the subscription with the same result. 

    Any ideas why this is happenning ? This is a prod box, of course none of this garbage ever happenned in test :( 

    The ultimate solution for me would be to drop all subscribers, nuke the publication, recreate it and re-add all the subscribers. I know there is powershell for that but if it can be avoided... 

    Forgot to mention both publisher and subscriber are 2008R2, Enterprise for the Publisher, Workgroup for the subscriber.




    • Edited by m1ghty Bear Tuesday, November 12, 2013 10:41 AM
    Tuesday, November 12, 2013 10:20 AM

Answers

  • Ok, ended up nuking everything, no more time to wait.

    All subscriptions killed, publication dropped and recreated, subscriptions backup up everything is fine and dandy.

    • Marked as answer by m1ghty Bear Tuesday, November 12, 2013 1:12 PM
    Tuesday, November 12, 2013 1:12 PM

All replies