SQL Server 2008 full-text query may return false positives using number with more than 9 digits as search predicate condition

In Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2, using a full-text query to search for a number with more than 9 digits when using the natural language word breaker (NaturalLanguage6.dll – Version: 6.0.6001.18000) shipped with SQL Server may return false positives.

This behavior happens because the neutral language word breaker that is shipped with these versions converts numbers with more than 9 digits into their scientific notation. See example below for more details:

Example:
If you execute the following query it returns two display terms:

1. 4050300169
2. nn4d0503e+009

The second term is the scientific notation of 4050300169 number. 

Assume that you have a table populated with the following records:

1. 4050300948
2. 4050300552
3. 4050300423

Executing the following query returns all the above records because the scientific notation for all them is the same ( nn4d0503e+009) 

All the three values will be returned because the scientific notation for all of them is the same.

This issue is resolved in a newer version of the NaturalLanguage6.dll (Version 6.1.7600.16385 or higher). This version ships with either Windows 7 or Windows Server 2008 R2. If you are running SQL Server on a lower version of Windows, you first need to upgrade to these operating systems and then use the following procedure to resolve this issue.

Procedure to resolve the problem on SQL Servers running on Windows Server 2008 R2 and Windows 7 environments:


Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

1. Navigate to the following registry hive on your SQL Server machine and save it as SQLMSSearch.reg.
   
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSearch\CLSID
   
   Note: Replace the "<instance>" in the path above with the relevant instance ID of SQL Server 2008/SQL Server 2008 R2.
   E.g. "MSQL10.MSSQLSERVER" for a default instance or "MSSQL10.KATMAI" for a named instance "Katmai"
   For further information refer to the following topic in SQL Server Books Online:
   File Locations for Default and Named Instances of SQL Server (http://msdn.microsoft.com/en-us/library/ms143547.aspx)
2. Edit SQLMSSearch.regfile with notepad and replace all occurrences of NaturalLanguage6.dll
   by C:\\Windows\\system32\\NaturalLanguage6.dll and then save the changes.
   Notes:
   - This assumes your windows folder is located at C:\Windows.
   - You need to enter each backslash in the new path twice!
3. Click the file SQLMSSearch.reg to import the content into the registry.
4. Execute the following T-SQL commands to enable the new setting in SQL Server
   
   

   exec sp_fulltext_service 'load_os_resources', 1
   

   exec sp_fulltext_service 'verify_signature ', 0

   exec sp_fulltext_service 'update_languages'

   exec sp_fulltext_service 'restart_all_fdhosts'

5. Check the availability of the new settings with this T-SQL command:
   

   EXEC sp_help_fulltext_system_components 'fullpath','c:\windows\system32\NaturalLanguage6.dll'

   Notes: You need to adapt the above command to suit the correct Windows system path on your system. Also ensure that all the languages (respectively their lcid), that should show the new behaviour are listed in the column "componentname" in the output.

Languages affected by this issue:
This issue affects the following languages. These languages use the NaturalLanguage6.dll (version 6.0.6001.18000) from the BINN folder of the SQL Server installation:

lcid        name                     
----------- -------------------------
0           Neutral
1025        Arabic
1026        Bulgarian
1027        Catalan
1031        German
1036        French
1037        Hebrew
1039        Icelandic
1040        Italian
1041        Japanese
1043        Dutch
1044        Norwegian (Bokmål)
1046        Brazilian
1048        Romanian
1049        Russian
1050        Croatian
1051        Slovak
1053        Swedish
1056        Urdu
1057        Indonesian
1058        Ukrainian
1060        Slovenian
1062        Latvian
1063        Lithuanian
1066        Vietnamese
1081        Hindi
1086        Malay - Malaysia
1093        Bengali (India)
1094        Punjabi
1095        Gujarati
1097        Tamil
1098        Telugu
1099        Kannada
1100        Malayalam
1102        Marathi
2070        Portuguese
2074        Serbian (Latin)
3082        Spanish
3098        Serbian (Cyrillic)


Steps to repro:

1. Create the following sample table and FTS catalog on your SQL Server
   

   CREATE TABLE dbo.test_table(column1 INT identity(1,1), column2 NVARCHAR(MAX))
   ALTER TABLE dbo.test_table ADD CONSTRAINT PK_TB_Test PRIMARY KEY (column1)
   CREATE FULLTEXT CATALOG [test_Cat]WITH ACCENT_SENSITIVITY = ON
   CREATE FULLTEXT INDEX ON [dbo].[test_table] KEY INDEX [PK_TB_Test] ON ([Test_Cat]) WITH (CHANGE_TRACKING AUTO)
   ALTER FULLTEXT INDEX ON [dbo].[test_table] ADD ([column2] language 0)
   ALTER FULLTEXT INDEX ON [dbo].[test_table] ENABLE
   GO 
   

2. Populate the table with the following sample data
   

   insert dbo.test_table(column2) 
   select N'4050300948' N'4050300948'
   union
   select N'4050300552' N'4050300552'
   union
   select N'4050300423' N'4050300423'
   go
   

3. Execute the following queries: 
   

   select * from dbo.test_table where contains(column2,N'4050300164',LANGUAGE 1033)
   go
   select * from dbo.test_table where contains(column2,N'4050300164',LANGUAGE 1043)
   go
   
   

   You will notice that the first query returns no results because it uses English word breaker but the second query returns three false positives because it is using neutral language word breaker.
   

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.