Making accent insensitive searches with Full Text Search
Installing an accent insensitive version of Microsoft Search
Service

It is really a shame that Microsoft did not provide a solution for doing case
insensitive Full Text Search (FTS) before 2005. It is reaslly a lack of
consideration for all of their customers speaking or using language
that has accents. This is why I decided to post these instructions,
because there is a way around it. It is a post by Alex Hubner that
pointed me in the right direction. Thanks Alex! His solution works,
I tried it. He did not give the details on how to do it, so I decided I would:

* Get SharePoint Portal Server 2001 Service Pack 3 (SP3): KB837017 from
http://www.microsoft.com/downloads/details.aspx?FamilyID=15677a92-3470-
465f-9f63-e621094103e0&DisplayLang=en. There are five files to download:

o    File Name: SPSFull1.exe
    File Size: 27937 KB
o    File Name: SPSFull2.exe
    File Size: 25464 KB
o    File Name: SPSFull3.exe
    File Size: 25975 KB
o    File Name: SPSFull4.exe
    File Size: 27206 KB
o    File Name: SPSFull5.exe
    File Size: 25198 KB

You can download and unpack the five downloaded files, but in fact, you only
need the content of SPSFull3.exe. A directory called SharePointPortalInstall
will
be created.

Note: Do not use SharePoint Portal Server 2003, since its directory structure
is
different, and the Microsoft Search Services don't seem to be a separate and
independent module in this version.

* Only the MS-Search part of the package is needed. You can find it in
SharePointPortalInstall\Server\Search. The installation program is called
SearchStp.exe. This is the installation program we will use to re-install
Microsoft
Search Service and make searches accent insensitive. This will allow, for
example, the get the same search results not matter if the user enters a
keyword
with or without accents (Eg.: 'Montreal' or 'Montréal');
* You can use the documentation found on
http://support.microsoft.com/?kbid=827449 to re-install the Microsoft Search
Services. However, it is not necessary to go through all that trouble. I did
so the
first time. However, some registry keys used by SQL Server and MS Search
Services were missing after. Gladfully, I had made a backup of all the
registry
keys that we are asked to delete in this documentation. Also, the MS Search
Services have been installed into a different directory, so I had to do a
search
through the registry to replace the old path by the new one where it had not
been
updated by the installation program. In fact, you can skip this paragraph.
I'm just
adding this information in case you would need it;
* Here are the registry keys I backup prior to this installation, just in
case:

.HKEY_LOCAL_MACHINE\Software\Microsoft\Search
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSCNTRS
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSEARCH
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSGATHERER
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSGTHRSVC
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSINDEX

* The second time I installed the Accent Insensitive MS Search Services, I
only
used section 2 of the documentation found on
http://support.microsoft.com/?kbid=827449 called Install the Microsoft Search

Service. However, I used the SearchStp.exe file downloaded earlier instead of

using the one they specify, and I stopped the Microsoft Search Service before

doing the installation even if it is not specified in the documentation. As
specified
in this document, "To view the original domain name and user account, you can

use the most recent SQL Server setup log file (SqlstpN.log). In the SqlstpN.
log
file, locate the line where the SQL Server Setup program ran the Ftsetup.exe
program. Additionally, make sure that the information is the same as the
information that is included in the command." On my computer, this file was
called sqlstp.log and it was located in the Windows directory.
* You can probably also follow the steps in section 3, but I haven't done so.

Once this is all done, you can use Enterprise Manager to create indexes on
your database.
There is one thing though to know. When using Enterprise Manager to manage a
remote
server, Tools->Full Text Indexing remains greyed out. I had to start
Enterprise Manager
locally on the server in order to gain access to the Full Text Indexing tool.

Creating indexes
In order to create Full Text Search indexes on a table, the table needs to
have a primary
key. So make sure all the tables you want to index have primary keys.

Here are the steps to create indexes. The first time you create an index, you
will need to
create a catalog:

* Start Enterprise Manager and open the your database Table View;
* Select the table you want to index using FTS (Full Text Search) and right-
click on
it.
* In the menu that just opened with the right-click, select Full-Text Index
Table,
and then in the sub-menu select Define Full-Text Indexing on a Table. This
will
start the Welcome to the SQL Server Full-Text Indexing Wizard.

* Click on Next;
* On the Next Screen, select the key to index, and click on Net;
* Select the field(s) to index and choose the proper language settings in the
second
column. Click on Next;
* You will then have to create a Catalog (An FTS Catalog). Give it a name and

enter a location where it should be stored. You can use the default path.
Click on
Next;
* You will then have to create a schedule for the indexation process. Click
on New
Catalog Schedule, and define a Schedule. Let's make it a Full Population.
Maybe
an Incremental Population would work, but I am not sure in that case what
happens if information is removed from the database being indexed. Once the
schedule is defined, click on Ok and then on Next;
* Click on Finish, and the catalogue will be created:

We are now ready to create an index on another table. This time, it is not
necessary  to
create a new catalogue. Simply reuse to one we previously created. Same thing
for the
schedule; the one already created can be reused.

We now have a catalogue, but the indexes are still empty, since they are
scheduled to be
generated in the future (based on the schedule you created). We should now do
a full
population of the indexes. In SQL Server Enterprise Manager, right click on
each table
you wish to index, select Full-Text Index Table, and then select Start Full
Population.

You can now use a query similar to the one bellow in SQL Query Analyzer to
test
whether the case insensitive indexing is working properly:

SELECT *
FROM table_name
WHERE CONTAINS(field_name, 'raphaël')

o    table_name should be replaced by the name of the table you want to search;
o    field_name should be replaced by the name of the field you want to search;

Use this query with the same word with and without accents, and you should
get the same
result both times.

Enjoy!

Jean-François Beauchamp
IT Consultant
jackojf-fts at yahoo.com