Problem replicating table has the same data (primary uniqueidentify) in both publish & subscriber

microsoft.public.sqlserver.programming

27 Jul 2006 11:37 PM

John

I can replicate most of my tables between two servers, the only problem
is on some of the meta data tables where publisher and subscriber both
has the same data (including the primary key ) before the replicate, it
failed with following error:
Replication-Replication Distribution Subsystem: agent
RSMCA7-DRPSRVR-JMPS.Repl-test-machinaname failed. The process could not
bulk copy into table '"dbo"."tablename".

If I delete the data from the subscriber before I replicate, it works
with no error.

The reason I want to replicate those metadata table is even those table
has the identical data right now, it may change and could get out of
sync. But in the mean time I can not leave the data in the subscriber
empty because I do not know when the user will turn on the replication.

The tables I am trying to replicate has the uniqueidentify as a primary
key,
I set the option Action if name is in use to "Keep existing object
unchanged"
I am using SQL2005 and Transaction Publication & push subscription.

Please advice
Thanks in advance.
John

28 Jul 2006 10:43 PM

Hilary Cotter

You need to somehow only send the unique data to the subscriber in your bcp
operation. to do this you would need to create a custom sync object which
references the subscriber metadata table.

Here is an example of a custom sync object. In your view you would have to
use a linked server (use data_access true for the linked server) to query
the subscriber and do a not in.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"John" <john***@yahoo.com> wrote in message
news:1154043464.181119.295790@p79g2000cwp.googlegroups.com...
>I can replicate most of my tables between two servers, the only problem
> is on some of the meta data tables where publisher and subscriber both
> has the same data (including the primary key ) before the replicate, it
> failed with following error:
> Replication-Replication Distribution Subsystem: agent
> RSMCA7-DRPSRVR-JMPS.Repl-test-machinaname failed. The process could not
> bulk copy into table '"dbo"."tablename".
>
>
> If I delete the data from the subscriber before I replicate, it works
> with no error.
>
> The reason I want to replicate those metadata table is even those table
> has the identical data right now, it may change and could get out of
> sync. But in the mean time I can not leave the data in the subscriber
> empty because I do not know when the user will turn on the replication.
>
> The tables I am trying to replicate has the uniqueidentify as a primary
> key,
> I set the option Action if name is in use to "Keep existing object
> unchanged"
> I am using SQL2005 and Transaction Publication & push subscription.
>
>
> Please advice
> Thanks in advance.
> John
>