Thursday, September 12, 2013

Sqlserver Full Text Index , PDF population empty

Error: Warning: No appropriate filter was found during full-text index population for table

Replicate the problem
create table fts_pdf (id int not null, doctype varchar(4), content varbinary(max));

alter table fts_pdf add constraint pk_id_fts_pdf primary key (id);

INSERT INTO fts_pdf(id, doctype,
content)
   SELECT 2 as id, '.pdf' AS doctype,
       * FROM OPENROWSET(BULK N'D:\common\ebooks2\India-Year-Book-2009.pdf', SINGLE_BLOB) AS Document;
GO

select * from fts_pdf;
go

--create catalog

CREATE FULLTEXT CATALOG fts_pdf_content_catalog;
GO

--create index
create fulltext index on fts_pdf
(
content type column doctype language 1033
)
key index pk_id_fts_pdf    ON fts_pdf_content_catalog
WITH CHANGE_TRACKING AUTO
go

select * from fts_pdf where contains (
content, 'brought');
no result


Problem :- While population generation from any file(doc,pdf,txt) , sqlserver use filters to extract
the contents from the file. Filter can be checked by  the below query :

select * from sys.fulltext_document_types

Some of the filters are provided by the sqlserver and some of the filter are provided by windows.
".pdf" is one of the filter which not available while installation and even with windows.

Solution: =

We need to install ifilter provided by the adobe.
  • Download and install the ifilter
  • Set the environment variable to path of bin folder of ifilter
    • i.e. "path = c:/program files/adobe/****/***/bin/"

  • Now Open the Mssqlserver
  • connect with database
  • execute below commands:--
     EXEC sp_fulltext_service 'load_os_resources', 1
     EXEC sp_fulltext_service 'verify_signature', 0
     GO

  • Restart the mssqlserver and fulltext index services
     EXEC sp_fulltext_database 'enable'


No comments:

Post a Comment

web stats