Cetin,

Thanks, this is exactly what I was looking for. I was successfully able to create my fulltext index and perform text searches on my SQL Server table.

Another question I have is, it appears that when I add text to an existing record or add a new record and text, the index does not seem to get updated because my query does not find this new data.

Should my fulltext index always update itself automatically? Or do I have to perform a function to make this happen?

Thanks,

Jerry





>>We have some Visual Foxpro tables that we have been converting to SQL Server tables. A few of these VFP tables have a PHD index on them that we use for text searching.
>>
>>Is there anyway of creating a text search index on a SQL Server table? Is there something built into SQl Server that allows you to text search? Or a third party product.
>>
>>Thanks,
>>
>>Jerry
>
>Jerry,
>You can use fulltext catalogs and indexes. Their creation differ between version (well SQL2000 ways still work but there is a warning that they're depreceated and might be removed in future versions). Assuming 2000:
>
>-Enable fulltext searching on you database
>
>exec sp_fulltext_database 'enable'
>
>-Create a catalog for fulltext
>
>exec sp_fulltext_catalog 'myCatalog', 'create'
>
>-Create fulltext for table
>
>exec sp_fulltext_table 'myTable', 'create', 'myCatalog', 'myPKIndexName'
>
>-Add columns for fulltext indexing
>
>exec sp_fulltext_column 'myTable', 'myColumn', 'add'
>
>-Activate and populate the index
>exec sp_fulltext_table 'myTable', 'activate'
>exec sp_fulltext_table 'myTable', 'start_full'
>
>Then your table is ready for fulltext searching. ie: Wth Northwind:
>

>use Northwind
>go
>exec sp_fulltext_database 'enable'
>go
>
>exec sp_fulltext_catalog 'myCatalog','create'
>exec sp_fulltext_table 'Customers', 'create', 'myCatalog', 'PK_Customers'
>exec sp_fulltext_column 'Customers', 'companyName', 'add'
>exec sp_fulltext_table 'Customers', 'activate'
>exec sp_fulltext_table 'Customers', 'start_full'
>

>
>Test selecting with this index:
>
>

>select * from customers where contains(companyNAme, 'food or box')
>select * from customers where contains(companyNAme, 'food near great')
>

etc
>Cetin