FIX: sp_MSexpandnotbelongs May Be Called at Publisher Due to Invalid Evaluation of Join Filter

This article has been archived. It is offered "as is" and will no longer be updated.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
IMPORTANT: The service pack must be applied to each instance of SQL Server.
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

Article ID: 300180 - Last Review: 01/16/2015 22:50:51 - Revision: 4.0

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB300180