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
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

McOzCommented:
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.
0
BlakeMcKennaAuthor Commented:
Oh there definitely are. Here's one for example.

-1.3010430e-018
0
McOzCommented:
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..?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

BlakeMcKennaAuthor Commented:
Well I did a Select MAX(column) on each of the columns. See the screenshot for the results!
Screenshot.jpg
0
McOzCommented:
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.
0
BlakeMcKennaAuthor Commented:
Would you know why then...that whenever I select all the records from this table, I get that Arithmetic Overflow error?
0
McOzCommented:
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.
0
BlakeMcKennaAuthor Commented:
Here is a screenshot of what I get. Instead of Selecting the TOP 1000 Rows, I select them all.
Screenshot.jpg
0
McOzCommented:
Blake, check out this link: Real datatype error "An error occurred while executing batch. Error message is: Arithmetic Overflow"

I think the error is being caused by your NaN values. You'll have to decide what to do about those, maybe replace with NULL? Once the NaN values are gone, you should be alright.

Good luck!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlakeMcKennaAuthor 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.
0
McOzCommented:
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
0
BlakeMcKennaAuthor 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.