(SQL Server 2008 64-bit) Full-text indexing PDF not working - can't find iFilter

Question

Has anyone been able to full-text index PDF documents on 64-bit SQL Server 2008 using the Adobe 64-bit PDF iFilter?

We're getting the following error in the log for any row that has a PDF document:
Warning: No appropriate filter was found during full-text index population for table or indexed view '[TestDB1].[dbo].[Documents]' (table or indexed view ID '277576027', database ID '10'), full-text key value 'D448F8EC-EB6E-4658-B9D4-CE44E7DE6552'. Some columns of the row were not indexed.

Configuration:
OS: 64-bit Windows 2008 Server R2
SQL Server: 64-bit 2008 Developer Edition SP1 + Cumulative Update 3 (Build 10.0.2723)
PDF iFilter: Adobe PDF iFilter 9 for 64-bit platforms http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025

Note that this does work correctly on 32-bit platforms (e.g. Windows XP Pro) with SQL Server 2008 at the same maintenance level (Build 10.0.2723) using the iFilter provided with the Adobe Reader 9.1. So the problem is purely related to 64-bit.

The 64-bit PDF iFilter is properly installed and shows properly in sys.fulltext_document_types. The path shown in sys.fulltext_document_types is also correct.  I.e.:

document_type class_id path version manufacturer
.pdf E8978DA6-047F-4E3D-9C78-CDBE46041603 C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin\PDFFilter.dll 9.0.0.0 Adobe Systems, Inc.

There is a KB article (http://support.microsoft.com/kb/972650) that deals with an issue related to iFilters. However,
1. the KB doesn't mention Windows Server 2008 nor 32-bit/64-bit
2. The fix is in CU6 (pre-SP1) but not in CU3 (post-SP1)
3. The path that SQL Server shows to the iFilter is correct on both 32-bit and 64-bit version of my SQL Server 2008
4. I've confirmed that the problem doesn't exist on 32-bit OSs at CU3.

Answer 1

you'll need to use the foxit ifilter.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

you'll need to use the foxit ifilter.

Why not the Adobe 64-bit iFilter?  Can you provide more details.

Answer 3

http://blogs.msdn.com/opal/archive/2008/12/10/pdf-ifilter-battle-foxit-vs-adobe-64bit-version.aspx

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

That only shows some performance differences between the two vendors.  It does not provide any answers why SQL Server 2008 64-bit is having a problem finding the Adobe 64-bit iFilter.

As for using FoxIT, the number of PDF documents that we index is small.  So the performance (and price) issues are not applicable to our situation.

Answer 5

Found the fix! I.e., RTFM

After installing the PDF iFilter, it is recommended that you set your system PATH environment variable to the "bin" folder of the "Ifilter" installation.

For 64-bit SQL Server 2008, it is REQUIRED.

Related documents:
Adobe Installation Instructions:
http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025

(blog) Adobe PDF iFilter 9 for 64-bit Platforms now available
http://blogs.adobe.com/acrobat/2008/12/adobe_pdf_ifilter_9_for_64bit.html See Erik's posting February 9, 2009 5:05 AM and answer.

Answer 6

I'm also having problems with getting results from PDF blob files. However, I already have iFilter and the Environment Path is set. I also have .pdf listed under my FullText Document types.

There are no errors on my logs and I'm confident that it's not a problem with my search string.

I hope someone can give me a clue on what I might be doing wrong...

Answer 7

Hi there Ive done exactly as you've mentioned and I've even executed scripts that other people recommend but still I don't get any results back. would you perhaps have any advise for me ?

I found this somewhere and tried it but still nothing:

Ensure that the table does not have an index already (and if so, delete it)

Install Adobe iFilter

Execute the command exec sp_fulltext_service 'load_os_resources', 1;

Execute the command exec sp_fulltext_service 'verify_signature', 0;

Restart SQL Server

Verify PDF iFilter is installed

Create full-text index on table

Do full re-index

Although this did the trick, I'm quite sure I performed these steps a few times before it eventually started working properly.

Answer 8

I am also having the same issue. It is SQL Server 2008 32-bit though. The suggestion above tells me to set the path(even though it said for 64 bit, i still tried this out of desperation). There is no bin folder in adobe filter installation so i just used "C:\Program Files\Adobe\Reader 10.0\Reader\" to set the system PATH where AcroRdIF.dll is installed. (Note that I also tried FoxIt, the Adobe iFilter itself). They all behave the same. I can see the PDF in the doc type list. I am positive that there is a word that I am looking.

What else could it be? Please help. Thanks.

 

Answer 9

The PDF filter for Adobe 32-bit is automatically included in the Adobe Reader and SQL Server 2008 will find it if the option to load OS resources is set to true (EXECUTE sp_fulltext_service 'load_os_resources', 1;).  However, Adobe Reader 10.0 does not have the filter DLL!  You need to install Adobe Reader 10.1.x.

SQL Server should find it as per my system (system view sys.fulltext_document_types)

document_type               class_id                version manufacturer    path

.pdf        E8978DA6-047F-4E3D-9C78-CDBE46041603           10.1.0.534            Adobe Systems, Inc.       C:\Program Files\Adobe\Reader 10.0\Reader\AcroRdIF.dll

Answer 10

Yes, I do have these in my sys.fulltext_document_types (mine is the latest):

.pdf

 E8978DA6-047F-4E3D-9C78-CDBE46041603 

C:\Program Files\Adobe\Reader 10.0\Reader\AcroRdIF.dll 

10.1.1.33 Adobe Systems, Inc.

There is no log errors nor warnings. I have done more than 10 times (of restart, reinstalls, rebuild/create/delete catalogs), steps that Misi7 above told (and other googled results re: this issue - they are the same steps).  When I looked at the catalog, it shows 18 count which probably is correct because I have 18 records and only one table is in the catalog for the database - since I just wanted to do a proof of concept of using fulltext search in the document. All I know is it does not index pdf but work in other types.

I tried searching just using the regular search using Windows Explorer in my db server. It does not search pdf contents (just when i put the filename but not the contents). When I read the fulltext internals, it has its own index engine embedded in sql server engine so I am thinking that it is a separate engine/process so i dont think it is related but im just throwing it out. Out of desperation too, I installed Windows Search 4.0, and it does allow me to search content. But then again, they are separate engine.

Could it be something with the security? Based on this link http://msdn.microsoft.com/en-us/library/ms142595.aspx , there are reasons for failure:

Indexing failures can occur when:

• The indexer cannot find or load a filter or word breaker component. This failure can occur if the table row contains a document format or content in a language that has not been registered with the instance of SQL Server. This failure can also happen if the registered word breaker or filter component was not signed or failed signature verification when it was being loaded.

• A component, such as a word breaker or filter, fails and returns an error to the indexer. This can happen if the document being indexed is corrupt and the filter is unable to extract text from the document. This can also occur when a component is unable to handle the content of a single row above a certain size, due to memory limits on the full-text filter daemon host (fdhost.exe).

I think the first one - is ok. its english only and i issued exec sp_fulltext_service 'load_os_resources', 1; sp_fulltext_service 'verify_signature', 0; and even these two exec sp_fulltext_service 'update_languages'
exec sp_fulltext_service 'restart_all_fdhosts';
aside from restarting the full server.

The second might not be either because the same database restored to a different server works. :( help.

Thank you so much JSG080.

 

 

 

Answer 11

Re: The second might not be either because the same database restored to a different server works.

If what you said is true, "same database restored to a different server works", then the problem is environmental to the machine.  What version of the Adobe IFilter is used on the other machine?

All that said, I have not tested the Adobe Reader 10.1.x IFilter on 32-bit SQL Server machines.  We are running 64-bit in production which uses the special 64-bit IFilter which is based on Adobe 9.

I got burnt on my own 32-bit development machine when I installed Adobe Reader 10.0 -- which removed my 9.x version of the Reader/IFilter.  So I had nothing!  I did recently notice that the filter came back with 10.1 but I have not yet tested it.

I can say that the Adobe Reader 9 filters DO WORK -- both on 32-bit and 64-bit platforms.

So I'd suggest removing Adobe Reader 10 and re-installing Adobe Reader 9.x.

Answer 12

Thanks JSG080. I agree that it's with my server and Adobe iFilter. I will try your suggestion on reinstalling Adobe's lower version.  The other server I have is from my offshore team in india. So I will check with them what version they used. Great you asked the version, I didnt realize that :)

I do see just now that I have something in my log (I mentioned initially that i dont see any log). I didn't see it in SQL Server log view(via SSMS) but when I go to my C:\Program Files\Microsoft SQL Server\SQL2008\MSSQL10.SQL2008\MSSQL\Log\SQLFT0001100005.LOG, I saw this log when ever I repopulate or rebuild [So for those who says they dont see the log, here where its hidden] :

Warning: No appropriate filter was found during full-text index population for table or indexed view <tablename>' (table or indexed view ID '693577509', database ID '11'), full-text key value '12'. Some columns of the row were not indexed.

this repeats for every pdf files that i have. This tells me that the fulltext engine really do not work with the filter I installed. 

I will surely post whatever I can find. I have been working with this for weeks now(on and off) and I have been dedicating myself with this issue for 3 days (not the mention the wait times of reinstalling/restarting the servers...).  I didnt see anything in my "googling" yet for the solution, in my case at least (most of the solutions that worked is setting the environment PATH which probably not an issue with 32-bit nor a solution either). I do however keep gaining knowledge on fulltext search. This is the benefit of having issues, you are forced to read when the "default" thing wont work.

Thanks again. I really appreciate you taking time on this.

 

 

Answer 13

GOT IT WORKING FINALLY BUT USING iFilter 6.0 (this is for 32-bit server)

As promised, here are the steps:

Scenario: Adobe Reader X and SQL Server 2008 Enterprise 10.0.5500

0. Before doing anything, stop SQL Server and Full text Daemon Launcher services.
1. Uninstall adobe filter if you have one currently installed.
2. Delete catalog (might not work for those who have existing indexes. Probably, deleting the concerned table from the catalog will be ok - though for me I deleted the whole catalog because i am just testing the pdf fulltext search).

2. After uninstall of adobe filter, restart SQL Server and Fulltext Daemon Launcher. Run the following prior to reinstalling:
use master
go
EXEC sp_fulltext_service 'load_os_resources',1
go
EXEC sp_fulltext_service 'verify_signature', 0
go
reconfigure with override

3. Just to be sure that the old pdf is removed from full text, I issued this command:
select * from sys.fulltext_document_types where document_type='.pdf'
this time, you should not see any record returned because you unistalled it already.

4. I have downloaded from adobe.com the PDF IFilter 6.0 (Adobe Reader X didnt work for me after days of trying - even though the filter is integrated there - i have to use the stand alone iFilter verson 6-Thanks JSG080 for the Adobe version idea). Install it.

5. Restart SQL Server. Execute this again:
use master
go
EXEC sp_fulltext_service 'load_os_resources',1
go
EXEC sp_fulltext_service 'verify_signature', 0
go
reconfigure with override -- ( i saw this from some post but didnt bother to read more :) , might not be needed but i ran it anyway )

6. Verify PDF iFilter is installed :
select * from sys.fulltext_document_types where document_type='.pdf'
this time, you should see one record similar like this:
.pdf 4C904448-74A9-11D0-AF6E-00C04FD8DC02 C:\Program Files\Adobe\PDF IFilter 6.0\PDFFILT.dll 6.0.0.0 Adobe Systems Incorporated
If not, try to restart SQL server and fulltext launcher again. and try the query again.

7. Create full-text index on table. Do full re-index/rebuild just to be sure.

That's it. Hope this helps someone in the future.  I think its with the Adobe version. I will do the same exercise again with Adobe X. Just to reproduce and confirm.

Thanks for those who share ideas. You really never know when this will help someone. And it did for me.

 

 

 

Answer 14

Re: I have to use the stand alone iFilter verson 6

That version is way too old!.  You may have some problem indexing documents created under later versions of Adobe.  Adobe Reader 9 does work.  It also has the filter built-in as part of the Reader (for 32-bit platforms).

You can obtain Adobe Reader 9.4 from:

ftp://ftp.adobe.com/pub/adobe/reader/win/9.x/

ftp://ftp.adobe.com/pub/adobe/reader/win/9.x/9.4.0/

 

Answer 15

Hi JSG080. Thank you so much for sharing the link.

I was able to install this and get the PDF working using 9 version instead of 6.

Also, I retried the version Adobe X (10.1.1.33) using the same steps, it did not really work (using same steps that worked for 6 and 9 version).

 

 

 

 

Answer 16

Hi AsianIT.Did you need to config the environment variable Path after installing the PDF Reader 9.X?

Answer 17

I’ve always had to add this filter to the system PATH environment variable with any version of the filter. Give that a try...

 

Cheers,

Bob

 

"fsjohnhuang" wrote in message news:f3acc94c-fdac-485a-a3e9-0ea214b782c0...

Hi AsianIT.Did you need to config the environment variable Path after installing the PDF Reader 9.X?

Answer 18

hi Bob Beauchemin,Now I have installed the Reader 9.4, and executed the following commands:

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
exec sp_fulltext_service 'update_languages';
exec sp_fulltext_service 'restart_all_fdhosts'; 

but there is no ".pdf" record in sys.fulltext_document_types. I'm on 32-bit platform now.Couldn't u tell me why?

Answer 19

Thanks, I was having the same problem where PDF files were not being indexed.  I added the iFilter path and now it's working.