PRB: SQL Distributed Query with MSIDXS Provider Needs Additional Quotes

ID: Q220598

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft OLE DB Provider for Index Server, version 2.0

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

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:

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

Additional query words: kbDSupport


Keywords          : kbDatabase kbIdxServ kbSQLServ700 kbGrpVCDB 
Version           : WINDOWS:2.0; winnt:7.0
Platform          : WINDOWS winnt 
Issue type        : kbprb

Last Reviewed: April 30, 1999