PRB: SQL Distributed Query with MSIDXS Provider Needs Additional Quotes (220598)

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" '.

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

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%'

REFERENCES

For more details on setting up and using distributed queries, search on sp_addlinkedserver, OpenQuery, OpenRowset, and related topics in SQL 7.0 Books Online.

For a discussion on OLEDB provider configuration for distributed queries, refer to the following topic in SQL 7.0 Books Online, "Configuring OLE DB Providers for Distributed Queries."

For more information on Index Server, refer to Index Server documentation in the Windows NT 4.0 Option Pack documentation.

For additional information about how to use the "NEAR" keyword for proximity searches, please see the following article in the Microsoft Knowledge Base:

198493 INFO: Using NEAR in SQL Full-Text and MSIDXS Distributed Queries