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

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: 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:

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

3. 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.
4. 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;