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
Line 381 is inserting the subscriber to sysmergesubscriptions. I think you need to check what the identity setting is for this table and compare it with the values in the row.
For example if dbcc checkident(sysmergesubscriptions) returns a value of 2 for current identity value and current column value is 3, you need to reseed it to 3.
Also check to verify there are no application locks or open transactions which are merge replication related in this database.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941