Article ID: 311159 - View products that this article applies to.
This article was previously published under Q311159
When you execute a SQL query using the Microsoft Jet OLEDB Provider 4.0 and you apply a LIKE clause to a linked SQL Server table, the LIKE clause is not properly forwarded by the Microsoft Jet query processor to SQL Server. This results in a less efficient overall query, because all of the rows are first retrieved from SQL Server locally by Jet.
Note that after all rows are retrieved locally by Jet, the LIKE clause is applied internally to the final result before sending the result back to the client, so the final result is the same set of rows.
To resolve this problem, install the latest Microsoft Jet 4.0 service pack. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/239114/ )How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was fixed in Jet 4.0 Service Pack 6 (SP6).
Steps to Reproduce Behavior
If you run the identical SQL query using the Microsoft Access ODBC Driver, the LIKE clause is properly forwarded to SQL Server in the first SQL statement, resulting in a much more efficient overall query.
Article ID: 311159 - Last Review: October 12, 2005 - Revision: 1.4