Hi Marc!
I may have a solution. There is a way to do accent insensitive Full
Text Search with SQL Server 2000 and the indexing services. If you are
using version 2005, then it shoul dbe built in. Here is the solution:
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 accent
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. 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 accent 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
Post by m***@gmail.comHi all,
I'm facing an old problem about doing accent-insensitive searches using
indexing service.
Specifically, I'm talking about queries in spanish like "informacion",
returning matches for "información" entries if exist.
I've found this question many times in this group, and many times the
answer is something like...
"Are you using the [X - Language] Word-breaker when indexing content ?
"
And threads end like this.
I have done no extra configurations in my indexing service, so... it
will be using the spanish word breaker by default?
will it use it just if it detects that the indexed content is in
spanish language?
How do I know which word breaker is being used?
What kind of query should I do to use this insensitive property?
thanks in advance,
Marc