Full text catalog empty

Hi,

I'm not sure this is the right place for this thread but here goes.

I am trying to setup a full text catalog on one table in my database. Although the catalog is created fine and no erros are reported when creating or populating the catalog. It seems to be empty as its size is only 1Mb and my query:

select top 10* from kmuser.tbl_webpages
where contains (WebPageHTML_FT, '"aruba"'

returns no rows, while:

select * from kmuser.tbl_webpages
where webpagehtml_ft like '%aruba%'

returns 6 rows.

I have read several articles on the subject and have followed them to a T. I've deleted it and recreated so many times now both through Enterprise manager and through query analyser using the stored procedure sp_fulltext_catalog.

I've checked that full text catalog is enabled using:

select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')

I am really stuck. Does anyone have any ideas?

Many thanks

This form is specific to Data Mining topics. You'll get better answers by posting to the SQL Server Database Engine forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

After recreating the full test catalog, did you Start a Full Population on the catalog?

|||

Thanks. I started a thread on the the other forum but no one has replied so far.

Yes I did start a full population but no change from my original problem

|||

The query you have searchs for the phrase "aruba" in the column. If you're searching for a prefix term, try the query:

select top 10* from kmuser.tbl_webpages
where contains (WebPageHTML_FT, '"aruba*"'

If you're searching for the term "aruba", then you can structure your query as:

select top 10* from kmuser.tbl_webpages
where contains (WebPageHTML_FT, 'aruba'

You can look up the documentation for CONTAINS at http://technet.microsoft.com/en-us/library/ms187787(SQL.90).aspx. Hope this helps

|||

You can check whether the fulltext catalog is populated using the following command

SELECT fulltextcatalogproperty('CatalogName', 'ItemCount');

Following on from Shuvro's point, your 2 searches are not identical as your contains clause will search for that standalone term whereas your like clause will search for that pattern anywhere in the column. As he suggests, check the wildcard options for CONTAINS.

eg pseudo code

create table1 (col1)

insert into table1 (space)

insert into table1 (lace)

insert into table1 (face)

select * from table where col1 LIKE '%ace%'

returns band, land, sand

select * from table where contains(col1, '"ace"')

returns 0 rows

Hope this helps!