Microsoft KB Archive/287651

= PRB: Join Filter Causes Inconsistent Rows on SQL Server CE Subscriber =

Article ID: 287651

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Windows CE Edition

-



This article was previously published under Q287651



SYMPTOMS
A join filter may cause inconsistent rows on a SQL Server CE Subscriber database. When you set up a row filter and a join filter on a Publisher to enforce a foreign key relationship between two articles (tables) in a publication, and you later (after initial synchronization) delete a row on the server, the resulting Subscriber table is different on the SQL Server Subscriber from the one on the SQL Server CE Subscriber.

You may also see anomalies on the Subscriber when a row is added to an article in the publication on the server side.



CAUSE
There is a problem with generations on the server side replication component, the Replprov.dll file.



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 &quot;sp_addmergepublication&quot; 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 &quot;Optimizing Synchronization&quot; topic in SQL Server 2000 Books Online.



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: <pre class="fixed_text"> 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) </li> 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.</li>  On the Filter Rows tab of the Publication Properties dialog box, define the following: <pre class="fixed_text">Orders - Orderstatus <> 'Y' Customer - <All rows published>

Bottom grid :

Filtered Table = Orders Table to Filter = Customer JOIN Filter Clause = Customer.CustId = Orders.CustId </li>  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: <pre class="fixed_text">CustId     State --- - 1          WA 2           OR 3           CA

OrderID    CustId              OrderStatus --- ---      - 1           1                   Y 2           2                   Y 3           3                   Y </li>  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: <pre class="fixed_text">CustId     State --- - 1          WA 2           OR 3           CA

OrderID    CustId               OrderStatus --- ---      - 1           1                  Y 2           2                  Y </li> Use an INSERT statement and add the deleted row back to the Orders table.</li> 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.</li> 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.</li> Repeat steps 6 and 7. Note that the deleted row is removed correctly on the CE subscriber.</li></ol>

Keywords: kbprb KB287651

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.