This was enough of a PITA that I'm going to blog it in hopes of keeping someone else from feeling my pain.

The goal: in Sql Server 2005, add a linked server that is the local indexing service [edit to add: I don't think it's possible to connect to a remote server] and then query using the OPENQUERY function.  It's actually pretty simple, but I was getting frustrated, so here we go.

In Management Studio, expand Server Objects-->Linked Servers.  Right click and select "new linked server".  It might also be handy to bring up the Indexing Service's snap-in control panel to reference some info.  It's on the Computer Management console under Administrative Tools, or just go Start-->Run--> ciadv.msc

  • In the dialog that comes up, fill in "Linked Server" textbox with any arbitrary name you want. 
  • Make sure the "Other data source" radio button is checked. 
  • Select "Microsoft OLE DB Provider for Indexing Service" (I just tab to the drop-down and hit the END key 'cause it's the last entry on the provider list).
  • Fill in the "Data Source" field with the name of the catalog from your index server.  Don't know the name? Look at the list in the indexer server control panel you opened. 

OK, and you're done adding the linked server.  Querying from the index is not what I was used to and finding information required a lot of Google-Fu.  Here's a simple query from a project of mine where the "Linked Server" name is 'KnowledgeDocs'.

SELECT * FROM OpenQuery(KnowledgeDocs, 'Select Directory, FileName, Size FROM SCOPE()')

You'll notice a thing called Scope() is being used as the 'table' here.  Scope provides functionality as a table, but also for further isolating which directory in your catalog you want to hit with the query.  Here's a slightly more complex query illustrating that:

SELECT * FROM OpenQuery(KnowledgeDocs, 'Select Attrib,Directory, FileName, Size FROM SCOPE(''DEEP TRAVERSAL OF "C:\Somewhere\1.1\Documents"'')')

You can see I've added some stuff inside the SCOPE function.  Well, just so it's clear, let me spell out what's in there.  First, because we're passing this query through as text, and since the SCOPE function wants a single quote, we're first escaping our single quote by doubling it.  Then come the string-within-a-string representing the clause/parameters telling the scope function to go through every sub-folder of a particular search location.

There are a lot of differences in the SQL language implementation for Indexing Service.  Here's the MSDN link (was hard for me to find!) on the query syntax:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/indexsrv/html/ixrefqls_3wj7.asp?frame=true

Here's an example of a slightly more complicated query:

SELECT * FROM OpenQuery(KnowledgeDocs, '
SET RANKMETHOD DICE COEFFICIENT
SELECT Rank, Directory, FileName, Size
FROM SCOPE(
''DEEP TRAVERSAL OF "C:\Somewhere\1.1\Documents"'',
''SHALLOW TRAVERSAL OF "C:\SomewhereElse\Documents"'')
WHERE CONTAINS(''CME'')
AND CONTAINS('' "gateway" NEAR() "network" '')
AND CONTAINS('' FORMSOF(INFLECTIONAL, "connect") '')AND FREETEXT(''install network gateway'')
AND ( FileName LIKE ''%.htm'' OR FileName LIKE ''%.doc'')
ORDER BY RANK DESC')

I hope his has been helpful...

Richard