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

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%'

4. 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

5. If you can search few documents but not all, then you should check catalog population.

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