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.
- Make sure Adobe PDF iFilter is installed. While writing this blog, here is the latest version download link. Adobe PDF iFilter 11
- 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.
- Check the path of iFilter in SQL Server’s catalog view also.
SELECT * FROM sys.fulltext_document_types WHERE document_type LIKE '%pdf%'
- 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
- If you can search few documents but not all, then you should check catalog population.
Reference: Pinal Dave (https://blog.sqlauthority.com)