Microsoft KB Archive/300180

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

Article ID: 300180

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q300180



BUG #: 352213 (SHILOH_BUGS)



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.

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB300180

-

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

© Microsoft Corporation. All rights reserved.