PRB: LIKE Predicates Are Not Remoted to SQL Server 7.0 After You Install SQL Server 2000 Service Pack 3 (814115)

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

SYMPTOMS

After you upgrade to SQL Server 2000 Service Pack 3 (SP3), SQL Server no longer remotes the execution of predicates that involve a parameterized LIKE clause when the target server is a SQL Server 7.0 server. This can lead to longer execution times for distributed queries after the SP3 upgrade. The following query demonstrates this behavior, where SQL7SERVER is the SQL Server 7.0 target server:

set statistics profile on
go

declare @customerID varchar(25)
select @customerID = 'VINET%'

select O.OrderID
from   [northwind].[dbo].[order details] OD JOIN
       [SQL7SERVER].[northwind].[dbo].[orders] O ON OD.OrderID = O.OrderID JOIN
       [SQL7SERVER].[northwind].[dbo].[customers] C ON O.CustomerID = C.CustomerID
where  O.[customerID] LIKE @customerID

The execution plan generated by this query (a truncated example is shown later in the article) indicates that the LIKE predicate in the query is not remoted to the SQL Server 7.0 target server. Instead, all of the result set is returned from the linked server, and then subsequently filtered on the local SQL Server 2000 server:

|--Nested Loops
     |--Hash Match
     |    |--Filter(WHERE:(like([SQL7SERVER].[northwind].[dbo].[orders].[CustomerID], Convert([@customerID]), NULL)))
     |    |    |--Remote Query(SOURCE:(SQL7SERVER), QUERY:(SELECT O."CustomerID" Col1012,O."OrderID" Col1011 ...
     |    |--Remote Query
     |--Index Seek

CAUSE

Before SQL Server 2000 SP3, SQL Server was incorrectly remoting non-ANSI LIKE predicates to remote servers. SQL Server provides its own extensions to the ANSI-standard LIKE clause that allows for such features as using regular expressions in a LIKE clause. Because not all providers can handle these Transact-SQL extensions, a fix was introduced in SQL Server 2000 Service Pack 3 that prevents the remoting of non-ANSI LIKE predicates to remote servers for evaluation. This fix of the LIKE remoting behavior between SQL Server 2000 Service Pack 2 and Service Pack 3 introduces a regression that causes SQL Server to stop remoting these predicates to SQL Server 7.0 target servers.

WORKAROUND

To correct the remoting of and associated performance problems introduced by this change for SQL Server 7.0 target servers, you can use the new registry key that is available in SQL Server 2000 Service Pack 3 that will allow the remoting of non-ANSI LIKE predicates to remote servers. To add this registry key, follow these steps:

Click Start, and then click Run. In the Run dialog box, type " REGEDT32" (without the quotation marks), and then click OK.
Open the following key in Registry Editor:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB
On the Edit menu, click "New", and then select "DWORD Value".
Type SQLServerLIKE for the name of the new value.
Double-click this value in Registry Editor, and then change its value to 1.

Note that this flag is also valid for non-SQLOLEDB providers, but it indicates that the provider supports all of the SQL Server specific extensions to the LIKE operator. Setting this flag for providers that do not support these extensions can cause unexpected behavior.

PRB: LIKE Predicates Are Not Remoted to SQL Server 7.0 After You Install SQL Server 2000 Service Pack 3 (814115)

SYMPTOMS

CAUSE

WORKAROUND

STATUS