OLE DB Provider for Index Services

In the examples discussed in this chapter, OLE DB provider is supplied by Index Services.

The sp_addlinkedserver stored procedure may be used to register data sources that will be referenced in distributed queries. To register OLE DB Provider for Index Services for the Web text search catalog on the computer on which SQL Server is running, execute this statement:

EXECUTE sp_AddLinkedServer FileSystem, 

                           'Index Services', 

                           'MSIDXS', 

                           'Web'

Here are the definitions of the syntax terminology:

FileSystem

The linked_server_name assigned to this particular linked server.

Index Services

The product_name of the data source.

MSIDXS

The provider_name (PROGID) of OLE DB Provider for Index Services.

Web

The name of the text search catalog that will be used for this linked server.

The OLE DB provider now can be referenced using the FileSystem linked_server_name in the new OPENQUERY() result-set–valued function. For example:

SELECT *

FROM OpenQuery(FileSystem,

               'SELECT Directory, FileName, DocAuthor, Size, Create

                FROM SCOPE()

                WHERE CONTAINS( Contents, ''Distributed'' ) ' )

Notice that there are two SELECT statements. The inner SELECT statement (within the OPENQUERY() function) returns a result set as a table that can then be used like any other table in the FROM clause. In this case, the outer SELECT statement is a simple SELECT *, which passes on all the rows from the inner SELECT statement. Also notice that because the inner SELECT statement is specified as a constant parameter value within single quotation marks, all single quotation characters within the inner SELECT statement must be doubled. That is why ‘ ‘Distributed’ ‘ appears as it does. Two adjacent single quote marks (‘ ‘) are not the same as one double quotation mark (“).

In general, SQL Server distributed queries support both read and update access to the data source. In the case of Index Services, only read access is appropriate. Generally, distribution is supported to remote computers; however, the SQL Server file content search feature has been tested only with all components and all data residing in the same computer.

Notice that OPENQUERY() does not work when running with a compatibility mode earlier than SQL Server 7.0. The compatibility mode can be set using the sp_dbcmptlevel stored procedure.