Textual Searches against File-System Data

It is worth noting that the ability to search against file-system data only already exists in Indexing Services 2.0. This capability is further enhanced by the SQL Server 7.0 Distributed Query Processor, which, in conjunction with the Microsoft OLE DB Provider for Indexing Services 2.0, can be used to issue both property and full-text searches against file-system data and to join the results with data in the database.

By recasting the first example in this chapter, we will introduce searches against the file system to obtain some of the data from the file system. The following query selects the names, sizes, and authors of all Microsoft Word files on drive D, where the document contains the phrase “SQL Server” in proximity to the word “text” and joins this result with the writers table to obtain the author’s citizenship.

SELECT Q.FileName, Q.Size, Q.DocAuthor, W.Citizenship 

FROM OpenQuery(MyLinkedServer, 

               'SELECT FileName, Size, DocAuthor

                from SCOPE('' "D:\" '')

                WHERE CONTAINS(''"SQL Server"

                                 NEAR() text'')

                AND FileName LIKE ''%.doc%'' '

              ) as Q,

     writers as W

WHERE Q.DocAuthor = W.writer_name

The location of the data must be known to the person writing the queries. The SQL standards organization is developing a new datalink data type, which will allow seamless queries that access data both inside and outside the database. This emerging definition offers some interesting possibilities.