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