FIX: SQL Server 7.0 Service Pack 2 May Fail to Delegate the Filter Condition to the Provider (276225)

SYMPTOMS

SQL Server 7.0 Service Pack 2 (SP2) may fail to delegate the filter condition (where clause) to Remote Query onto a heterogeneous remote server when the the queried column is of character data type. This can occur even when the server option is set to "collation compatible". This is a regression introduced by Service Pack 2.

For example, the following query may generate a less efficient plan while using SQL Server 7.0 SP2:

select * from RUMBATEST.S10186DR.QGPL.MSTEST WHERE MSCUNO = 'abc'

Example query plan:

  |--Filter(WHERE:([RUMBATEST].[S10186DR].[QGPL].[MSTEST].[MSCUNO]='abc'))
       |--Remote Query(SELECT Remote1001.MSCUNO Col1002,Remote1001.MSNAME Col1003 FROM S10186DR.QGPL.MSTEST Remote1001)

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

SQL Server can delegate comparisons and ORDER BY operations on character columns to a provider; however, restrictions such as collation compatibility apply. For more information, see "Optimizing Distributed Queries" in SQL Server Books Online.

For additional information on using numeric data types of linked servers, click the article number below to view the article in the Microsoft Knowledge Base:

197456 INF: Optimizing Distributed Query with Numeric Predicates