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

Article translations Article translations
Article ID: 300180 - View products that this article applies to.
This article was previously published under Q300180
BUG #: 352213 (SHILOH_BUGS)
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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:
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.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

Properties

Article ID: 300180 - Last Review: October 17, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB300180

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com