I have a full-text index on a Title column in a Books table and I have a
CONTAINSTABLE query that seems to work well in most cases. Recently I
discovered that the query was failing to return an expected result. When I
search for titles containing the word "shining," I expect the following title
to match:
Stephen King 2 -- Three Volume Boxed Set: Christine; The Shining; Cujo
But, coming off a fresh rebuild of the catalog (and then waiting until the
status has gone from Populating to Idle), the above title does not match.
The query (which I've simplified) looks similar to this:
SELECT booksTable.Title FROM Books booksTable
INNER JOIN CONTAINSTABLE( Books, Title, 'shining') titleRank ON
booksTable.BookId = titleRank.[KEY]
Note that the non-simplified version actually uses the rank information that
CONTAINSTABLE provides.
At first, I thought it was a probably with Noise Words or the semicolons and
hyphens in that particular title. That's not the case, though. It turns out
that if I modify anything about that book whatsoever (say, changing the word
"Stephen" to "Steven", or even changing a non-Title column in the same book
row), a subsequent query (the exact same query as above) WILL return the
result.
It looks like the full-text catalog is not completely populating itself, and
this particular book is only getting added to the catalog when I update it in
some way (any way at all). After that, it matches perfectly, just like I'd
expect.
My noise words file is completely empty (I tried adding ';' and '-' to it
and rebuilding, to no effect).
As I said, I've tried rebuilding the catalog completely and waiting for it
to repopulate. I need a way to force everything to get indexed right off the
bat without waiting for records to be updated before they'll show up in a
search. How do I do this?
Thank you.
Hilary Cotter - 24 Jan 2006 14:44 GMT
I hate to say this to you, but this works for me.
There could be a couple of factors involved - you could be using change
tracking and the row might not be indexed yet. This is especially the case
if you are indexing binary content. I take it you have tried repeatedly and
can repro this. Also it could be that for some reason the row was not
indexed - check the gatherer logs for any entry.
Your noise word list should contain a single space. If it doesn't it will
using the Indexing Services noise word lists.
One final question - what OS and version of SQL Server and sp?

Signature
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
>I have a full-text index on a Title column in a Books table and I have a
> CONTAINSTABLE query that seems to work well in most cases. Recently I
[quoted text clipped - 48 lines]
>
> Thank you.
Greg Smalter - 24 Jan 2006 16:54 GMT
I am using the default "Track changes:" option of "Automatic." This should
prevent the catalog from becoming fully populated to start, though, should it?
The platform is Windows Server 2003 Standard x64 Edition with SQL Server
2005 Standard x64.
I looked in a file called SQLFT0000500005.LOG and saw no errors. Is there
more gatherer log information I can get at?
Thank you.
Greg
> I hate to say this to you, but this works for me.
>
[quoted text clipped - 61 lines]
> >
> > Thank you.
Hilary Cotter - 24 Jan 2006 20:37 GMT
No the catalog should be automatically populated as soon as you create it.
You are looking at the correct log file I think. Sounds like it thinks the
population happened successfully!

Signature
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
>I am using the default "Track changes:" option of "Automatic." This should
> prevent the catalog from becoming fully populated to start, though, should
[quoted text clipped - 89 lines]
>> >
>> > Thank you.
Greg Smalter - 24 Jan 2006 22:35 GMT
Okay, well, this is really bad. It thinks it has repopulated the catalog but
it clearly hasn't, or I wouldn't have to go in and modify a row in order for
it to ever be returned.
The catalog automatically repopulates after I do a rebuild operation.
Additional information:
In the properties window for the catalog, the "Repopulate catalog" Catalog
Option is greyed out. I have db_datareader as the owner of the catalog.
The item count is 104879. There are 130747 books in the database (some are
duplicates with respect to the full text keys). The unique key count is
56505.
It seems like the item count should be the same as the book count. I
understand why the unique key count is lower.
This is a deal breaker for this system and for full-text in general for my
entire company. I need to ensure that everything gets put in the catalog
initially.
Greg
> No the catalog should be automatically populated as soon as you create it.
> You are looking at the correct log file I think. Sounds like it thinks the
[quoted text clipped - 93 lines]
> >> >
> >> > Thank you.
Hilary Cotter - 25 Jan 2006 02:16 GMT
Item count is the number of tables you are full-text indexing and the number
of rows you are full text indexing. So if there are 130747 rows in your
database, somehow 130747-104878 rows are missing - that's 25869 rows/books.
Any way you can bcp the data out and send it to me?

Signature
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
> Okay, well, this is really bad. It thinks it has repopulated the catalog
> but
[quoted text clipped - 143 lines]
>> >> >
>> >> > Thank you.
Greg Smalter - 25 Jan 2006 05:45 GMT
I can zip the backup file to about 25MB and send it to you, if you tell me
where. It should restore with the full-text catalog and everything so the
problem should be evident. I could try to cut down the size if it's more
convenient.
Thanks.
Greg
> Item count is the number of tables you are full-text indexing and the number
> of rows you are full text indexing. So if there are 130747 rows in your
[quoted text clipped - 149 lines]
> >> >> >
> >> >> > Thank you.
Hilary Cotter - 27 Jan 2006 11:56 GMT
Could you contact me offline?

Signature
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
>I can zip the backup file to about 25MB and send it to you, if you tell me
> where. It should restore with the full-text catalog and everything so the
[quoted text clipped - 180 lines]
>> >> >> >
>> >> >> > Thank you.