|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 198493 - Last Review: March 2, 2005 - Revision: 2.2 INFO: Using NEAR in SQL Full-Text and MSIDXS Distributed QueriesThis article was previously published under Q198493
When performing a full-text search against SQL Server character data, the
"NEAR" keyword should be used for proximity searches. Parentheses after the
keyword NEAR are optional.
Products previous to SQL Server 7 that use the SQL Full Text extensions,
such as the Index Server OLE DB provider, require that the keyword be used
with the parentheses, as in NEAR().
When using a SQL Distributed query with OLE DB Provider for Microsoft Index
Server provider version 2.0 to search for file system data, you must use
NEAR() (that is, with parentheses).
As an alternative, the tilde, "~", can be used as shorthand to specify a
proximity search.
Microsoft SQL Server version 7.0 provides the ability to perform full-text
query on character data stored in SQL Server tables. The NEAR keyword is
used to specify a proximity search (match of words or phrases that must be
close to one another), for example:
SELECT ProductID, ProductName FROM Products
WHERE CONTAINS(ProductName,'spread NEAR Boysenberry')
For SQL Server full-text queries, the preferred syntax is NEAR without
parentheses. However "NEAR()" will also work.
However, when using a SQL Distributed query with the Microsoft OLE DB
Provider for Index Server version 2.0 to search for file system data, the
parentheses are required, for example:
SELECT a.*
FROM OpenRowset('MSIDXS','Web';''; '','Select Directory, FileName, size,
Create, Write from SCOPE()
where CONTAINS(''"Index Server" NEAR() "SQL Server"'')
and FileName LIKE ''%.htm%'' ') AS a
Using NEAR without the parentheses in this case will cause a syntax error.
SELECT a.*
FROM OpenRowset('MSIDXS','Web';''; '','Select Directory, FileName, size,
Create, Write from SCOPE()
where CONTAINS(''"Index Server" NEAR "SQL Server"'')
and FileName LIKE ''%.htm%'' ') AS a
will give an error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSIDXS'.
[OLE/DB provider returned message: The command contained one or more
errors]
[OLE/DB provider returned message: The command contained one or more
errors]
[OLE/DB provider returned message: Incorrect syntax near "SQL Server".
Expected '('. SQLSTATE=42000]
As an alternative, the tilde, "~", can be used as shorthand to specify a
proximity search with both Distributed and Full-text queries, as follows:
SELECT ProductID, ProductName FROM Products
WHERE CONTAINS(ProductName,'spread ~ Boysenberry')
SELECT a.*
FROM OpenRowset('MSIDXS','Web';''; '', 'Select Directory, FileName,
size,
Create, Write from SCOPE()
where CONTAINS(''"Index Server" ~ "SQL Server"'')
and FileName LIKE ''%.htm%'' ') AS a
For more details on SQL 7.0 full-text search, refer to SQL 7.0 Books
Online.
For more information on the use of NEAR in CONTAINS clause with MSDIXS
provider, refer to the Index Server documentation in the Windows NT 4.0
Option Pack documentation.
For additional information on the CONTAINS clause, click the article number below
to view the article in the Microsoft Knowledge Base:
200862Â
(http://kbalertz.com/Feedback.aspx?kbNumber=200862/EN-US/
)
INFO: Update to Index Server OLEDB Provider CONTAINS Clause
APPLIES TO- Microsoft SQL Server 7.0 Standard Edition
- Microsoft OLE DB Provider for Index Server 2.0
- Microsoft Data Access Components 2.1 Service Pack 2
| kbdatabase kbinfo KB198493 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |