Arithmetic overflow error converting numeric to data type numeric.

Question

Post reply

Arithmetic overflow error converting numeric to data type numeric.

Angelindiego

SSCertifiable

Points: 6048
More actions
May 23, 2006 at 4:54 pm

#113131

I have a data populaton script that worked fine on the previous version of our application. I am now trying to revamp it for the newer version and I get this error. I am not finding any "user friendly" explanations of this issue. Can anyone help me out in understanding this problem????? Thank you!!!
Thank you!!,
Angelindiego

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

need to see some code

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Ray M SSC-Insane Points: 21093 More actions · Answer 2

declare @var1 decimal(9,5)

set @Var1= 10000

Results

Server: Msg 8115, Level 16, State 8, Line 7

Arithmetic overflow error converting numeric to data type numeric.

Your likely trying to stuff a number into another number data type with an insufficient length

stax68 SSChampion Points: 11711 More actions · Answer 3

decimal(9,5) means the highest number you can do is 9999.99999

The 9 includes the 5 digits to the right of the point.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

stax68 SSChampion Points: 11711 More actions · Answer 4

Sorry my mistake. That was a reconstruction of what we think the original code could have looked like?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Angelindiego SSCertifiable Points: 6048 More actions · Answer 5

ok guys, this may be the issue. I will make some changes in my code and see if it works....I will be back soon!! Thanks!!!!!!

Thank you!!,

Angelindiego

Angelindiego SSCertifiable Points: 6048 More actions · Answer 6

it seems odd and I don't know why, but the variables and values I was working with didn't jive on the new platform. I fussed with them and ended up making my original variables var decimal(8,6) into var decimal(18,6). Go figure. Then after I did that, the debug statements that I had converting the values into varchar(10) wouldn't hold the values either....and I had to make them varchar(20). Why wouldn't they just truncate like always.....go figure again.....

Anyhoot guys, big thanks.....I got it fixed...with your suggestions!!

Thank you!!,

Angelindiego

Clive Chinery SSCrazy Points: 2563 More actions · Answer 7

I also have this problem. I have increased the size of the decimal column C to 38, 14 - maximum size and an excessive amount of precision.

If I do a

SELECT

MAX(CONVERT(DECIMAL(7,2), A) / B) FROm TABLENAME

SELECT

MIN(CONVERT(DECIMAL(7,2), A) / B) FROm TABLENAME

I get

---------------------------------------

3.0000000000000

(1 row(s) affected)

---------------------------------------

0.1000000000000

(1 row(s) affected)

yet if I update

UPDATE

TABLENAME SET C = CONVERT(DECIMAL(7,2), A) / B WHERE B <> 0

Msg 8115

, Level 16, State 8, Line 1

Arithmetic overflow error converting numeric to data type numeric.

The statement has been terminated

.

Obviously the column names and table name I have obscured, but I am puzzled as to why values in the range 0.1 to 3.0 should give an overflow error.

I am using SQL2005 SP2.

Sergiy SSC Guru Points: 110208 More actions · Answer 8

What is the datatype of C?

_____________
Code for TallyGenerator

BudaCli Hall of Fame Points: 3404 More actions · Answer 9

I am also struggling here.Here's a qry:

DECLARE @a DECIMAL(30,16), @b-2 DECIMAL(30,2)

SELECT @a = 1, @b-2 = 1

SELECT @a * @b-2

RESULTS from SERVER 1

Msg 8115, Level 16, State 1, Line 5

Arithmetic overflow error converting numeric to data type numeric.

RESULTS from SERVER 2

1.000000

Check below after running SELECT @@version from both servers

Could this be causing this?

What you don't know won't hurt you but what you know will make you plan to know better