sql server replication: Architecture : 1 Combined Publisher and Distributor. 4 anon (pull)
subscribers to a Merge publication. All SQL instances are SP3.

Details : Subscriber A and B get the same initial snapshot. Subscriber A
regularly synchronises, updates are applied. Server and subscriber A data
consistent. Subscriber B synchronises infrequently , thus has 'old' data that
Subscriber A has since updated and applied at the Publisher.

Problem : Subscriber B's 'old' data is being applied at the publisher and
then replicated to other subscribers. Thus Subscriber A is seeing data, that
he/she had changed, reverting back to previous values that B still had a copy
of.
It seems that the process is seeing 'old' data as 'new' data and then
applying it at the publisher.

Tried : I've looked at retention periods for the publication, distribution
database properties (transaction and history retention) in an attempt to make
it remember that B has 'old' data to no avail.

Question : How can this situation arise ? This 'old' data may NOT have been
altered by B in order for it to be re-applied. The only problems we have had
are errors like
"Agent is suspect. No response within the last XX minutes", but these have
been overcome by changing parameters for the profile (Query Timeout,
KeepAliveMessageInterval etc).

Any help would be much appreciated.

Jane,
can you double-check to see definitely if the data from B has been
modified - have a look in the msmerge_contents table and also use my script
for this purpose - http://www.replicationanswers.com/Script9.asp. If it has
changed, it could be winning a conflict.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Jane,
as far as I understand, the BOL is referring to the case where a subscriber
might not synchronize for ages then synchronizes which sends up loads of old
data. However this data that is uploaded is data which weas changed on the
subscriber some time ago. I haven't heard of data migrating when it hasn't
been changed, apart from an obscrure ring topology where data flowed in the
ring and a single change would continue to propagate, but this is different
to your case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Thanks for the reply Paul.

I had a quick look and there certainly seems to be a lot of data in there. I
shall run your script tomorrow and take a closer look.
Have you ever heard of this occurring where 'old' data is somehow seen as
'new' data, where the 'old' data may not have changed but the user?
I read in SQL Books Online, article "How Merge Replication Works", under
"Synchronization" the following ;
"If subscriptions do not synchronize with the Publisher within the
retention period, they are marked as 'expired' and will need to be
reinitialized. This is to prevent old Subscriber data from synchronizing and
uploading these changes to the Publisher."
This describes exactly what we are experiencing only it seems that somehow,
in our case, this checking has failed or it has applied the old data in any
case.

Thanks for your interpretation re : BOL, maybe it was wishful thinking that I
found a possible lead on this problem.
OK, I have ran your SP on one of the articles and there were some 3000 rows
returned as Ins/Upd.
I have tried to recreate the problem but it seems to be behaving i.e. if the
subscription has expired we must reinitialize and logs do not show any data
changes uploaded as it did before.
So since reinitialising I have not made any data changes so what do these
3000 changes refer to (certainly not changes that I have made) ?

Jane - I don't know :)
I have seen incorrect data returned from my routine immediately after
initialization but once a proper synchronization took place, it returned
correct results. Perhaps this is the case? Try changing one row then
synchronize and see if the routing returns a correct count. Before this,
have a look at one of the GUID values and examine the relevant record to
check that the data hasn't changed at all.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Recreated the publication and obtained a new snapshot.

Ran the Script and again returned 3142 records.
Changed one record, ran the script and it returned some 6283 records
although I think that there were 2 related records
in sysmergesubscriptions i.e. the sysmergesubscriptions.srvid column (values
of 0 or 1).
So if records are being duplicated then the original value is more or less
the same.
There are 181204 records in total in msmerge_contents!
**Should this many records still be in msmerge_contents even though
synchronisation has been successful ?**

I also noticed that the msmergecontents.generation on the client was 71 when
I got the initial snapshot
but in replinfo.sentgen values are null, 1, 7 and 30 (which would cause
those records to be returned in your query).
The record I changed had generation=7.
**Could this be the problem when it eventually gets to 71 it will apply
those records with generation=71 in msmerge_contents to the server?**

Thanks for your help, Paul. I shall continue to investigate. Your script has
also helped to identify how the tables relate to each other (i.e. join
columns).
Although because there is so much data its hard to get your head around

Just Applied another update and there is a line in replinfo that has a
generation value of 73 which is higher than the initial snapshot value of 71.
So I'm not sure what's going on.
Interestingly there is an entry in sysmergesubscriptions for a subscription
that no longer exists. I wonder if that could confuse the replication