PRB: Join Filter Causes Inconsistent Rows on SQL Server CE Subscriber (287651)

WORKAROUND

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next that contains this hotfix.

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 about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

To resolve this problem:

Obtain the QFE that is described in detail in the following Microsoft Knowledge Base article:
293761 FIX: Dynamic Filtered Tables Do Not Replicate Data
-and-
Use synchronization optimization. If you are using SQL Server Enterprise Manager to create your publication, toward the end of the Create Publication wizard, you will see the Minimize the amount of data sent over the network option. You cannot set the Minimize the amount of data sent over the network option for an existing publication. Therefore, you need to create a new publication in order to set this option. After you create a publication, the Minimize the amount of data sent over the network option appears in the Publication Properties or the Subscription Options as Minimize Network Traffic by increasing the storage requirements at the publisher.

If you use scripts to create publications, you need to define your publications with the keep_partition_changes option set to TRUE. For more information, refer to the "sp_addmergepublication" topic in SQL Server 2000 Books Online. The keep_partition_changes option specifies whether synchronization optimization occurs. The keep_partition_changes option is nvarchar(5), with a default of FALSE. A FALSE value means that synchronization is not optimized, and that the partitions sent to all Subscribers is verified when data changes in a partition. A TRUE value means that synchronization is optimized, and only Subscribers that have rows in the changed partitions are affected.

The keep_partition_changes option provides the ability to keep track of a row going out of or into a partition. For more information, refer to the "Optimizing Synchronization" topic in SQL Server 2000 Books Online.

MORE INFORMATION

Steps to Reproduce Behavior

Create a test database on a SQL Server 2000 Publisher.

Create two tables, named Customer and Orders, as follows:

Create table customer (CustId int PRIMARY KEY, State char(2))



Create table Orders   (OrderID int PRIMARY KEY, CustId int REFERENCES customer (CustId) )

Insert a few rows into each table, so that it looks similar to this:


CustId      State 
----------- ----- 
1           WA
2           OR
3           CA
4           NJ

(4 row(s) affected)

OrderID     CustId            OrderStatus
----------- -----------       -----------------
1           1		      Y
2           2		      Y
3           3		      Y

(3 row(s) affected)

Use SQL Server Enterprise Manager to create a Merge publication for a SQL Server CE Subscriber that contains two articles, Customer and Orders.

A uniqueIdentifier column is added to each article by the Replication Wizard but that is not shown in this example.

On the Filter Rows tab of the Publication Properties dialog box, define the following:

Orders - Orderstatus <> 'Y'
Customer - <All rows published>

Bottom grid :

Filtered Table = Orders
Table to Filter = Customer
JOIN Filter Clause = Customer.CustId = Orders.CustId

Generate a snapshot on a SQL Server 2000 computer, and then run a Microsoft eMbedded Visual Basic (eVB) Replication sample application on the CE device to download the replica.

Examine the tables on the CE subscriber:

CustId      State 
----------- ----- 
1           WA
2           OR
3           CA

OrderID     CustId              OrderStatus
----------- -----------       -----------------
1           1		            Y
2           2		            Y
3           3		            Y

On the Publisher, delete a row from the Orders table by using:
```
Delete from Orders where OrderId=3
```
Run the eVB application to synchronize from the CE device, and examine the tables on the CE Subscriber. Notice that one row was deleted from the Orders table. However, the corresponding row was NOT deleted from the Customer table:
```
CustId      State 
----------- ----- 
1           WA
2           OR
3           CA

OrderID     CustId               OrderStatus
----------- -----------       -----------------
1           1		           Y
2           2		           Y
```
Use an INSERT statement and add the deleted row back to the Orders table.
Apply the hotfix described in Q293761 - FIX: Dynamic Filtered Tables Do Not Replicate Data From Joined Tables to SQL Server CE Subscribers

NOTE: You must stop, and then restart the SQL Server service after you replace the DLL.
Drop and re-create the publication, and then set the Minimize the amount of data sent over the network option after you define the join filter in the wizard.
Repeat steps 6 and 7. Note that the deleted row is removed correctly on the CE subscriber.

PRB: Join Filter Causes Inconsistent Rows on SQL Server CE Subscriber (287651)

SYMPTOMS

CAUSE

WORKAROUND

MORE INFORMATION

Steps to Reproduce Behavior