title: Using full-text search with PDF files in SQL Server 2005
body: <p>I've got a strange problem with indexing PDF files in SQL Server 2005, and hope someone can help.  My database has a table called MediaFile with the following fields - MediaFileId int identity pk, FileContent image, and FileExtension varchar(5). I've got my web application storing file contents in this table with no problems, and am able to use full-text searching on doc, xls, etc with no problems - the only file extension not working is PDF.  When performing full-text searches on this table for words which I know exist inside of PDF files saved in the table, these files are not returned in the search results.</p>
  
  <p>The OS is Windows Server 2003 SP2, and I've installed <a href="http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611" rel="noreferrer">Adobe iFilter 6.0</a>.  Following the instructions on <a href="http://weblogs.asp.net/wallym/archive/2005/02/28/382060.aspx" rel="noreferrer">this blog entry</a>, I executed the following commands:</p>
  
  <pre><code>exec sp_fulltext_service 'load_os_resources', 1;
  exec sp_fulltext_service 'verify_signature', 0;
  </code></pre>
  
  <p>After this, I restarted the SQL Server, and verified that the iFilter for the PDF extensions is installed correctly by executing the following command:</p>
  
  <pre><code>select document_type, path from sys.fulltext_document_types where document_type = '.pdf' 
  </code></pre>
  
  <p>This returns the following information, which looks correct:</p>
  
  <blockquote>
    <p>document_type: .pdf<br/>
    path: C:\Program Files\Adobe\PDF IFilter 6.0\PDFFILT.dll</p>
  </blockquote>
  
  <p>Then I (re)created the index on the MediaFile table, selecting FileContent as the column to index and the FileExtension as its type.  The wizard creates the index and completes successfully.  To test, I'm performing a search like this:</p>
  
  <pre><code>SELECT MediaFileId, FileExtension FROM MediaFile WHERE CONTAINS(*, '"house"');
  </code></pre>
  
  <p>This returns DOC files which contain this term, but not any PDF files, although I know that there are definitely PDF files in the table which contain the word <em>house</em>.</p>
  
  <p>Incidentally, I got this working once for a few minutes, where the search above returned the correct PDF files, but then it just stopped working again for no apparent reason.</p>
  
  <p>Any ideas as to what could be stopping SQL Server 2005 from indexing PDF's, even though Adobe iFilter is installed and appears to be loaded?</p>