Unique Stop List for Single Database in SQL 2005

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.