January 23, 2012 at 4:34 pm
Hello all:
I have a table created to store word documents, where one of the fields is a Varbinary(Max) in which I am storing the actual word document. I am able to insert the document fine.
However, when I try to search within this field using CONTAINS for a word that I know is in it, it returns no results.
(I did setup the full text catalog, performed a full population on the table too)
Any thoughts where I am missing it?
Thank you
January 23, 2012 at 4:38 pm
It is SQL 2008
and this is what I can find from the logs:
No appropriate filter was found during full-text index population for table or indexed view '...'(table or indexed view ID '2137058649', database ID '8'), full-text key value '1'. Some columns of the row were not indexed.
January 23, 2012 at 5:52 pm
You need a separate 'type' column to tell FTS what sort of document is in the binary data. See http://msdn.microsoft.com/en-us/library/ms142499.aspx and the TYPE COLUMN clause of the CREATE FULLTEXT INDEX statement (http://msdn.microsoft.com/en-us/library/ms187317.aspx)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 23, 2012 at 6:10 pm
ok, thanks. ( I already have it )
I found my problem, I needed to search the complete word to get the result.
Here's what I mean:
SELECT *
FROM SermonList
WHERE CONTAINS(DocumentContent, ' "hebrews" ');
I get a result.
But for below, I DO NOT get any result back
SELECT *
FROM SermonList
WHERE CONTAINS(DocumentContent, ' "hebrew" ');
Why is it so (I must be missing something)
January 23, 2012 at 6:29 pm
mymail.default (1/23/2012)
But for below, I DO NOT get any result backSELECT *
FROM SermonList
WHERE CONTAINS(DocumentContent, ' "hebrew" ');
Why is it so (I must be missing something)
See CONTAINS (http://msdn.microsoft.com/en-us/library/ms187787.aspx) in Books Online...this should work:
SELECT *
FROM SermonList
WHERE CONTAINS(DocumentContent, ' "hebrew*" ');
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 24, 2012 at 9:43 am
Ok, thanks, Paul
January 24, 2012 at 4:19 pm
Hello again,
Little twist here:
I removed all the entries in the table, and re-inserted the same document, and re-populated the indexes.
But, the search now does not yield results!
Anything I am missing?
Thanks again.
January 24, 2012 at 4:43 pm
Note:
I added another document with a .doc extension, and I was able to see the search results.
So, the problem is when I add the document as .docx, the search does not yield results.
Do I need a separate filter for .docx documents other than .doc?
Thanks,
Paul
January 24, 2012 at 5:17 pm
mymail.default (1/24/2012)
Note:I added another document with a .doc extension, and I was able to see the search results.
So, the problem is when I add the document as .docx, the search does not yield results.
Do I need a separate filter for .docx documents other than .doc?
Thanks,
Paul
It would appear you'll need a separate column, it doesn't appear that you can apply multiple types to the same Full Text Index. .docx is definately different than .doc for formatting information.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 5:43 pm
Thanks for your input.
But, I am able to search fine on my local machine (with SQL 2008 32bit), not so on the server (64bit).
I did install the filters too!
January 24, 2012 at 5:50 pm
mymail.default (1/24/2012)
Thanks for your input.But, I am able to search fine on my local machine (with SQL 2008 32bit), not so on the server (64bit).
I did install the filters too!
Hm, I must have misread something in the articles surrounding this. I'll go re-read them a few times and see what I can test out. Sorry about that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 5:54 pm
mymail.default (1/24/2012)
Thanks for your input.But, I am able to search fine on my local machine (with SQL 2008 32bit), not so on the server (64bit).
I did install the filters too!
Check sys.fulltext_document_types and:
EXECUTE
sys.sp_help_fulltext_system_components
@component_type = N'filter';
You may need to load the .docx filter from the 2007 Office System Converter: Microsoft Filter Pack.
Otherwise, this is something that is going to be a lot easier for you to debug for yourself using the documentation and comparison with the 2008 instance you have working.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 25, 2012 at 6:04 pm
Thanks for your input, Paul.
Still scratching on this one.
I did install/load the right filters, however it(.docX) is not showing up in the list when I ran: SELECT * FROM sys.fulltext_document_types
The weird thing, though, is I have the same filter loaded on my local workstation, and it does not show up in the listing either, BUT, I am able to get results fine when I search in .docx documents.
January 25, 2012 at 6:09 pm
I would pay particular attention to whether the filter is 64-bit or 32-bit.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 25, 2012 at 6:19 pm
Thanks,
Yes, it is a 64-bit that I installed (for a 64-bit SQL).
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply