SQL SERVER – Full-Text Search Not Working For PDF Documents

SQL SERVER - Full-Text Search Not Working For PDF Documents search Full-Text is a feature of SQL Server which allows linguistic search in various languages. In general, we use LIKE operator to search inside the column data, but it is not very efficient. There are multiple advantages of full-text search. You can read more about this on my blog Creating Full-Text Catalog and Full-Text Search

Let’s say you have stored PDF document in the database using VARBINARY(MAX) column and now we need to search some text. To search in a binary column, we need to use the appropriate filter.

One of my clients has contacted me as they were not able to use full-text for PDF documents.

I tested in my lab and there are many steps needed to make it work and I am putting all steps together as a quick checklist.

  1. Make sure Adobe PDF iFilter is installed. While writing this blog, here is the latest version download link. Adobe PDF iFilter 11
  2. Make sure that PATH in environment variables is set to the bin folder where you have installed iFilter in the previous step. By default, the path should be “C:\Program Files\Adobe\Adobe PDF iFilter 11 for 64-bit platforms\bin\”. If you have selected custom path, then we need to provide a path to the bin folder.
  3. Check the path of iFilter in SQL Server’s catalog view also.
SELECT *
FROM sys.fulltext_document_types
WHERE document_type LIKE '%pdf%'
  1. Sometimes, we need to run below command so that iFilter is loaded correctly.
EXEC sp_fulltext_service 'load_os_resources', 1  
GO
EXEC sp_fulltext_service 'verify_signature', 0
GO
  1. If you can search few documents but not all, then you should check catalog population.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – What is Change Pending State of in SQL Server Configuration Manager?
Next Post
SQL SERVER – Patch Rule Failure: Not Clustered or the Cluster Service is Up and Online

Related Posts

2 Comments. Leave new

  • Has anyone got the pdf iFilter working in Sql Server 2017? Works fine for me in 2014, but in 2017 it errors out with

    Error ‘0x8004fd02: The filter daemon MSFTEFD failed to load an IFilter interface for document, so it can’t be indexed.

    PDF shows up in the list of filters, but errors out when rebuilding the index. Any ideas?

    Reply
  • we did, still searching for solutions.
    Also i dont see any entries in System Variables for this pdf iFilter. It would be nice if Dave elaborated on #2.

    Reply

Leave a Reply

Menu