asked on

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

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

McOz

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.

BlakeMcKenna

ASKER

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

-1.3010430e-018

McOz

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..?

BlakeMcKenna

ASKER

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

McOz

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.

BlakeMcKenna

ASKER

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

McOz

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.

BlakeMcKenna

ASKER

Here is a screenshot of what I get. Instead of Selecting the TOP 1000 Rows, I select them all.
Screenshot.jpg

ASKER CERTIFIED SOLUTION

McOz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BlakeMcKenna

ASKER

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.

McOz

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

BlakeMcKenna

ASKER

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!