Monday, March 12, 2012

Merge Replication Missing Some Data

Hi All,
I have a curious problem. I have a replication to my branch that has been running over 8 months. Everything works fine but once in a blue moon I get this problem.
Replication Agent reports all replication is successful.
however, 1-2 rows of data in the branch data does not match with the publisher ( we know this because some reports in the branch that pulls this data do not tally)
no matter how many time I re-run the merge.. the data will not be merge down to the branch. The only way to get it to merge down to branch is to go to EM select that rows of data and basically re-key in the same value and save. Then the merge will pull
the data down.
Any ideas? like I say, this does not happen very often. I think only 2-3 times over the 8 months. I am not able to reproduce it mannually, I tried many ways like inserting/updating/deleting rows during a merge.. etc etc... .. but not able to reproduce
the same problem.
The replication is a standard merge replication. Server is SQL2000 SP2, branch is MSDE 2000 SP3. Nothing fancy, just pulling the data down to branch for some crystal reports.
any ideas, or ways to check? The only thing I can think of is that we are using some Chinese character... but then why only 1-2 rows.. and why not consistancely happen...
Jonathan
Jonathan,
could be a collation issue.
Are the column level collations identical on publisher and subscriber?
(this is possible because if you don't check the collation box for the
article, then when the table is created on the subscriber it picks up the
collation of the subscriber's database.)
Regards,
Paul Ibison
|||Paul,
How do I check the collation of the publisher and
subscriber?
Thanks
Emma

>--Original Message--
>Jonathan,
>could be a collation issue.
>Are the column level collations identical on publisher
and subscriber?
>(this is possible because if you don't check the
collation box for the
>article, then when the table is created on the
subscriber it picks up the
>collation of the subscriber's database.)
>Regards,
>Paul Ibison
>
>.
>
|||The default column collation is that of the database. In the database you can run sp_helpdb - eg sp_helpdb 'pubs'.
For the column, you run sp_help 'columnname' eg sp_help 'region'.
Regards,
Paul Ibison
|||I've had this a few times as well, if the collation idea fails, just change
some peice of the info and then immediatly change it back and it'll be
flagged for replication.
"Jonathan Lim" <jonathan@.jamminc.com.my> wrote in message
news:6B8034A7-B3E4-4135-B6D4-8438E7534DB1@.microsoft.com...
> Hi All,
> I have a curious problem. I have a replication to my branch that has been
running over 8 months. Everything works fine but once in a blue moon I get
this problem.
> Replication Agent reports all replication is successful.
> however, 1-2 rows of data in the branch data does not match with the
publisher ( we know this because some reports in the branch that pulls this
data do not tally)
> no matter how many time I re-run the merge.. the data will not be merge
down to the branch. The only way to get it to merge down to branch is to go
to EM select that rows of data and basically re-key in the same value and
save. Then the merge will pull the data down.
> Any ideas? like I say, this does not happen very often. I think only
2-3 times over the 8 months. I am not able to reproduce it mannually, I
tried many ways like inserting/updating/deleting rows during a merge.. etc
etc... .. but not able to reproduce the same problem.
> The replication is a standard merge replication. Server is SQL2000 SP2,
branch is MSDE 2000 SP3. Nothing fancy, just pulling the data down to
branch for some crystal reports.
> any ideas, or ways to check? The only thing I can think of is that we are
using some Chinese character... but then why only 1-2 rows.. and why not
consistancely happen...
> Jonathan

No comments:

Post a Comment