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.

Back to the top

Sample script

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

Back to the top