Microsoft KB Archive/220598

= PRB: SQL Distributed Query with MSIDXS Provider Needs Additional Quotes =

Article ID: 220598

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft OLE DB Provider for Index Server 2.0

-



This article was previously published under Q220598



SYMPTOMS
Performing the following distributed query with SQL Server 7.0 and Index Server 2.0 OLE DB Provider SELECT a.* FROM OpenRowset('MSIDXS','Web';; , 'Select Directory, FileName, size, Create, Write from SCOPE where CONTAINS(' ' "Index Server" NEAR "SQL Server" ' ')> 0   and FileName LIKE %.htm% ') as a gives an error similar to the following:

Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near ' "Index Server" NEAR "SQL Server" '.



CAUSE
There is an additional space character between the two single quotation mark characters inside the CONTAINS clause.



RESOLUTION
Remove any additional space characters between the two single quotation mark characters inside the CONTAINS clause. For example: SELECT a.* FROM OpenRowset('MSIDXS','Web';; , 'Select Directory, FileName, size, Create, Write from SCOPE where CONTAINS( "Index Server" NEAR "SQL Server" )> 0   and FileName LIKE %.htm% ') AS a



STATUS
This feature is by design.



MORE INFORMATION
Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers, which is done by using the OpenQuery or OpenRowset Transact-SQL function or by using a query with four-part names, including a linked-server name.

You need to install Index Server 2.0 from the Windows NT 4.0 Option Pack on a SQL Server 7.0 machine in order to perform a distributed query with SQL Server 7.0 and Index Server 2.0 OLE DB Provider.

When passing the same query to MSDIXS provider outside a SQL 7.0 distributed query (for example, in an ADO application), you must use one single quotation mark instead of two single quotation marks:

Select Directory, FileName, size, Create, Write from SCOPE where CONTAINS('"Index Server" NEAR "SQL Server"')> 0 and FileName LIKE '%.htm%'

