I've got one SQL 2005 SP2ÃÂ server setup as Publisher and distributorÃÂ which replicates through Merge pull subscriptions on SQL 2000 SP4ÃÂ machines. Some of these SQL 2000ÃÂ machines does republishing too. However, after a columndrop on one of the articles at the SQL 2005 machineÃÂ by usingÃÂ sp_repldropcolumn
the whole subscription sync came down on me with failures all over. AllÃÂ had theÃÂ same error :
"Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'"
This procedure was called byÃÂ sp_MScreateglobalreplica.
I have found that there are invalid publications in sysmergepublications, but onlyÃÂ on publishers for the SQL 2000 subscription machines(excluding valid republishing publications). Deleting these pubID's does not help at all. With the first sync of any pull subscription (after the delete) they re-appear on the SQL 2005 machine.
However, I need to get the pull subscriptions going again. How can I clean the system tables to prevent any errors like above or duplicate keys in the sysmergesubscription table... Please do not recommend an overhaul. I need step by stepÃÂ procedures to get it going.
View Complete Post