Thesaurus support in SQL Server Full Text service

Question

Thesaurus support in SQL Server Full Text service

EIJ

Right there with Babe

Points: 734
More actions
May 20, 2006 at 8:44 am

#366212

Hi,
I’ve been trying to add keywords to the thesaurus used for SQL Server 2005 Full Text services but do not seems to get any improved results.
For example, I have a catalogue of article titles which contains titles such as ‘CRM’ and then some others use the words ‘Customer Relationship Management’, the idea solution I would have thought was SQL Servers thesaurus support, I’ve been though and added the values:
_{customer relationship management}
_crm

To the txGlobal.xml file in the SQL Servers FTData directory, I then amended by test query to use this clause: FORMSOF(THESAURUS,"CRM")
The idea being that when I search for CRM it will bring back articles with Customer Relationship Management also. However I’m just getting results for the word CRM and no expansion is taking place it would seem.
Would anyone be able to clarify what the best approach to making this work would be,
1. Do I need to restart the SQL Server FullText service after making any changes to the XML files?
2. Do I need to restart the SQL Server service after making any changes to the XML files?
3. Do you need to re-populate the full text catalogues after making a change the XML files?
If this syntax correct when trying to utilise the thesaurus?
CONTAINSTABLE (Article_Metadata, *, ‘FORMSOF(THESAURUS,"CRM")’ )
Any help would be appreciated.
Thanks
Ed

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

I'm having a similar issue. I know you're supposed to restart the Full-Text Search Service. Try stopping the SQL Server Service and then stop the Full-Text Search service. Then start them both up again. Also make sure you've changed the correct thesaurus file (tsENU.xml for U.S. English, tsENG.xml for the Queen's English, etc.)

Mike C SSC-Insane Points: 23224 More actions · Answer 2

Also the format should be:

CONTAINSTABLE (Article_Metadata, ‘FORMSOF(THESAURUS,"CRM")’ )

or

CONTAINS(Article_Metadata, 'FORMSOF(THESAURUS, "CRM")')

I would use the CONTAINS version at first since you don't have to link to another table to get useful results. This assumes that the column you're searching is named Article_Metadata also. Use * instead to search all columns that are full-text indexed.

EIJ Right there with Babe Points: 734 More actions · Answer 3

Thanks Mike,

I've tried restating the FT serives, then SQL Server services... and then when I got no different results, I restarted the whole machine (I saw that somebody on the MSDN forums suggested this to another with a same problem), still no luck.

So far I've added the synonyms to tsENU.xml, tsEng.xml and tsGlobal.xml in an attempt to get anything working (my word breaker and default database language is British Engligh).

As for the containstable query, it works perfectly well with and without the FORMSOF(Thesaurus) clause which makes me think that it's valid, no syntax errors and executes just fine, it's just that the synonym does not get picked up.

Ed

EIJ Right there with Babe Points: 734 More actions · Answer 4

I've got an update to my problem:

I realised I hadn't removed the comments from the XML wild, I'm just a dunce

However, after removing the comments I've found this error in my Event Log:

"The global thesaurus file C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\FTData\tsglobal.xml has invalid format. Please edit the file and correct it. Use retail tracing for detailed error description. See user documentation on fulltext retail tracing.Component: MicrosoftIndexer"

I've tried hacking about the the XML file and think it's one of these lines which is causing problems:

0

I've done a search on my computer for the file tsSchema.xml and I can't find it, could this be the issue? If so would anyone be able to post a valid copy of tsSchema.xml?

Any ideas?

Thanks

Ed

Mike C SSC-Insane Points: 23224 More actions · Answer 5

tsSchema just defines the xml namespace. It shouldn't have to be on your local computer.

Do you have the line <diacritics_sensitive>0</diacritics_sensitive> in your thesaurus? Try <diacritics = false/> instead and let me know if that fixes it for you.

Also, I found that I was getting the following error in the Event Log:

Source: MSFTESQL$SQL2K5

Category: MSFTESQL Service

Event ID: 4153

Description: Unicode byte order mark (0xFEFF) missing at the beginning of thesaurus file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\tsenu.xml.Component: MicrosoftIndexer

This was causing me issues and making the Search Service ignore the thesaurus. I switched editors from TextPad to NotePad and re-saved as Unicode format and it works. Also, I used the <diacritics_sensitive>0</diacritics_sensitive> tag instead of the <diacritics = false /> mentioned above.

EIJ Right there with Babe Points: 734 More actions · Answer 6

Thanks Mike, it's working now, it appears it was the line:

<diacritics = false /> which was needed, however I'm sure I tried this early yesterday and it didn't work, maybe I forgot to restart the FT Service after.

Thanks for all your help

Ed

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 7

I am tracking down issues why my custom thesaurus isn't working. I found this article: http://support.microsoft.com/kb/923317 and I saw that I had an invalid character in my Thesaurus. None the less I restarted the Full-Text service and still get the same results as before. Does the server have to be rebooted? Is there anything else that needs to be restarted in order for this to work?

Kurt Zimmerman

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman