title: Full-text search using Windows Search Service and SQL Server 2008 R2
body: <p>Currently I'm trying to query the Windows Search Service from a SQL Server 2008 R2 instance (also tested on SQL Server 2012) . Windows Search is being exposed as an OLE DB datasource, giving me several options to query the search index. When configuring a new Linked Server in SQL Server, Management Studio gives me the option to select the Microsoft OLE DB Provider for Search, implying that I should be able to connect to it from SQL Server. It turns out to be a challenge to get this up and running however. Below you'll find the error message I stumbled upon.</p>
  
  <blockquote>
    <p>OLE DB provider "Search.CollatorDSO" for linked server "TESTSERVER" returned message "Command was not prepared.".<br>
    Msg 7399, Level 16, State 1, Line 2<br>
    The OLE DB provider "Search.CollatorDSO" for linked server "TESTSERVER" reported an error. Command was not prepared.<br>
    Msg 7350, Level 16, State 2, Line 2<br>
    Cannot get the column information from OLE DB provider "Search.CollatorDSO" for linked server "TESTSERVER".</p>
  </blockquote>
  
  <p>Things get even more interesting. Although the Linked Server solution isn't working, I'm able to wrap code that queries Windows Search in a CLR Function (using <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/bb266517(v=vs.85).aspx" rel="nofollow noreferrer">MSDN: Querying the Index Programmatically</a>) and use if <em>successfully</em> within SQL Server. This is however less desirable, because of the steps needed to set it up (deploying the library, configuring permissions, etc.). I've tried several parameter settings, without any luck. I've also tried enabling some of the Search.CollatorDSO provider options, like allowing the provider to be instantiated as an in-process server. I'm currently using the settings below. For security I'm using the login's current security context.</p>
  
  <ul>
  <li><strong>Provider:</strong> <code>Microsoft OLE DB Provider for Search</code></li>
  <li><strong>Data source:</strong> <code>(local)</code></li>
  <li><strong>Provider string:</strong> <code>Provider=Search.CollatorDSO.1;EXTENDED?PROPERTIES="Application=Windows"</code></li>
  <li><strong>Location:</strong> <code>-</code></li>
  </ul>
  
  <p>Additionally I need to search network drives, can this be done using shared Windows libraries?</p>
  
  <p>I'm aware more people have been struggling with this problem over the last few years. I'm wondering if someone has been able to get this up and running, or could point me in the right direction. </p>
  
  <h2>OLEDB Works</h2>
  
  <p>Normal ADO/OLEDB components can query the Windows Search service with the connection string:</p>
  
  <pre><code>provider=Search.CollatorDSO.1;EXTENDED PROPERTIES="Application=Windows"
  </code></pre>
  
  <p>And an example query:</p>
  
  <pre><code>SELECT TOP 100000 "System.ItemName",
      "System.ItemNameDisplay",
      "System.ItemType",
      "System.ItemTypeText",
      "System.Search.EntryID",
      "System.Search.GatherTime",
      "System.Search.HitCount",
      "System.Search.Store",
      "System.ItemUrl",
      "System.Filename",
      "System.FileExtension",
      "System.ItemFolderPathDisplay",
      "System.ItemPathDisplay",
      "System.DateModified",
      "System.ContentType",
      "System.ApplicationName",
      "System.KindText",
      "System.ParsingName",
      "System.SFGAOFlags",
      "System.Size",
      "System.ThumbnailCacheId"
  FROM "SystemIndex"
  WHERE CONTAINS(*,'"Contoso*"',1033)
  </code></pre>
  
  <p>You can try the query directly on SQL Server in SQL Server Management Studio by attempting to run:</p>
  
  <pre><code>SELECT *
  FROM OPENROWSET(
          'Search.CollatorDSO', 
          'Application=Windows', 
          'SELECT TOP 100 "System.ItemName", "System.FileName" FROM SystemIndex');
  </code></pre>
  
  <p>Which gives the errors:</p>
  
  <pre><code>OLE DB provider "Search.CollatorDSO" for linked server "(null)" returned message "Command was not prepared.".  
  
  Msg 7399, Level 16, State 1, Line 1  
  The OLE DB provider "Search.CollatorDSO" for linked server "(null)" reported an error. Command was not prepared.  
  Msg 7350, Level 16, State 2, Line 1  
  Cannot get the column information from OLE DB provider "Search.CollatorDSO" for linked server "(null)".  
  </code></pre>
  
  <h2>Bonus Reading</h2>
  
  <ul>
  <li><a href="https://social.msdn.microsoft.com/Forums/en-US/d8922482-bd05-4b04-9f7a-0e173b36804e/connect-to-windows-search-from-sql-server-using-linked-server?forum=searchserverdevelopersandcustomization" rel="nofollow noreferrer">Connect to Windows Search from SQL Server using Linked Server</a> <em>(January 2011)</em></li>
  <li><a href="https://social.msdn.microsoft.com/Forums/en-US/c460515c-2446-465d-bd30-92d632579b39/link-with-sql-server?forum=windowsdesktopsearchdevelopment" rel="nofollow noreferrer">Link with SQL Server</a> <em>(May 2008 - December 2012)</em></li>
  <li><a href="http://codebetter.com/kylebaley/2008/07/07/trying-to-access-windows-search-from-sql-server-an-appeal/" rel="nofollow noreferrer">Trying to access Windows Search from SQL Server: An Appeal</a> <em>(July 2007)</em></li>
  <li><a href="http://www.nesterovsky-bros.com/weblog/2011/03/04/CallingWindowsSearchFromSQLServer2008.aspx" rel="nofollow noreferrer">Calling Windows Search from SQL Server 2008</a> <em>(March 2011)</em></li>
  <li><a href="https://stackoverflow.com/questions/22957281/ole-db-provider-search-collatordso-returns-command-was-not-prepared">OLE DB provider &quot;Search.CollatorDSO&quot; returns &quot;Command was not prepared&quot;</a> <em>(April 2014 - Suggests CLR workaround)</em></li>
  <li><a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/19e6c7bd-1755-4aaf-9fae-30ac0a784369/linked-server-to-windows-search?forum=sqldataaccess" rel="nofollow noreferrer">Linked Server to Windows Search</a> <em>(February 2011)</em></li>
  <li><a href="https://blogs.msdn.microsoft.com/sqlserverfaq/2009/08/27/query-to-the-systemindex-to-read-the-microsoft-search-results-fails-when-using-search-collatordso-provider/" rel="nofollow noreferrer">MSDN Blogs: Query to the SYSTEMINDEX to read the Microsoft search results fails when using Search.CollatorDSO provider</a> <em>(<a href="http://archive.is/pRgaK" rel="nofollow noreferrer">August 2009</a> - suggests CLR workaround)</em></li>
  <li><a href="http://www.bigresource.com/MS_SQL-Vista-Search-57XoGBze.html" rel="nofollow noreferrer">Vista Search</a> <em>(<a href="http://archive.is/wRabf" rel="nofollow noreferrer">February 2007</a>)</em></li>
  </ul>