GeekZilla

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.

UNTIL

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.

Comments

  Thanks for the post.

  I am facing an issue regarding fulltext search.

  Environment: 

    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 

        CHANGE_TRACKING AUTO 

Add Comment

Latest Articles