Full Text Index Issue

Hey all,
I'm using full text indexing for keyword search capabilities for one of our
clients'. We needed to provide boolean search capability and hence I chose
containstable instead of freetext.
The stored proc works fine except that for specific keywords the client
expects certain documents to show which do not. This is really random and we
have not been able to find out why these documents don't show inspite of the
keyword being the the document name and summary. We also made sure the
document exists in the indexed table.
We did not want to re-populate the catalog because when we did it in the
past the client was able to find 5 documents and 10 others were not showing
up.
We even tried defragmenting the drive SQL server sits on, which did not
help. Has anyone experienced this before?
Any help will be greatly appreciated.
-Aarti.
a defrag won't help. Are there any messages in the event log from MSSCI or
MSSearch?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aarti." <Aarti@.discussions.microsoft.com> wrote in message
news:73F8C6ED-2ACE-438E-B341-9DEE30D122AC@.microsoft.com...
> Hey all,
> I'm using full text indexing for keyword search capabilities for one of
our
> clients'. We needed to provide boolean search capability and hence I chose
> containstable instead of freetext.
> The stored proc works fine except that for specific keywords the client
> expects certain documents to show which do not. This is really random and
we
> have not been able to find out why these documents don't show inspite of
the
> keyword being the the document name and summary. We also made sure the
> document exists in the indexed table.
> We did not want to re-populate the catalog because when we did it in the
> past the client was able to find 5 documents and 10 others were not
showing
> up.
> We even tried defragmenting the drive SQL server sits on, which did not
> help. Has anyone experienced this before?
> Any help will be greatly appreciated.
> -Aarti.
|||Thanks for your response.
I deleted and recreated the FT catalog. Here's the list of messages in the
event log:
MS Search Gatherer : The end of crawl for project <SQLServer SQL0000600005>
has been detected. The Gatherer successfully processed 10373 documents
totaling 0K. It failed to filter 2936 documents. 0 URLs could not be reached
or were denied access.
SQLFTHNDLR: One or more documents stored in image columns with extension
'doc' did not get full-text indexed because loading the filter failed with
error '0x1'.
Note: These documents will not be passed to MSSearch for indexing, and
therefore this failure will not be reflected in the end of crawl summary
statistics.
SQLFTHNDLR: One or more documents stored in image columns with extension
'vb' did not get full-text indexed because loading the filter failed with
error '0x1'.
Note: These documents will not be passed to MSSearch for indexing, and
therefore this failure will not be reflected in the end of crawl summary
statistics.
SQLFTHNDLR: One or more documents stored in image columns with extension
'wpd' did not get full-text indexed because loading the filter failed with
error '0x1'.
Note: These documents will not be passed to MSSearch for indexing, and
therefore this failure will not be reflected in the end of crawl summary
statistics.
MS Search Gatherer : One or more warnings or errors for Gatherer project
<SQLServer SQL0000600005> were logged to file <d:\Microsoft SQL
Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000060 0005.1.gthr>. If you are
interested in these messages, please, look at the file using the gatherer log
query object (gthrlog.vbs, log viewer web page).
Please let me know if you need any more information.
Thanks in advance.
"Hilary Cotter" wrote:

> a defrag won't help. Are there any messages in the event log from MSSCI or
> MSSearch?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aarti." <Aarti@.discussions.microsoft.com> wrote in message
> news:73F8C6ED-2ACE-438E-B341-9DEE30D122AC@.microsoft.com...
> our
> we
> the
> showing
>
>

FTS capabilities + questions

Hello,
I have some questions about Full Text Search and how to handle a
scenario that I have. The database that I am going to hit up against
has 80 tables and over 250 fields that will be searched for FTS. This
database has 600 + tables in it so 80 is needed to make the search
meaningfull for the user.
What is the best method to use FTS against so many tables?
Could I build a massive query that dumps the results into a single
table for searching?
Can I use the 80 tables and use some kind of dynamic query to do my
joins so that it scales?
I know this problem isnt unique to our company but just wondering how
some of the gurus have solved this problem?
Thanks in advance.
That really depends on the queries you have. Ideally you will have a single
table which you full text index. The queries themselves against the
full-text indexes are processed separately from the queries against the
tables, so it is somewhat asynchronous.
Adding this asynchronous nature to 80 tables means that you do not have a
scalable solution.
HTH
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tmueller@.teewebco.com> wrote in message
news:1163098034.044676.194200@.h48g2000cwc.googlegr oups.com...
> Hello,
> I have some questions about Full Text Search and how to handle a
> scenario that I have. The database that I am going to hit up against
> has 80 tables and over 250 fields that will be searched for FTS. This
> database has 600 + tables in it so 80 is needed to make the search
> meaningfull for the user.
> What is the best method to use FTS against so many tables?
> Could I build a massive query that dumps the results into a single
> table for searching?
> Can I use the 80 tables and use some kind of dynamic query to do my
> joins so that it scales?
>
> I know this problem isnt unique to our company but just wondering how
> some of the gurus have solved this problem?
> Thanks in advance.
>
|||Thanks for the fast response.
How about doing the queries up front and stuffing the results into a
single field that is searchable?
Is this the preferred method when encountering many tables like me?
Hilary Cotter wrote:[vbcol=seagreen]
> That really depends on the queries you have. Ideally you will have a single
> table which you full text index. The queries themselves against the
> full-text indexes are processed separately from the queries against the
> tables, so it is somewhat asynchronous.
> Adding this asynchronous nature to 80 tables means that you do not have a
> scalable solution.
> HTH
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <tmueller@.teewebco.com> wrote in message
> news:1163098034.044676.194200@.h48g2000cwc.googlegr oups.com...
|||Sure, this is an option, but then you would have to full-text index this on
the fly. One other option comes to mind, and that is if you are using SQL
2005, you might be able to full-text index an index view which represents
your underlying tables.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"teewebco" <tmueller@.teewebco.com> wrote in message
news:1163101288.921588.258730@.f16g2000cwb.googlegr oups.com...
> Thanks for the fast response.
> How about doing the queries up front and stuffing the results into a
> single field that is searchable?
> Is this the preferred method when encountering many tables like me?
>
>
> Hilary Cotter wrote:
>
|||Ill take a look at that. Thanks!