Spanish thesaurus file not loading on Sql Server 2008

Question

Hi!

I'm having some problems making full text index search work with a spanish thesaurus file.

Details:

- SQl Server Express 2008 with advanced services on a windows vista box.

- sp_configure 'default full-text language' returns 3082 which is the LCID for Spanish.

- Thesaurus file is located at SQLServerInstallationFolder\MSSQL10.SQLEXPRESS\MSSQL\FTData\tspan.xml and looks like:

- Database and full-text search where restarted engine after each change.

- Thesaurus file is loaded this way: 

EXEC sys.sp_fulltext_load_thesaurus_file 3082;

- Thesaurus file is located at SQLServerInstallationFolder\MSSQL10.SQLEXPRESS\MSSQL\FTData\tspan.xml and looks like:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
	<diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>Internet Explorer</sub>
            <sub>IE</sub>
            <sub>IE5</sub>
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            <sub>Windows 2000</sub>
        </replacement>
        <expansion>
            <sub>run</sub>
            <sub>jog</sub>
        </expansion>
    </thesaurus>
</XML>

- No error was found inside the log events

Problem:

The following query should return more than one row but only retrieves one:

SELECT * FROM tBrower WHERE CONTAINS(description,'FORMSOF(Thesaurus,"IE5")')

I looked for the spanish registry key under: HKLM\Software\Microsoft\Microsoft SQL Server\YourInstance\MSSearch\Language\span but was not able to find it. 

Maybe this is part of the problem.

Thanks a lot!

Answer 1

did you issue a call to this:

EXEC sys.sp_fulltext_load_thesaurus_file 3082;
GO

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Thanks Hilary! Yes, I did execute the "EXEC sys.sp_fulltext_load_thesaurus_file 3082;" command but without any results.

Any ideas?

Answer 3

the Spanish thesaurus file is tsesn.xml. Try that one.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

I update the tsesn.xml, restarted sql server file but with same results.

The table contains the following 3 rows:

1 - "IE5"

2 - "Internet Explorer"

3 - "IE6"

If I execute the query : 

SELECT * FROM tBrower WHERE CONTAINS(description,'FORMSOF(Thesaurus,"IE5")')

It should retrieve the 3 rows, ¿right?

Answer 5

what does this return?

SELECT * FROM tBrower WHERE CONTAINS(description,'FORMSOF(Thesaurus,"IE5")' language 3082)

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Hilary,

That did the trick!

Thank you very much for your time and expertise!