I'm trying to get iFilter to work, I'm running Windows 10 64-bit with MS SQL Server 2017/2016 Developer version. I've downloaded the 64-bit version of iFilter 11 from Adobe website and I followed their guide here. Even though it's for MS SQL 2008/2012 it's still pretty easy to follow.
My problem is that the full-text search doesn't return any results.
I installed iFilter 11 64-bit and added it to the system PATH. I confirmed that this is working by restarting the pc and finding the DLL from cmd.
After that, because I didn't have the FullText search component of MSSQL Server, I installed it and started the SQL Full-text Filter Daemon Launcher (I also restarted the main MSSQL Server instance).
I already had a DB and apparently FullText search are enabled by default now so I continued with the installation.
I loaded the components and verified that everything is recognized by running the following queries:
GO
PRINT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
exec sp_fulltext_service 'load_os_resources', 1
exec sp_fulltext_service 'verify_signature', 0
/* Verify if iFilter is installed */
SELECT * FROM sys.fulltext_document_types WHERE document_type = '.pdf'
GO
/* Restart MSSQL before continuing */
/* Enable fulltext search on the database */
Use [db]
GO
EXEC sp_fulltext_database 'enable'
GO
Use [db]
DROP TABLE pdfifiltertable
GO
CREATE TABLE pdfifiltertable(
PdfID INT IDENTITY NOT NULL,
PdfFileName VARCHAR(MAX),
Ext VARCHAR(10),
PdfText VARBINARY(MAX),
CONSTRAINT PK_PdfID PRIMARY KEY (PdfID)
)
GO
CREATE FULLTEXT CATALOG pdfCatalog AS DEFAULT
GO
CREATE FULLTEXT INDEX ON pdfifiltertable([PdfText] Type column [Ext] LANGUAGE 'French'
) KEY INDEX PK_PdfID with change_tracking auto
GO
/* Check if the table correctly have a fulltext_index */
SELECT distinct
object_name(fic.[object_id]) table_name,
[name] column_name
FROM
sys.fulltext_index_columns fic
INNER JOIN sys.columns c
ON c.[object_id] = fic.[object_id]
AND c.[column_id] = fic.[column_id]
GO
/* Try to search (doesn't work) */
SELECT PdfFileName
FROM [db].[dbo].[pdfifiltertable]
WHERE CONTAINS(PdfText, 'Adobe')
With the queries I was able to:
- Verify that the FullText Search component is installed
- I was able to load the different filters and verify that iFilter was being loaded.
- I have also made sure that fulltext is enabled on the database even though it is on by default.
- I've created a table and a catalog and I specified the most complex language that it will be used for (French).
- After creating my table and my catalog I ran a query to verify that the PdfText column had a text index and it did.
Following Adobe own guide, I created their sample WinForm application to upload PDFs. I uploaded 2 PDF, one in French, the other in English. I then searched for simple words that should be contained in them but no results were returned.
I have tried rebuilding the Catalog and rebuilding all index for my pdf table but it didn't change the result.
I tried Installing iFilter 9 by downloading the file PDFiFilter64installer.zip from Adobe ftp in case the newest version had any problem. Unfortunately, it doesn't work either. I have ran a profiler on the MSSQL server and no error is being thrown.
I have searched and gathered multiple related questions which helped me build my complete troubleshooting. I will link them so that they are easier to search together:
SQL Server : full-text pdf search results using contains and ifilters - Not answered
Using full-text search with PDF files in SQL Server 2008 - No working answer for MSSQL 2017.
Using full-text search with PDF files in SQL Server 2005 - Not working for MSSQL 2017 either.
I'm thinking that maybe iFilter just doesn't work on newer OS and newer version of MSSQL.
