Unlike SQL 2008, in SQL 2005 we cannot define a unique stop list (Noise Word List) for each database. As this is controlled by the Language settings on Full Text Index defined in the catalog.
So lets say I want the stop list to only include words from following list:
a, an, the, I, am, us
Rest of the words must be indexed as per the requirements. Setting up a new instance for this one database was out of scope for budget and modifying the noise world list on SQL Server affected more then a single database. I was able to meet the requirements by manually rebuilding the index with new stop list then changing the list back. But I did not want to do this every month manually; so I decided to create SQL Server Job for this. Before job could be created following must be done:
- Confirm the time Job should run, it cannot overlap with any other auto-populations for full text index.
- The full text catalog in question cannot have auto-population schedule.
- You will have to enable xp_cmdshell Extended stored procedure on the server.
- Create two new text files, one called SQL_noiseNEU.txt which is copy of noiseNEU.txt in MSSQL.1\MSSQL\FTData\ and another text file called Simple_noiseNEU.txt with my stop list.
So I created a new job to run last day of the month at 6AM, 6:15AM, and 6:30AM. They reason I created three schedules was to execute each step of the job at different time. If I executed all three steps one after another I found SQL was too fast in starting step 2 before step 1 finished. So putting a small delay gives each step enough time to complete:
Step 1 (Executed at 6AM):
Replace the noiseNEU.txt in MSSQL.1\MSSQL\FTData\noiseNEU.txt with Simple_noiseNEU.txt.
Step 2 (Executed at 6:15AM):
Rebuild the catalog for the database. Please note when you change the noise word list you have to rebuild the entire catalog you cannot just rebuild one index.
Step 3 (Executed at 6:30AM):
Replace the noiseNEU.txt in MSSQL.1\MSSQL\FTData\noiseNEU.txt with SQL_noiseNEU.txt.
SQL Job Script Generated from SQL Server: Link.