sql server replication: Hello everybody
I have to set up a transactional replication between two server W2k with sql2000 sp3
I'm experiencing problems with initial snaphot, because:
1 - the replicated table at subscriber is not initially empty (and I can't loose data already present in it)
2 - it could contain some rows already present at the publisher

Due to the problem (2) bulk copy of initial snapshot fails (for duplicated primary key)

How could I override this problem
Thank

also consider the continue on data consistency errors profile.

You could do a subscriber validation to see how out of sync your data is,
and then try to reconcile it as Paul mentions.

Here is a script that I normally use when I am trying to reconcile the data
on the subscriber with the publisher. titleauthorpub is a table which I have
DTS'd over from my publisher to my subscriber to check for consistency
locally on the subscriber. Make sure you kick everyone off your Publisher
and Subscriber when you do this.

To tell which rows have different values in one or more columns, I check to
see which tables have different binary check sum values in them, as
illustrated below:

select

ABS(binary_checksum(c.au_id,c.title_id,c.au_ord,

c.royaltyper)-

binary_checksum(a.au_id,a.title_id,a.au_ord,a.royaltyper)),

c.au_id, c.title_id from titleauthorpub as c ,(select
title_id,au_id,au_ord,royaltyper from titleauthor) as a

where a.au_id=c.au_id

and a.title_id=c.title_id

order by 1 desc

The results set from this query look like this:


au_id
title_id

-----------
-----------
--------

96
427-17-2319
PC8888

0
472-27-2349
TC7777

0
486-29-1786
PC9999

0
486-29-1786
PS7777

0
648-92-1872
TC420


The row with a binary check sum value that is not 0 is different on the
Publisher and Subscriber. You could do a simple check sum, but the
performance hit using a binary check sum isn't that significant for smaller
tables and it tends to be a more rigorous analysis of differences.

[quoted text, click to view]

Marco,
you'll need to reconcile the data before initializing. On the publisher you
could create a linked server to the subscriber and then determine which is
the correct data. To find duplicate rows and required subscriber rows you
could use binary checksums. Once all the correct data is on the publisher
then you could initialize with selecting @sync_type = 'none'.
HTH,
Paul Ibison