Search code examples
sql-serverfreetexttable

Create FREETEXTTABLE in MS SQL

I am trying to create FREETEXTTABLE. I am getting this following error.

Msg 7601, Level 16, State 2, Line 1

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

My Sample,

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));

CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);

INSERT Flags VALUES ('France', 'Blue and White and Red');
INSERT Flags VALUES ('Italy', 'Green and White and Red');
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');

SELECT * FROM Flags;
GO

CREATE FULLTEXT CATALOG TestFTCat;
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;
GO 

SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Blue');
Solution

  • Simple way to validate if you have installed the Full Text "component" of MSSQL Server 2008 is to execute the following T-SQL

    SELECT SERVERPROPERTY('IsFullTextInstalled')

    If this returns a value of '1' then the component is installed.

    Else you have to install SQL Server Fulltext search on an existing SQL Server instance.

    1. Download latest installer for "SQL Server Express with Advanced Services" on the server (save it in "c:\installers" for example) MSSQL Server 2008 Express with Advanced Services.
    2. Browse to where you saved it and Unzip the self extracting EXE (right click and click "Extract").
    3. Install it by double clicking setup.
    4. Choose "upgrade existing instance to a new version or edition" and go through all steps.
    5. I am pretty sure I was never asked what features I would like (ie no chance to choose Fulltext Search). When running the installer again there was also no way to "add features to existing instance", so eventually I figured out the following additional steps were needed...
    6. Go to Control Panel > Add or Remove Programs
    7. Find SQL Server and open it. (It asked me a question along the lines of "uninstalling this while other people are using it will cause them some pain" - it was a bit scary but I said yes OK and it then asked the usual question: do I want to unininstall or change)
    8. Question: uninstall or change (Answer: change).
    9. Click "Add Features"
    10. Browse for the install media. In this case we can locate the place where we unzipped the installer for "SQL Server Express with Advanced Services" (note it must be extracted or it won't like it)
    11. Go through a bunch of crapping around steps. You will then be asked "install new instance" or "add features to existing instance" (choose add features).
    12. At the "Features" step you can check the box "Fulltext Search" (hooray!!)

    13. Finish the wizard and everything should now work, run this sql to confirm:

      SELECT SERVERPROPERTY('IsFullTextInstalled')

    Now you can Enable Full Text Search With T-SQL

    -- We'll use Northwind sample database to enable
-- Full Text Search feature using T-SQL code only
USE Northwind
GO

-- We need to enable full text search for Northwind database
-- We will do that with sp_fulltext_database procedure
EXEC sp_fulltext_database 'enable'
-- Create catalog
EXEC sp_fulltext_catalog 'NorthwindCatalog','create'
-- Add some indexes to database
EXEC sp_fulltext_table 'Customers', 'create', 'NorthwindCatalog', 'pk_customers'
EXEC sp_fulltext_table 'Orders', 'create', 'NorthwindCatalog', 'pk_orders'
-- add columns for searching to full text search index
EXEC sp_fulltext_column 'Customers', 'CompanyName', 'add'
EXEC sp_fulltext_column 'Customers', 'ContactName', 'add'
EXEC sp_fulltext_column 'Customers', 'Address', 'add'
EXEC sp_fulltext_column 'Customers', 'City', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipName', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipAddress', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipCity', 'add'
-- Activate full text search indexes
EXEC sp_fulltext_table 'Customers','activate'
EXEC sp_fulltext_table 'Orders','activate'
-- start full population of catalog
EXEC sp_fulltext_catalog 'NorthwindCatalog', 'start_full'

    Now you can perform search on indexed columns using CONTAINS, FREETEXT, CONTAINSTABLE or FREETEXTTABLE keywords. For example, let say I want to check all contacts where first name is Maria or Ana: 

    USE Northwind
GO

SELECT CustomerId, ContactName, CompanyName, Address, City
FROM Customers c INNER JOIN
CONTAINSTABLE(Customers, (ContactName), '"Maria" OR "Ana"') AS KEY_TBL
ON c.CustomerId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

    This SQL query will return results as on image bellow: enter image description here