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

Merge Replication Doesn't Update IDENT_CURRENT() ?

I have found that a table that is being merge-replicated to each branch
office has a different INDENT_CURRENT value in each branch. Is this
correct?
Basically, I have a table called "Batch" in two database servers (one
for each branch office). Let's call the database servers dbsOne, and
dbsTwo. "Batch" table has an identity field that is not the primary
key and is not the replication ID either. Somehow we find that
IDENT_CURRENT() in dbsOne is different from that's in dbsTwo, like
this:
IDENT_CURRENT("Batch") in dbsOne: 1073
IDENT_CURRENT("Batch") in dbsTwo: 943
When I check the identity values of "Batch" entries that the users have
added in dbsOne, the identity values is closely following
IDENT_CURRENT("Batch") in dbsOne.
Likewise, when I check the identity values of "Batch" entries that the
users have added in dbsTwo, the identity values is closely following
IDENT_CURRENT("Batch") in dbsTwo.
Seem like the merge replication doesn't update IDENT_CURRENT()
accordingly.
Is my understanding correct?
Thanks.
Jay Chan
yes, this is perfectly normal. Basically it means that on one subscriber the
merge agent has run more frequently than on the other.
Hilary Cotter
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
<jaykchan@.hotmail.com> wrote in message
news:1120749290.480181.201600@.g49g2000cwa.googlegr oups.com...
> I have found that a table that is being merge-replicated to each branch
> office has a different INDENT_CURRENT value in each branch. Is this
> correct?
> Basically, I have a table called "Batch" in two database servers (one
> for each branch office). Let's call the database servers dbsOne, and
> dbsTwo. "Batch" table has an identity field that is not the primary
> key and is not the replication ID either. Somehow we find that
> IDENT_CURRENT() in dbsOne is different from that's in dbsTwo, like
> this:
> IDENT_CURRENT("Batch") in dbsOne: 1073
> IDENT_CURRENT("Batch") in dbsTwo: 943
> When I check the identity values of "Batch" entries that the users have
> added in dbsOne, the identity values is closely following
> IDENT_CURRENT("Batch") in dbsOne.
> Likewise, when I check the identity values of "Batch" entries that the
> users have added in dbsTwo, the identity values is closely following
> IDENT_CURRENT("Batch") in dbsTwo.
> Seem like the merge replication doesn't update IDENT_CURRENT()
> accordingly.
> Is my understanding correct?
> Thanks.
> Jay Chan
>
|||Thanks for telling me that this is normal. I will have to keep this in
mind and not using the identity field for synchronizing data between
branch offices and only use it to ID an entry generated within the
branch office. OK.
I don't quite understand what do you mean by "one subscriber the merge
agent has run more frequently than on the other". My database servers
situation is actually like this:
o I have three database servers:
1. dbsOne (the publisher)
2. dbsTwo (The subscriber # 1)
3. dbsThree (The subscriber # 2)
o The INDENT_CURRENT("Batch") value in each database server are:
1. dbsOne: 10734
2. dbsTwo: 9841
3. dbsThree: 9289
o The estimated volume of data added to "Batch" table from
each branch office into their database server are:
1. dbsOne: High
2. dbsTwo: Medium
3. dbsThree: Low
I assume what you mean is that the value in IDENT_CURRENT in each
database server is corresponding to the number of entries that we add
to the table in the database server. This seems to match the numbers
that I get from our database servers. Thanks.
Jay Chan
Hilary Cotter wrote:[vbcol=seagreen]
> yes, this is perfectly normal. Basically it means that on one subscriber the
> merge agent has run more frequently than on the other.
> --
> Hilary Cotter
> 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
> <jaykchan@.hotmail.com> wrote in message
> news:1120749290.480181.201600@.g49g2000cwa.googlegr oups.com...