Knowledge Base

SQL Server 2005 full-text search includes improved and updated noise word files

Article ID: 905617

Article Last Modified on 11/20/2007

APPLIES TO

Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
Microsoft SQL Server 2005 Enterprise X64 Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
Microsoft SQL Server 2005 Standard X64 Edition
Microsoft SQL Server 2005 Workgroup Edition

INTRODUCTION

Microsoft SQL Server 2005 full-text search includes improved and updated noise word files.

These noise word files are located in the following directory:

$SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\

This directory is created and the noise word files are installed when you set up SQL Server 2005 together with full-text search support.

Note If you used customized noise word files in earlier versions of SQL Server, the customized noise word files will no longer work after you upgrade to SQL Server 2005. To continue to use the customized noise word files, you must follow the steps in the "More Information" section.

MORE INFORMATION

When you upgrade to SQL Server 2005 from SQL Server 2000 or from SQL Server 7.0, the full-text catalogs are populated based on the improved and updated SQL Server 2005 noise word files.

Noise word files from the earlier SQL Server installation are put in the following directory:

$SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\FTERef

Additionally, the old noise word file names are updated. For example, the name of the Simplified Chinese noise word file is updated from noise.chs to noiseCHS.MSSearch2x.txt.

To continue to use the old noise word files after you upgrade to SQL Server 2005, follow these steps:

Immediately stop full-text catalog population after you upgrade to SQL Server 2005. To do this, run the following ALTER FULLTEXT INDEX command on each full-text index:

ALTER FULLTEXT INDEX ON table_name STOP POPULATION

Note In this command, table_name is a placeholder for the table name.

If you have change tracking enabled, run the following command to disable change tracking before stopping population:

ALTER FULLTEXT INDEX ON table_name SET CHANGE_TRACKING OFF

For more information about how to stop full-text index population, see the "ALTER FULLTEXT INDEX (Transact-SQL)" topic in SQL Server Books Online.
Copy the old noise word files from the $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\FTERef directory to the $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\ directory.

Note When you upgrade a cluster solution, the old noise word files are copied to the following setup node directory:

$SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\FTERef
Rename the old noise word files to match the names of the SQL Server 2005 noise word files. For example, rename the Simplified Chinese noise word file from noiseCHS.MSSearch2x.txt to noiseCHS.txt.

Note Make sure that you back up the SQL Server 2005 noise word files before you replace them with the old noise word files.
Repopulate the full-text catalogs. To restart population, run the following ALTER FULLTEXT INDEX command on each full-text index:

ALTER FULLTEXT INDEX ON table_name START FULL POPULATION

If you want to use change tracking, run the following command to restart population and to enable change tracking:

ALTER FULLTEXT INDEX ON table_name SET CHANGE_TRACKING {MANUAL|AUTO}

For more information about how to start full-text index population, see the "ALTER FULLTEXT INDEX (Transact-SQL)" topic in SQL Server Books Online.

Sample script

The following code is a sample script to stop full-text index population for each full-text index in step 1. You can change this script to start full-text index population for each full-text index in step 4.

-- Sample script to stop full-text index population for 
-- each full-text index.

DECLARE @table_name NVARCHAR(517),
        @schema_name NVARCHAR(517);

DECLARE @exec_str NVARCHAR(4000);

DECLARE @change_tracking_state NCHAR(1);

-- Retrieve a list of tables with full-text indexes and stop 
-- full-text index population for each full-text index.
DECLARE ms_crs_ftind CURSOR STATIC LOCAL FOR
    SELECT t.name, SCHEMA_NAME(t.schema_id), ft.change_tracking_state
    FROM sys.fulltext_indexes AS ft 
        JOIN sys.tables AS t 
        ON (ft.object_id = t.object_id);

OPEN ms_crs_ftind;

FETCH ms_crs_ftind INTO @table_name, @schema_name, @change_tracking_state;

WHILE @@FETCH_STATUS >= 0
BEGIN
    -- If change tracking is enabled ('O' indicates change tracking 
    -- is OFF), stop change tracking before stopping population. 
    IF (@change_tracking_state != N'O')
    BEGIN
        SELECT @exec_str = 'ALTER FULLTEXT INDEX ON '
            + QUOTENAME(@schema_name,'[')+'.'+ QUOTENAME(@table_name,'[') 
            + ' SET CHANGE_TRACKING OFF ';
        EXEC (@exec_str);
    END

    -- Stop full-text index population for each full-text index. 
    SELECT @exec_str = 'ALTER FULLTEXT INDEX ON '
        + QUOTENAME(@schema_name,'[')+'.'+QUOTENAME(@table_name,'[') 
        + ' STOP POPULATION ';
    EXEC (@exec_str);

    FETCH ms_crs_ftind INTO @table_name,@schema_name, @change_tracking_state;
END

DEALLOCATE ms_crs_ftind;

Additional query words: fts noise

Keywords: kbinfo kbtshoot kbsql2005fts KB905617

Microsoft KB Archive/905617

Contents

SQL Server 2005 full-text search includes improved and updated noise word files

INTRODUCTION

MORE INFORMATION

Sample script