We help IT Professionals succeed at work.

How to find record that throws Arithmetic overflow exception in SSMS?

on
918 Views
I duped a SQL Server 2008 Table, TEST_Polynomials to TEST_Polynomials2, in order to change the datatypes from a "real" to a "decimal(21, 18). When I executed my script, I received an "Arithmetic overflow error converting real to data type numeric". Is this even possible? If so, how would I go about finding which record(s) are causing the error? I have attached a screenshot!

Thanks!
Screenshot.jpg
Comment
Watch Question

View Solution Only

Commented:
Decimal(21,18) means you can have 21 digits total, 18 of which can be to the right of the decimal point. In your case this means no more than 3 digits can be to the left of the decimal.

Try commenting out the "insert into" part of the script and see if there are any numbers greater than 999.999999999999999999.

Commented:
Oh there definitely are. Here's one for example.

-1.3010430e-018

Commented:
Actually that one is a very small number (near zero), and would be rounded to 18 decimal places. Are there any numbers whose absolute value is greater than 999.99...? Such as 1000.123456..?

Commented:
Well I did a Select MAX(column) on each of the columns. See the screenshot for the results!
Screenshot.jpg

Commented:
OK, so it looks like column [b0] has the largest absolute value, with six digits to the left of the decimal. If you want to use the decimal type, you'll need to declare the destination columns as something like decimal(24,18). (24-18 = Six digits to the left)

Bear in mind, for the huge range of values you have, using decimal/numeric is not going to be as storage-efficient as real/float.

Commented:
Would you know why then...that whenever I select all the records from this table, I get that Arithmetic Overflow error?

Commented:
The error is not from selecting, but from inserting.

The Arithmetic Overflow error arises when you try to convert a "real" datatype to a "decimal" datatype that does not support the number of digits needed to represent the value.

If [Test_Polynomials] is declared with "real" columns, and [Test_Polynomials2] is declared with "decimal(21,18)" columns, inserting values from one into the other like you describe in your question is going to try and make the conversion, and fail due to values like the one from column "b0" in your screenshot.

Commented:
Here is a screenshot of what I get. Instead of Selecting the TOP 1000 Rows, I select them all.
Screenshot.jpg
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
There was a custom SQL Script that I modified and tried, however, it didn't return anything. Not exactly sure what it was suppose to do.

Commented:
Blake, did any of my suggestions help? If not, please clarify... it seems like we resolved the original issue, but now there are others, in which case you should start a new question.

Cheers

Commented:
McOz,

Yes, you did help me with resolving the original issue. I will open a new question to find a resolution for the new problem.

Thanks!
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.