SQL Server 2008 Containstable generate negative rank with weighted_term

I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank valu开发者_Python百科e. The query is as follow

 SELECT ID, CAST(Res_Tbl.RANK AS Decimal) AS Relevancy , Title
  FROM table1 AS INNER JOIN
    CONTAINSTABLE(table1,Title,'ISABOUT("pétoncle" weight (.8), "pétoncle" weight (.8), "PÉTONCLE" weight (.8))',LANGUAGE 1036 ) AS Res_Tbl 
     ON ID = Res_Tbl.[KEY]

When I execute this query I get : Arithmetic overflow error for type int, value = -83886083125.000076.

If I remove one of the two ';' in the ISABOUT function the query complete successfully.

Note you need to have some results if there is no result the query complete successfully.

Does anybody know how to solve this ?

This question is also on dba.stackexchange.com

Qualifier: Since I can't recreate this, I'm unable to know for sure if this will fix the problem. However, these are some things that I'm seeing.

First off, the ampersand, pound sign, and semicolon are word-break characters. That means, that instead of searching for the string "pétoncle", what you're actually searching for is "p", "233", and "toncle". Clearly, that's not your intent.

I have to presume that you have the text "pétoncle" somewhere in your dataset. That means you need that entire string to be complete.

There are a few things you can do.

1) Turn off Stopwords all together. You can do that by altering the full text index to turn it off.

Note that you have to have your database set to SQL Server 2008 compatability for this to not generate a syntax error:

ALTER FULLTEXT INDEX ON Table1 SET STOPLIST OFF;

2) Create a new stoplist

If you create an empty StopList, you might be able to add the stopwords that you want or copy the system stoplist and remove the stopwords that you don't want. (I would advise the second approach).

Having said that, I wasn't able to find the & or # in the system stoplist, so they may be hard coded. You may have to simply turn the stoplist off.

3) Change your search to ignore the "pétoncle" case.

If you drop the "pétoncle" from the ISABOUT and change them to "p toncle", it might work:

'ISABOUT("pétoncle" weight (.8), "p toncle" weight (.8))'

Those are just some ideas. Like I said, without being able to access the system or recreate the scenario, we won't be able to help much.

Some more information for your researching pleasure:

• Stopwords and Stoplists
• Alter Fulltext Index syntax
• FullText search using Thesaurus file and special characters

For people who got to this page searching for negative rank results returned by SQL Server, as I did, it turns out that can happen if some of your match terms are too long (beyond some character limit). SQL Server will not actually complain or produce an error at query time, instead, the ranking will be mostly garbage, producing negative rank for some choices of weights (in my case, esp. with low weight values on the overlong terms). Limit token/word length and avoid this problem (probably a bug deep inside SQL Server 2008 fulltext search).