Microsoft KB Archive/923317

= You obtain incorrect results when you run a full-text search query that uses a thesaurus file in SQL Server 2005 =

Article ID: 923317

Article Last Modified on 9/22/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



SYMPTOMS
Consider the following scenario:
 * You define a term in a thesaurus file in Microsoft SQL Server 2005.
 * The term contains a special character. For example, the term contains one of the following characters:
 * $ (dollar sign)
 * & (ampersand)
 * # (number sign)
 * ' (single quotation mark)
 * - (hyphen)
 * You run a full-text search query that uses the thesaurus file.

In this scenario, you obtain incorrect results.

Additionally, if you use a hyphen in the thesaurus file, you will find that an error message is logged in the event log. For example, the thesaurus file contains the follow tag:

pa

posterior anterior

posterior-anterior

You receive the following event log when you run a full-text search query that uses this thesaurus file:

Event Type: Error

Event Source: MSFTESQL

Event Category: MSFTESQL Service

Event ID: 4154

Date:

Time:

User: N/A

Computer:

Description:

The thesaurus file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\tsenu.xml for lcid 1033 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

Note The special characters that are listed here are only examples. Other special characters may also cause the problem.



WORKAROUND
To work around this problem, avoid using special characters in thesaurus file terms.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
When the word breaker encounters a word-breaking character in a term, the word breaker parses the character as a white space character. For example, if the word breaker encounters the term &quot;look&out,&quot; the word breaker parses the term as the two words &quot;look&quot; and &quot;out.&quot;

Note Word-breaking characters include the following:
 * $ (dollar sign)
 * , (comma)
 * & (ampersand)
 * # (number sign)

When the word breaker encounters a single quotation mark (') in a term, the word breaker correctly parses the term. However, the full-text thesaurus component keeps only the characters that follow the single quotation mark in the term. For example, if the original term is &quot;L'Mary,&quot; the term appears as &quot;Mary&quot; in the thesaurus file.

When the word breaker encounters a hyphen (-) in a term, the word breaker correctly parses the term. However, the full-text thesaurus component treats the characters that are connected by the hyphen together with the hyphen itself as empty characters. For example, if the original term is &quot;well-known celebrity,&quot; the term appears as &quot;celebrity&quot; in the thesaurus file.

The problem that is described in the &quot;Symptoms&quot; section may also cause a duplicate entry or an empty entry for a term in the thesaurus file. The full-text thesaurus component cannot load a thesaurus file if the file contains a duplicate entry or an empty entry.

Note In SQL Server 2005, if the full-text thesaurus component finds an error in the thesaurus file, the full-text thesaurus component does not load the thesaurus file.

For example, you define the following terms in the thesaurus file: Windows Windows&you In this scenario, the ampersand will be parsed as a white space character. By default, the word &quot;you&quot; is listed in the noise file and is ignored. Therefore, the word breaker parses the term &quot;Windows&you&quot; as &quot;Windows.&quot; Because the thesaurus file already contains the term &quot;Windows,&quot; the thesaurus file now contains duplicate terms. Therefore, the full-text thesaurus component does not load the thesaurus file.

Keywords: kbsql2005fts kbexpertiseadvanced kbtshoot kbprb KB923317

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.