Search keyword in Documents stored in file system from SQL Server

Hi,

Can you please help me in searching keywords with in the documents stored in file system from SQL Server.

I do remember that I used to do this using full text by using the path of the document, file name and keyword in single query. I could not get the exact way.

Thanks in advance.
LVL 14
Jagdish DevakuSr DB ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StolsieCommented:
Hi
must be possible with MS and its indexing.
if you have a Linux client you can use you can try "
was going to type it out but here this will at least explain it :) "
i'll have a scout in windows, I have a feeling you might need to enable advanced searching and indexing if on a server but that will add load.
Jagdish DevakuSr DB ArchitectAuthor Commented:
Hi Stolsie,

Thanks for the response.

We are using windows & sql server.

And I want to search a string with in the document from sql query.

Thanks.
StolsieCommented:
ahh sorry I read it as you wanted to search a document for a specific line.. oops...
What I know about SQL is dangerous, I'll bow out, sorry.
I'll leave you with this click me :)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Raju SrivatsavayeSoftware EngineerCommented:
I vaguely remember something like this a while back.

https://www.mssqltips.com/sqlservertip/2769/sql-server-semantic-search-to-find-text-in-external-files/

Hope it helps
Jagdish DevakuSr DB ArchitectAuthor Commented:
@srivatsavaye - I already checked this.

But looks like I don't have any option than to try this.

I will keep you posted once I try this.

Thanks.
Jagdish DevakuSr DB ArchitectAuthor Commented:
I forgot that I am using SQL Server 2008 R2.

I think  Semantic Search will work from SQL Server 2012.
Raju SrivatsavayeSoftware EngineerCommented:
Bummer!! I didn't realize you are using 2008 R2. I don't think i can help you with this. Sorry.

Good luck.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to use the FULL TEXT search feature.
Jagdish DevakuSr DB ArchitectAuthor Commented:
Hi vitor,

Documents are stored in the file system and not in the database. Can you please help me.

Thanks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you please help me in searching keywords with in the documents stored in file system from SQL Server.
This means you're using FILESTREAM?
If so then you'll just need to create a fulltext index on the column that stores the document in the table and then use the FULL TEXT search functions. For example:
SELECT *
FROM  TableName
WHERE  CONTAINS(filestream_ColName,'my test')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jagdish DevakuSr DB ArchitectAuthor Commented:
Hi Vitor,

I am on a vacation and could not respond.

Documents are stored in file system and not in the column.

Is there a way to do the below.

Loading the documents from file system to the tables using query and then index the column as you said above.

Thanks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Jagdish,

Sorry to get back to this question just now.

Did you read the link I post in my previous comment. From there you can jump to other pages that has everything you need.
Anyway here's a good article with examples how to work with FILESTREAM.
Jagdish DevakuSr DB ArchitectAuthor Commented:
Thanks Vitor.

I am going through it.
Jagdish DevakuSr DB ArchitectAuthor Commented:
Finally I have done it by enabling FileStream and created a database with file Stream datafile.

 Created a table & added a full text on the table.

Thanks all for your help.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm very glad to hear that.
Cheers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.