Full-Text Search and Norwegian

I have a very strange situation with full-text search. Looks like it also causes ft queries to get stuck. Happens on both SQL Server 2008 R2 and on SQL Server 2012. Examples below can be reproduced on SQL Server 2012 with AdventureWorks2012 database.

Consider following 3 queries:

1) SELECT [KEY], [Rank] FROM FREETEXTTABLE(Production.Document, DocumentSummary, 'SQL Server', LANGUAGE N'abcd',100)
2) SELECT [KEY], [Rank] FROM FREETEXTTABLE(Production.Document, DocumentSummary, 'SQL Server', LANGUAGE N'Norwegian',100)
3) SELECT [KEY], [Rank] FROM FREETEXTTABLE(Production.Document, DocumentSummary, 'SQL Server', LANGUAGE N'Norwegian (Bokml)',100)

First query fails with following error:

Msg 7678, Level 16, State 12, Line 1
The following string is not defined as a language alias in syslanguages: abcd.

Qeries #2 and #3 both complete without errors. On a table with real data (I have such at my client's site) both return data (although different).

BUT! Query #2 causes following errors to be recorded in SQL Server Error log:

Error 2786, Level 16, State 1, Procedure sp_fulltext_load_thesaurus_file, Line 41, Message: The data type of substitution parameter 1 does not match the expected type of the format specification.

I've already opened bug for it:

https://connect.microsoft.com/SQLServer/feedback/details/779257/sys-sp-fulltext-load-thesaurus-file-fails-sometimes-due-to-error

The bug is in RAISERROR command (it expects 2 parameters for the particular error but only one is supplied - of a wrong data type). But codepath inside procedure indicates that invalid lcid (locale id or language id) has been supplied.

As I said, query #3 runs successfully.

If we query master..syslanguages (as error message from query #1 points us to), we can see row with the value "Norwegian" in "alias" column (lcid = 2068). If you're still following me, "Norwegian" is the value that has invalid lcid - at least it generates messages in error log.

When I queried sys.fulltext_languages, I received different results on my laptop with SQL Server 2012 (no rows with any sort of Norwegian) and at client's site with SQL Server 2008 R2 (lcid = 1044, language = "Norwegian (Bokml)".

The interesting thing here is that with "Norwegian (Bokml)" query succeeds on both machines without throwing error either to the client to the log. Even on mine that doesn't have "Norwegian (Bokml)" - neither in syslanguages nor in sys.fulltext_languages. I guess, it has something internally or takes it from Windows.

Last thing - when I see large number of error messages in the log, entire system is stuck. I see lot's of FTS queries waiting on FT_IFTS_RWLOCK wait type. It is almost undocumented, some internal synchronization. I guess it is connected (can't do much more besides guessing - internal architecture is undocumented).

So... beware of the described strange behavior. And I'll be gratefull if anybody from Microsoft can shed some light on these issues.

  • Moved by Olaf HelperMVP Wednesday, March 27, 2013 5:41 PM Moved from "Database Engine" to "SQL Search" for better response
March 18th, 2013 1:48pm

hi

I am getting the same error messages alot in event log and sqlsever log in SQL Server 2014 Standard Edition.

Error: 50000 Severity: 16 State: 1 Error 2786, Level 16, State 1, Procedure sp_fulltext_load_thesaurus_file, Line 41, Message: The data type of substitution parameter 1 does not match the expected type of the format specification

is there any workaround solution? 

thanks,

Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2015 5:54pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics