Microsoft KB Archive/324362

= Subqueries that you define on merge articles are not re-evaluated =

Article ID: 324362

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q324362



SYMPTOMS
When you use subqueries to filter data in a merge publication, the subquery is not re-evaluated after the initial synchronization, which may generate unexpected results at the Subscriber.



CAUSE
This behavior occurs because the query is not re-evaluated and the row is not propagated as part of replication when you update a row in a table that is referenced by a subquery. Although you can place a subquery in a row filter, it is not a join filter. Although you can define a subquery that is based on data from another table, this too can cause unexpected results at the Subscriber.



RESOLUTION
To resolve this behavior, use a join filter when you want the filter definition to be re-evaluated during every merge synchronization process.



STATUS
This behavior is by design.



MORE INFORMATION
A fundamental design goal for merge replication is to partition the data in such a way that each Subscriber receives the smallest data set possible. One technique to implement this is to define subset filter clauses. With a subset filter clause, which is an article property, you can define a logical expression. You can use this logical expression in the WHERE clause of queries to evaluate data that is to be replicated to the subscribing tables. You can also include a subquery in the subset filter clauses.

For example, a Customers table at the Publisher contains customer data for several states, and an Orders table tracks product orders. Consider the following initial data at the Publisher: Customers:

CustomerID State 1      WA 2       TX 3       WA

Orders:

OrderID    CustomerID 1      1 2       1       3       3 4       2 The business logic of the application requires that the Publisher only sent orders that belong to customers from the state of Washington (WA) to the Subscribers. Also, the Customers table is not filtered. To implement this logic, you can define the following subset filter clause that uses a subquery on the Orders table: exec sp_addmergearticle @publication = N'Northwind', @article = N'Orders', ... @subset_filterclause = N'orders.customerid in ( select customerid from customers where state = wa )', ...

exec sp_addmergearticle   @publication = N'Northwind', @article = N'Customers', ... @subset_filterclause = null, ...

Note The subquery in the preceding filter statements is the following SQL statement: select customerid from customers where state = wa Although you can successfully specify the subset filter clause, this violates the requirement for row filters to refer only to columns and to values in that table.

When this publication is synchronized the initial synchronization process honors the preceding subquery and therefore the following data is sent to the Subscriber: Customers:

CustomerID State 1      WA 2       TX 3       WA

Orders:

OrderID    CustomerID 1      1 2       1 3       3 For example, by making the following update at the Publisher update customers set state = 'VA' where customerID = 1 this results in the following data at the Publisher: Customers:

CustomerID State 1      VA 2       TX 3       WA

Orders:

OrderID    CustomerID 1      1 2       1       3       3 4       2 Start the merge synchronization process to generate the following data results at the Subscriber: Customers:

CustomerID State 1      VA 2       TX 3       WA

Orders:

OrderID    CustomerID 1      1 2       1       3       3 The Orders table still includes orderID 1 and 2, which no longer belong to customers from Washington State. This is because the subquery that you defined on the Orders table is not re-evaluated. To avoid this problem, the Customers table must be filtered on the state column and you must definite a join filter clause on the Customers and Orders tables. For example: exec sp_addmergearticle @publication = N'Northwind', @article = N'Customers', ... @subset_filterclause = N'state = WA', ...

exec sp_addmergearticle   @publication = N'Northwind', @article = N'exec sp_addmergearticle @publication = N'Northwind', @article = N'Orders', ... @subset_filterclause = null, ...

exec sp_addmergefilter @publication = N'Northwind', @article = N'orders', @filtername = N'orders_customers', @join_articlename = N'customers', @join_filterclause = N'customers.customerid = orders.customerid', @join_unique_key = 0 GO Note In this example, you must filter the Customers table.

