Merge Replication Synchronization Issue

Hello,
I have a merge replication problem has been driving me nuts for the last couple of days and I haven't been able to find any information on it from other posts in this group. The problem is that extra uploads (as UPDATEs) are being sent to the publisher w
hen the subscriber sync's.
First off our setup info:
Server:
- SQL Server 2000 Enterprise (SP3).
- Running on a clustered Win 2000 server (Active/Passive).
- Publisher and Distributor are located on the same virtual Sql Server instance.
- Merge publication using dynamic horizontal data filters with join filters off of filtered tables.
- Dynamic snapshots used for each subscribers data initialization.
Client: MSDE (SP3)
- Running Win XP Tablet Edition.
- Anonymous on demand subscription.
Table Info:
Primary Table: Retailers (PK ApplicationNumber char(7))
Sub Table: RetailerInformation (PK ApplicationNumber char(7), which is also a FK to Retailers.ApplicationNumber)
Replication Info Table: ReplicationRetailers (PK ApplicationNumber char(7), UserLogin varchar(20) (This is the windows login returned by the SUSER_SNAME() function during the filtering during the sync))
Publication Info:
ReplicationRetailers table has a dynamic filter of 'WHERE UserLogin = SUSER_SNAME()'
ReplicationRetailers Join Filters Retailers on ApplicationNumber
Retailers Join Filters RetailerInformation on ApplicationNumber
Our replication is working great; the correct data is being sent down to the subscribers database, speeds are excellent, etc... However we noticed a strange behavior while testing yesterday. If I assign a new retailer to a user (by adding a row to the Re
plicationRetailers table) the first sync down to the subscription works fine; all the applicable Retailer records are INSERTed into the subscriber. However, when the subscriber sync's again, an almost identical number of UPDATEs are sent back up to the s
erver. The number of UPDATEs never quite equals the number of INSERTs but is usually 2-4 less. The data that gets UPDATEd up to the publisher is not different from the data that was INSERTed to the subscriber and I know that there are no subscriber tabl
e triggers firing that update any data.
I've traced the merge process to see what tables are sending data back up to the publisher and confirmed that it is the Retailers table and its associated sub table that sends the updates. However I cannot tell exactly which records are being sent.
I know this is not the normal behavior during a sync as I have other tables that have an identical table filter that only send data when there are changes (in the same publication). So I guess I have a couple questions:
1) Has anyone else seen this behavior?
2) Is so, what did you do to fix it?
3) Is there some way to use the MSmerge_contents and MSmerge_genhistory tables to figure out what rows are going to be sent durning the next sync BEFORE the sync occurs?
Thank you very much for your help.
Wesley Brown
Each row in a merge published table has a GUID column which is used to uniquely identify each row.
Each row in MSmerge_contents corresponds to changes which have happened locally on the database.
Each row in MSmerge_contents will contain an generation number.
When the merge agent runs compares the generation numbers in in MSmerge_contents and the msmerge_replinfo table between the publisher and subscriebr to determine which GUID's have incremented their generation number.
Then depending on whether the publisher or subscriber has the higher generation number a stored procedure is constructed with parameters based on the values in either the publisher or subscriber published tables and executed on the subscriber or publisher
|||Thanks for the response Hilary.
I've used the information you gave me and created a query to show me the state of the subscriber and the publisher after
the 1st sync. Interestingly I noticed that there are no rows in the publishers MSmerge_contents table for some of the
replicated data. I think the problem relates to the fact that most of the records in our database have never been
replicated (which makes sense given that we are using filters and have only been testing with a couple user logins). When
a database is turned into a publisher none of the existing rows are added to the MSmerge_contents table. When the sync
occurs the data gets added to the subscribers MSmerge_contents table but NOT the publishers MSmerge_contents table. I
think this may be a bug as all the required information is known when this sync is being performed. When the next Sync is
started the publisher correctly determines that the subscriber has new rows that it needs to upload for the
MSmerge_contents table. As for why the inserts to the publisher show up as updates I’m not sure; it could be that the
publisher adds the rows itself at the start of the 2nd sync and then checks the values it has for the rowguid column against
those in the subscriber database, however I’m really not sure.
Does anyone know if this behavior (the lazy load into the MSmerge_contents table) is by design?
sql