Full Text Search in SQL 2005

Full Text Search was installed when SQL Server was installed and a service was running, so I expected it to just be sitting there waiting. This is unlike SQL 2000, where I lost count of how many times I had to install it on different servers.

Was I wrong in expecting it to just work?

Absolutely! (WRONG!!)

It seems that there is a fault in the installer that defaults the service to run under the LocalSystem account.

Fix here :- http://support.microsoft.com/?kbid=907256

Having fixed this, I went on to use Full Text Search and still no luck. The option was disabled in the management console.


I issued the commands

USE MyDatabase
EXEC sp_fulltext_database  'enable'

"At this point, the Full Text Search was available in the Management Console"

From here in TSQL :-

--1  Create the catalog (unless you already have)

EXEC sp_fulltext_catalog   'FTCatalog','create' 

--2  Add a full text index to a table 

EXEC sp_fulltext_table     'Departments''create''FTCatalog''pk_departments' 
EXEC sp_fulltext_table     'Employees''create''FTCatalog''pk_employees' 

--3  Add a column to the full text index 

EXEC sp_fulltext_column    'Departments''ProductName''add' 
EXEC sp_fulltext_column    'Employees''Description''add' 

--4  Activate the index 

EXEC sp_fulltext_table     'Departments','activate' 
EXEC sp_fulltext_table     'Employees','activate' 

--5  Start full population 

EXEC sp_fulltext_catalog   'FTCatalog''start_full' 
Author Ric Hayman MCPD

Ric is an MCPD Microsoft Certified Professional Developer with over 25 years commercial development experience using Microsoft technologies.


Elja Trum said:

Very usefull information, thx!

Got it to work with your help.

Needed to find the PK key indentifier (cost me some time).

So for others: rightclick on the primairy key in table (modify) and choose 'indexes/keys'.

02/Dec/2008 11:11 AM

Arif said:

Very nice Examle of full text search

14/Apr/2010 12:59 PM

Turbo said:

Thanks! That helped me a lot!>

03/Aug/2010 10:25 AM

Elamparithi said:

Nice one

12/Aug/2010 11:24 AM

Santhosh Kumar said:

To get the index name use the below query.

sp_helpindex 'tablename'

06/Oct/2010 10:16 AM

K.Arun said:

clear description.thanks a lot

03/Feb/2011 09:59 AM

Abhishek... said:

Hi Ric,

  Thanks for the post.

  I am facing an issue regarding fulltext search.


    SQL 2005 OS           : Windows 2003 Server.

    Application OS : Windows 2008 Server R2.

  Problem Statement:

    Unable to upload .docx files, others work fine. This works fine if the OS on which Application is installed is Windows 2003 Server.

    On Windows 2008 Server R2, it works fine if I enable the FullText search by checking the "Use Fulltext Indexing" checkbox from database properties. But not if i use below set of commands:

        a. USE <Database_Name> 

        b. EXEC sp_fulltext_database 'enable' 

        c. CREATE FULLTEXT CATALOG <Catalog_Name> WITH                         ACCENT_SENSITIVITY=OFF AS DEFAULT 

        d. CREATE FULLTEXT INDEX ON dbo.t_FileData (fData TYPE COLUMN 

        fDocumentType) KEY INDEX pk_FileData ON <Catalog_Name> WITH 


Please advice if we need to do anything else.



17/Mar/2011 10:03 AM

Ranathilaka said:

Very usefull information, thx!

Got it to work with your help.

30/Sep/2011 04:49 AM

Add Comment


Your comment has been received and will be shown once it passes moderation.