Search code examples
sql-serverfull-text-searchadobesql-server-2014

Full-text search for pdf files on SQL Server doesn't return results


I've installed Adobe IFilter changed the path and create full-text index by running queries below

CREATE TABLE ResearchReport
(
   ResearchReportID int identity(1,1),
   Report varbinary(max),
   Title nvarchar(100) not null,
   Summary nvarchar(300) not null,
   Author nvarchar(150) not null,
   Country nvarchar(50) not null,
   Sector nvarchar(50) not null,
   Company nvarchar(50),
   Extension nvarchar(10) NOT NULL,
   CreationDate smalldatetime default getdate(),

   primary key(ResearchReportID)
);

EXEC sp_fulltext_database 'enable'
GO

IF NOT EXISTS (SELECT TOP 1 1 FROM sys.fulltext_catalogs WHERE name = 'Report_Catalog')
BEGIN
    EXEC sp_fulltext_catalog 'Report_Catalog', 'create';
END

DECLARE @indexName nvarchar(255) = (SELECT Top 1 i.Name from sys.indexes i
                                    Join sys.tables t on  i.object_id = t.object_id
                                    WHERE t.Name = 'ResearchReport' AND i.type_desc = 'CLUSTERED')

PRINT @indexName

EXEC sp_fulltext_table 'ResearchReport', 'create', 'Report_Catalog',  @indexName
EXEC sp_fulltext_column 'ResearchReport', 'Report', 'add', 0, 'Extension'
EXEC sp_fulltext_table 'ResearchReport', 'activate'
EXEC sp_fulltext_catalog 'Report_Catalog', 'start_full'

ALTER FULLTEXT INDEX ON [dbo].ResearchReport ENABLE
ALTER FULLTEXT INDEX ON [dbo].ResearchReport SET CHANGE_TRACKING = AUTO

EXEC sp_fulltext_service @action='load_os_resources', @value=1; -- update os resources 
EXEC sp_fulltext_service 'verify_signature', 0 -- don't verify signatures 
EXEC sp_fulltext_service 'update_languages'; -- update language list 
EXEC sp_fulltext_service 'restart_all_fdhosts'; -- restart daemon 
EXEC sp_help_fulltext_system_components 'filter'; -- view active filters
reconfigure with override

select * from ResearchReport query returns results but when I want to do full text search with one of the queries below I get no results, what could be the problem? I've checked most of the questions on stackoverflow and google but couldn't find the answer.

SELECT r.* 
FROM dbo.ResearchReport r
WHERE Contains(r.Report, 'a')

SELECT * 
FROM ResearchReport 
WHERE freetext(Report, 'a')

Solution

  • Turns out Ifilter version 11 is not really tested. When i removed 11 and installed 9, then recreated index it worked perfectly.