Microsoft KB Archive/198493

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

Article ID: 198493

Article Last Modified on 3/2/2005

-

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

-



This article was previously published under Q198493



SUMMARY
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.



MORE INFORMATION
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

