sql server full text search: OS: Windows XP Pro
DB: Sql Server 2000

I set up a full text index on a "searchfield" column on a
table. It created and populated just fine with an item
count of 1744.

Whenever I run a query using contains or containstable or
anything like that, it doesn't return any results. I've
been running the select in query analyzer with sa privs -
still no dice. Here's my query:

SELECT ca.Category, c.ClientID, c.Program, Rank
FROM Client c
INNER JOIN CONTAINSTABLE(Client, SearchField, '"cash"')
KEY_TBL
ON c.ClientID = KEY_TBL.[KEY]
inner join Category ca
on c.PrimaryCategoryID = ca.CategoryID
where c.Active = 1
and c.isAdult = 0
order by ca.Category asc, Rank desc

I chose the keyword "cash" because 90% of the rows in the
searchfield column have that word in them. But I still get

You probably need to repopulate the index. Running

sp_fulltext_catalog '<index name>', 'start_incremental'

will start populating it (This should be scheduled at regular intervals).
You can also do start_full to populate the whole index.

If you need to look up the index name, do

select * from sysfulltextcatalogs

in the database where you need the index back.

Make sure the Microsoft Search service is running as well. Good luck.

--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.

This posting is provided "as is" with no warranties
and confers no rights.

*******************************************************************
[quoted text, click to view]

[quoted text, click to view]
well.


Thanks for the reply, Andy.

The search service *is* running. I repopulated the index
and ran the query again and still got no results. I
deleted the index and recreated it, and *still* no results.

I am completely at a loss. I've got clients that are going

Jesus. I just ran some checks, and one of the tables I was
inner joining to was empty.

*slaps forehead*

Glad you found the answer. Full-text indexing is definitely difficult. I
wish there was an easier way for the indexes to repopulate automatically.
There is a way to modify the structure of the table to do it (
sp_fulltext_table <tablename>, 'Start_change_tracking' )
, but it had some drawbacks.

Andy