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

ID: Q198493

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft OLE DB Provider for Index Server, version 2.0
Microsoft Data Access Components version 2.1 SP2

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

REFERENCES

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:

Q200862 INFO: Update to Index Server OLEDB Provider CONTAINS Clause

Additional query words:

Keywords : kbDatabase kbOLEDB kbOLEDB200 kbSQLServ700
Version : WINDOWS:2.0,2.1 SP2; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbinfo