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

by
  1. Make sure you have full-text search feature installed.

Full-Text Search setup

  1. Create full-text search catalog (if needed)

    First check if any catalog already exists

      select *
  from sys.fulltext_catalogs

    If no catalog is found create one

      use [DatabaseName]
  create fulltext catalog FullTextCatalog as default

    you can verify that the catalog was created in the same way as above

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

After following the steps you need a few minutes so that the full text search index is created (this depends on the size of the table and column data)

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. CROSS JOIN vs INNER JOIN in SQL
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Multiple INSERT statements vs. single INSERT with multiple VALUES
  6. Split function equivalent in T-SQL?
  7. Is the NOLOCK (Sql Server hint) bad practice?
  8. Generate Dates between date ranges
  9. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  10. How to print VARCHAR(MAX) using Print Statement?
  11. Where value in column containing comma delimited values
  12. Parse comma-separated string to make IN List of strings in the Where clause
  13. SQL query to split column data into rows
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Compare DATETIME and DATE ignoring time portion
  18. Split function in SQL Server 2008
  19. SQL “between” not inclusive
  20. Can I set ignore_dup_key on for a primary key?
  21. Using PIVOT in SQL Server 2008
  22. Stored procedure that exports data into csv files only exports to one file
  23. How do I prevent a database trigger from recursing?
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. How to combine results of two queries into a single dataset
  26. When using GETDATE() in many places, is it better to use a variable?
  27. Executing SQL Server Agent Job from a stored procedure and returning job result
  28. SQL take just the numeric values from a varchar
  29. COALESCE Function in TSQL
  30. Using CASE Statement inside IN Clause

Leave a Comment