SQL Server 2014 Fulltext search ON PDF Document

Fulltext search is the good one to search words on document

Find the below steps to configure Full Text Search.

·         First enable file stream
·         Enable full text search
·         Download and Install adobe I filter
·         Setting environmental variable
·         Restart Machine
·         Creating database and table
·         Create full text search
·         Start Full population
·         Insert docs into table
·         Retrieve data from document

First enable file stream:-

Start àms sql server  2014 à configuration tools à select sql server instances à propertiesàfilestreamà  enable below all

Enable filestream for T-sql access

Enable filestream for file I/O access

Allow remote clients to acess to file stream data.

 

 

Enable fulltext search

By default full text search will disabled in sql server configuration manager as per your requirement set start mode automatic and start.

Download and Install adobe I filter:-

Find the below link and download adobe ifilter from adobe..

http://www.adobe.com/support/downloads/detail.jsp?ftpID=5542

 

Setting environment variable:-

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

·         

·         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

For more information find the below URL

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/69535dbc-c7ef-402d-a347-d3d3e4860d72/sql-server-2008-64bit-fulltext-indexing-pdf-not-working-cant-find-ifilter?forum=sqldatabaseengine

Restart complete machine once

Create database and table :- If you want to continue with old database you can proceed, but here I’m creating new database. 

Create database :-

Crete database FT_TEST

Create Table:-

USE [FT_Test]

GO

/****** Object:  Table [dbo].[FTS]    Script Date: 6/17/2014 4:29:27 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FTS](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [DocNAme] [varchar](250) NULL,

       [Extention] [varchar](20) NULL,

       [Doc] [varbinary](max) NULL,

 CONSTRAINT [PK_FTS] PRIMARY KEY CLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Create Fulltext search:-

Defining fulltext search

After creation FT Click on Start Full papualtion

Now Insert Data into table

insert into fts( docname, extention, doc)

select '\\192.168.2.169\Share\eBook_Performance_Tuning_Davidson_Ford.pdf','.pdf', * from openrowset (bulk '\\192.168.2.169\Share\eBook_Performance_Tuning_Davidson_Ford.pdf',single_Blob) A

Retrieve data from document

select * from fts where contains (*,'simple')

in case if you'r not able to retrive data from pdf file follow below steps as well...

To install Windows TIFF IFilter with Windows Server 2008 R2

1. Click Start, click All Programs, click Administrative Tools, and then click Server Manager.
2. In the console tree of Server Manager, click Features, and then in Feature Summary, click Add Features.
3. Click Features, and then click Add Features.
4. Select the Windows TIFF IFilter check box, and then click Next.
5. Click Install.