Article ID: 300180 - View products that this article applies to.
This article was previously published under Q300180
BUG #: 352213 (SHILOH_BUGS)
In merge replication, if the column name in the join filter is similar to one that can qualify as a function, like USERID or USER_ID, it may cause expansion of deletes (done by sp_MSexpandnotbelongs) at the publisher. This may affect performance to some extent because another call (which should not be made) is made to the publisher.
When setting up merge replication, join filters can be specified for various reasons, such as minimizing the amount of data sent over the network, reducing the amount of storage space required at the subscriber, and many others. The stored procedures sp_MSsetupbelongs, sp_MSbelongs, sp_MSexpandbelongs, and sp_MSexpandnotbelongs are used to determine the set of rows that need to be published to the subscriber based on the filter specified. These filters can also be made dynamic by specifying a system function or a user-defined function that is evaluated differently for each subscriber. This also improves performance because the subscriber receives only the information needed based on the connection properties of the merge agent for the subscription.
To determine whether a filter is dynamic, a check is made internally using a LIKE clause that looks for patterns. However, under certain conditions, there may be columns with names like USERID, USER_ID, and so on, that are used in the filter. In this case, the check incorrectly evaluates the filter to be dynamic, interpreting USERID or USER_ID column as a USER_ID() function. This leads to an expansion of deletes by sp_MSexpandnotbelongs at the publisher. Expansion of deletes is normally done at the subscriber, but with functions in join filters it is done at the publisher, because those functions are supposed to be evaluated in the context of the publisher.
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211IMPORTANT: The service pack must be applied to each instance of SQL Server.
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How to Obtain the Latest SQL Server 2000 Service Pack
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.