Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

Question

I am getting following error in my SQL server 2008 R2 database      Cannot use a CONTAINS or FREETEXT predicate on table or indexed view  tblArmy  because it is not full-text indexed

User · Answer

There is one more solution to set column Full text to true.

These solution for example didn't work for me

ALTER TABLE news ADD FULLTEXT(headline, story);

My solution.

Right click on table
Design
Right Click on column which you want to edit
Full text index
Add
Close
Refresh

NEXT STEPS

Right click on table
Design
Click on column which you want to edit
On bottom of mssql you there will be tab "Column properties"
Full-text Specification -> (Is Full-text Indexed) set to true.

Refresh

Version of mssql 2014

User · Answer

You must define Full-Text-Index on all tables in database where you require to use a query with CONTAINS which will take sometime   Instead you can use the LIKE which will give you instant results without the need to adjust any settings for the tables   Example   SELECT   FROM ChartOfAccounts WHERE AccountName LIKE   Tax     The same result obtained with CONTAINS can be obtained with LIKE   see the result

User · Answer

Make sure you have full-text search feature installed    Create full-text search catalog    use AdventureWorks  create fulltext catalog FullTextCatalog as default   select    from sys fulltext catalogs  Create full-text search index    create fulltext index on Production ProductDescription Description   key index PK ProductDescription ProductDescriptionID   Before you create the index  make sure       - you don t already have full-text search index on the table as only one full-text search index allowed on a table      - a unique index exists on the table  The index must be based on single-key column  that does not allow NULL       - full-text catalog exists  You have to specify full-text catalog name explicitly if there is no default full-text catalog    You can do step 2 and 3 in SQL Sever Management Studio  In object explorer  right click on a table  select Full-Text index menu item and then Define Full-Text Index    sub-menu item  Full-Text indexing wizard will guide you through the process  It will also create a full-text search catalog for you if you don t have any yet     You can find more info at MSDN

User · Answer

A workaround for CONTAINS  If you don t want to create a full text Index on the column  and performance is not one of your priorities you could use the LIKE statement which doesn t need any prior configuration   Example  find all Products that contains the letter Q   SELECT ID  ProductName FROM  ProductsDB   dbo   Products  WHERE  ProductsDB   dbo   Products  ProductName LIKE   Q

User · Answer

you have to add fulltext index on specific fields you want to search   ALTER TABLE news ADD FULLTEXT headline  story     where  news  is your table and  headline  story  fields you wont to enable for fulltext search

User · Answer

Select   from table where CONTAINS  Column     A00         will act as   same as  where  Column  Like  A00

User · Answer

You might need to enable the table for full-text indexing

[sql] Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to full-text-search