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..
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
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
Click Start, click All Programs, click Administrative Tools, and then click Server Manager.
In the console tree of Server Manager, click Features, and then in Feature Summary, click Add Features.
Click Features, and then click Add Features.
Select the Windows TIFF IFilter check box, and then click Next.
Click Install.
