FIX: Replication Merge JOIN Filters May not be Completely Evaluated when a Filtered Table is Modified (241678)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 7.0 Service Pack 1
This article was previously published under Q241678
BUG #: 56460 (SQLBUG_70)

SYMPTOMS

By design initial synchronization of existing data rows, on the publisher, that do not meet established filter criteria are not moved to the subscriber. However, some modification sequences making the filter criteria valid for a subscriber, after synchronization, can lead to only partial data merge on the subscriber.

Merge replication keeps track of changes to rows using a system of GUIDs and change track tables. Any modification immediately ties the associated ROW GUID to a merge replication tracking table. The behavior of this issue is such that any modified row(s) will be merged but subsequent rows meeting the filter criteria that where not modified, are not merged. Any modification contained in the tracking table is properly evaluated during the merge process.

CAUSE

A temporary table is used to track merge filters requiring evaluation as a result of a given modification. This table did not contain the information necessary to identify the subsequent filters requiring evaluation.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

The issue corrected by the fix pertains to filter and sub filter qualifications. The merger replication process allows the user to establish an elaborate system of filter operations to segment data for given subscribers. It is possible to have a sequence of modifications made at a publishing site extending qualification of filter criteria that did not exist at initial synchronization time.

For example the STORES table is a primary key table containing basic store information such as location. The EMPLOYEE table is a foreign key table to STORES establishing a work relationship for the EMPLOYEE. Finally, you have a DIRECT_DEPOSIT table as a foreign key reference for each EMPLOYEE.

The replication filters are partitioned in a manner so each store pulls its own employee data. During initial synchronization Bob works for STORE ID #100. STORE #100 gets the proper data for Bob.

A brand new store, STORE #200, is being built and Bob is going to transfer because it is closer to his house. The sequence of conditions to encounter this issue would be:
  1. Store #200 sets up the subscribing server but the publisher contains NO STORE #200 data. The initial synchronization results in the creation of empty tables.
  2. On the publisher the new STORE ID #200 is added AND the EMPLOYEE record for Bob is updated.
This results in a situation where the filter for DIRECT_DEPOSIT is valid to move to STORE #200 but no official modification to the table took place. Without the correction for this issue the new row in STORES is moved to the subscriber as well as the EMPLOYEE row update. These updates where specifically tracked in the merge tracking tables.

The correction successfully applies the filter to the DIRECT_DEPOSIT table and move the detailed row to the subscriber.
Modification Type:MajorLast Reviewed:3/14/2006
Keywords:kbBug kbfix KB241678
©2004 Microsoft Corporation. All rights reserved.