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

ID: Q241678

The information in this article applies to:

Microsoft SQL Server versions 7.0, 7.0 Service Pack 1

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.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   Date      Time     Version    Size     File name      Platform
   --------------------------------------------------------------

   9/17/99   1:04pm   7.00.731   484 KB   replmerg.sql   intel
   9/17/99   1:04pm   7.00.731   484 KB   replmerg.sql   alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

NOTE: If this product was already installed on your computer when you purchased it from the Original Equipment Manufacturer (OEM) and you need this fix, please call the Pay Per Incident number listed on the above Web site. If you contact Microsoft to obtain this fix, and if it is determined that you only require the fix you requested, no fee will be charged. However, if you request additional technical support, and if your no-charge technical support period has expired, or if you are not eligible for standard no-charge technical support, you may be charged a non-refundable fee.

For more information about eligibility for no-charge technical support, see the following article in the Microsoft Knowledge Base:

Q154871 Determining If You Are Eligible for No-Charge Technical Support

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0

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:

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.

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 will successfully apply the filter to the DIRECT_DEPOSIT table and move the detailed row to the subscriber.

Additional query words:

Keywords : kbSQLServ700bug kbSQLServ700sp1bug
Version : winnt:7.0,7.0 Service Pack 1
Platform : winnt
Issue type : kbbug