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 · Accepted 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.

enter image description here

You can find more info at MSDN

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

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

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

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

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 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