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:
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/69535dbc-c7ef-402d-a347-d3d3e4860d72
If you do encounter this problem, good luck in making it work!
I have been pulling my hair with this same issue. no logs, no nothing but the pdf is not indexing. Have tried several times – same steps, but no luck. what could it be? I have another instance of SQL Server, it worked there, I restored the same database to my problematic instance of SQL Server and it worked **before** rebuilding. But when I rebuild the catalog after restore, it does not index again. So it has something to do with the fulltext search engine that does not index PDF files. All other files-doc, docx, xls are working fine. sys.fulltext_document_types has the pdf and correct path. I tried PDF x itself (this include ifilter) , PDF iFilter itself, and even FoxIt just to try if its the iFilter. But I think its not. It is the SQL Server fulltext search engine but where and how? Helpp???
@Carenski – this is a horrid problem so I sympathise with you.
I’ve never found any real logic behind how to fix this.
Whenever I’ve seen this problem, I’ve followed the steps through again and again. After enough reinstallations (and reboots), I can usually get it to work. It might be worth deleting the full text catalog and recreating it from scratch (rather than just rebuilding it).
I’ve encountered this problem on production SQL Servers that I’m not allowed to randomly restart so I know how much of a nightmare this can be.
This probably hasn’t helped you much so I only thing I can do is to wish you luck!
Same problem here. One server works fine, the other does not. SQL 2008 R2 with Adobe Reader 10.1 and iFilter 9. Reboots are real pain in production.