SQL Server full text indexing using Adobe PDF iFilter 9, SQL Server 2008 R2 64bit

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

image

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!

About these ads

About dotnettim

Tim Leung is a Microsoft .Net / SQL Server developer based in England.
This entry was posted in SQL 2008 R2. Bookmark the permalink.

3 Responses to SQL Server full text indexing using Adobe PDF iFilter 9, SQL Server 2008 R2 64bit

  1. Carenski says:

    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???

  2. dotnettim says:

    @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!

  3. Jon Arancio says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s