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" '.
There is an additional space character between the two single quotation mark characters inside the CONTAINS clause.
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
This feature is by design.
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%'
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=198493/EN-US/
)
INFO: Using NEAR in SQL Full-Text and MSIDXS Distributed Queries