The full text indexing service in SQL Server allows PDF files to be indexed and allows you to perform full text searches against the contents of PDF files stored in binary fields. The prerequisite for making this work is the installation of Adobe PDF iFilter.
Unfortunately, I’ve found this to be really buggy and have spent the best part of half a day trying to get this to work against the a 64bit version of SQL 2008 R2. Everything appears to install properly but when you actually attempt to search for some PDFs, none are returned. For anyone also experiencing this problem, here’s some advice which might help you:
01. After installing Adobe iFilter, run the following commands:
exec sp_fulltext_service 'load_os_resources', 1; exec sp_fulltext_service 'verify_signature', 0;
02. Add the iFilter.dll folder location into the Windows path. By default, this is set to the following:
C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin
If using the ‘Environment Variables’ dialog in ‘System Properties’ , make sure to edit the path variable within ‘System variables’ and not the path variable for the user (if one exists).
03. PDF searching may work but most likely, it won’t. Try restarting the SQL Server Service and if this fails, reboot the server.
04. If this still doesn’t work, try deleting and recreating the full text indexes.
05. If this still doesn’t work, uninstall iFilter and repeat from step 1 until it does work. In my case, the PDF searching began working for me after uninstalling and reinstalling, despite every setting being identical the second time round.
If you encounter this problem, other tips that might be of use to you are:
01. Try performing searches against the contents of other PDF files as it may be the PDF content that’s at fault and not the iFilter
02. When the iFilter is installed properly, PDF files on the file system will be searchable using the search in Windows. It’s worth investigating that this works properly before delving in too deep into SQL Server.
03. In SQL Server, it’s worth running the following SELECT and making sure that a row relating to ‘PDF’ is returned. This row should contain the correct file location that corresponds to where PDFFilter.dll is installed on the server.
SELECT * FROM sys.fulltext_document_types
Here’s a thread which I found to be useful:
If you do encounter this problem, good luck in making it work!