Replication issue
-
Hey all, I could really use some ideas on this one. I'm receiving this error from two separate locations. They are both push subscriptions and I'm still a replication newbie. Any help would be greatly appreciated.
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The process could not bulk copy into table '"dbo"."MSmerge_contents"'.
(Source: SERVER899 (Agent); Error number: 20037)
---------------------------------------------------------------------------------------------------------------
Cannot insert duplicate key row in object 'MSmerge_contents' with unique index 'uc1SycContents'.
(Source: SERVER899 (Data source); Error number: 2601)
---------------------------------------------------------------------------------------------------------------
Function sequence error
(Source: SERVER899 (ODBC); Error number: 0)
---------------------------------------------------------------------------------------------------------------
-
This was removed by the editor as SPAM
-
looks like you are trying to insert a duplicate value in a column(s) with a unique index on your merged database.
my questions are:
Do these 2 tables need to be merged since it looks like they contain identical data from the error? (you could just remove 1 of these tables from one of the publishers) Otherwise, can you remove the unique index on the merged table?
i've only been replicating a year, so i'm still a newbie myself. i know this probably doesn't help a lot, but i DO know what a pain it is to try to solve these issues with no feedback.
good luck!
😎
-
Hi Peps!
Sorry for the BUMP...
I have just come into work this morning to have this brick wall of a SQL error hit me.
I Have seached high and low on goggle and MS web site, and this is the only fourm post i have found to have the exactly same error!
Is there anyone two years on from the original post that has even come across this error, let alone sloved it?
Here is my current config...
One DB (bout 3GB)
One Publication (all but a couple of tables in articles)
Merege Replication
One Push Subscription
Running across a 2mb WAN
I set this replication up in January and has been working a treat, only dropping every so often when the WAN goes down.
Here is what i have tried so far...
Validate All Subscriptions
Validate and Resynchronize Subscriptions
Ran script to find any duplications in the MSMerege_Contents table
Every time i starts synchorisation it runs for about 20mins.
At first in the agent history, it states it has 'A column was added to or dropped from the replicated table' then
It starts to 'upload 102 data changes' followed by
Bulk copying data into table 'MSmerge_contents'
Bulk copying data into table 'MSmerge_gethistory'
Bulk copying data into table 'MSmerge_tombstone'
Bulk copying data into table 'sysmergesubsetfilters'
and then the brakes go on with the error...
The process could not deliver the snapshot to the subscriber.
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The process could not bulk copy into table '"dbo"."MSmerge_contents"'.
(Source: SERVER899 (Agent); Error number: 20037)
---------------------------------------------------------------------------------------------------------------
Cannot insert duplicate key row in object 'MSmerge_contents' with unique index 'uc1SycContents'.
(Source: SERVER899 (Data source); Error number: 2601)
---------------------------------------------------------------------------------------------------------------
Function sequence error
(Source: SERVER899 (ODBC); Error number: 0)
---------------------------------------------------------------------------------------------------------------
and this is the loop its stuck in, any ideas?
Thanking you very much in advance
Regards
Lee
-
I dont do a lot of merge, but I'd start with the dupe key error. You could either temporarily change the index definition of 'uc1SycContents' to be non-unique, or I think the merge agent should support a skiperror switch that might let you proceed past it, though I dont know the consequences of doing so in this case. It's not working anyway, have to try something.
-
This is for SQL 90, but I believe that it would still apply.
From http://technet.microsoft.com/en-us/library/ms151779(SQL.90).aspx
This error can occur because of an incorrect value for the join filter property join_unique_key. This property should be set to TRUE only if the joined column in the parent table is unique. If the property is set to TRUE, but the column is not unique, this error is raised. For more information on setting this property, see How to: Define and Modify a Join Filter Between Merge Articles (SQL Server Management Studio) and How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
So... check the join filters in your publications.
Maybe. Hope it helps.
-
Thank you all for your help!
The info pointed me in the right direction; this is what I have done so far...
Put a tick in the box 'ignore duplicate values' in the 'uc1sycContents' manage index config. On both the subscriber and the publication.
Tried the 'validate all subscriptions' with 'verify the row counts and compare binary checksums' option and this took over 30hrs across a 256k link! This then came back with quite a few tables witch failed validation, but mainly passed.
Next I queried both 'MSmerge_contents' tables on the subscriber and publisher. The subscriber table had two lots duplicate 'rowguid's. On the advice from my boss and other threads regarding similar duplication rowguids, I copied these rows out and reinserted them to generate new guid's. So far so good.
Now I have started a 'validate and resynchronize subscription' with just selecting resync option and setting it to run transactions from three weeks ago onwards, which is just before I went on leave when it was working and in sync!
It is currently still running and by the looks of the 'agent history' many more data changes are taking place than usual. Which is indicating to me that resync is taking place? Am I right in assuming this?
Any comments to what I have done right or wrong please let me know!
Thanks again...
Lee
-
Hi,
In your case, looks like replication setup process was NOT completed successfully.
Drop the subscription/publication and try to recreate the replication process again. (Need to identify data which is more current ...publication db or subcribtion db)
Also check whether all primary keys (identity related) are not enabled for replication. Besides foreign key is NOT marked to "enforce relationship during replication"
It is advisable not to manually insert into any of the system related tables for replication .
check MSmerge_tombstone system table, there should be any rows in this table for type=6 (points out FK is enforced for replication)
HTH
rangark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply