Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 value. 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 Stackoverflow

share|improve this question
1  
Make sure the term weightings add up to 1, and try again. I suspect this is the problem, but since we don't have your data, I doubt we'd be able to reproduce this problem exactly. –  Jon Seigel Aug 15 '11 at 4:44
1  
Could you add some sample data that recreates the problem? I have tried creating some sample on my own, but was unable to recreate this. Also, what happens if you drop the CAST in your select? –  Richard Aug 15 '11 at 15:56
    
I saw this was cross-posted on StackOverflow (smart move). link –  Richard Aug 18 '11 at 15:26

1 Answer 1

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:

share|improve this answer
    
I know for the stoplist but i can't turn it off. So i already use option 3 : ignore the pattern. Thanks for the help but my concern is about the CONTAINSTABLE function that return an overflow error. I really want to be able to search for HTML encoded string the é means "é". This is a search that i use in a web site. –  Nico Aug 21 '11 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.