full text search error - catalogue does not exist

Hi guys, I sent the following queries to my DB and they seem to work with success messages after each line but when I try to test it I get the message at the bottom. Any ideas? there's a good chance my test sql is all wrong!

sp_fulltext_database 'enable'

sp_fulltext_catalog 'Fulltextcatalog1','create'

sp_fulltext_table 'test','create','Fulltextcatalog1','PK_test'

sp_fulltext_column 'test','text','add'

sp_fulltext_table 'test','activate'

sp_fulltext_table 'test','start_full'

sp_fulltext_table test, 'Start_change_tracking'

sp_fulltext_table test, 'Start_background_updateindex'

-- now test it --

SELECT * FROM test WHERE FREETEXT(*,'spotless')

gets this result:
Error -2147217900
Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.Just tried this (where my field name is 'text' and my table is called 'test') but get the same catalogue error
SELECT * FROM test WHERE CONTAINS(text,'spotless')

full text index is exist or not

Hi,
How to find full text index is exist in the table or not ?
like that how to find normal index is exist in the table or not ?

Thanks in adv.
> Murali <-

Please refer to Books Online "Obtaining Full-Text Property Values using Transact-SQL Functions" http://msdn2.microsoft.com/ms142579.aspx

In your case you should use OBJECTPROPERTY function and TableHasActiveFulltextIndex property. OBJECTPROPERTY(table_id, 'TableHasActiveFulltextIndex') will return a value of 1 when at least one column of a table is added for indexing.

Your code will look like:

SELECT OBJECTPROPERTY(OBJECT_ID('MySchema.MyTable'), 'TableHasActiveFulltextIndex');
GO

|||

Hi,

thaks Lefter, I found small solution to find index is exist or not using

sp_helpindex 'tablename'

and

fulltext index is exist or not in the table using

sp_help_fulltext_tables @.table_name='tablename'

bye

--> Murali <--