[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-20
12
Medium Priority
?
462 Views
Last Modified: 2014-08-22
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
0
Comment
Question by:BlakeMcKenna
  • 6
  • 6
12 Comments
 
LVL 9

Expert Comment

by:McOz
ID: 40274267
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
 

Author Comment

by:BlakeMcKenna
ID: 40274357
Oh there definitely are. Here's one for example.

-1.3010430e-018
0
 
LVL 9

Expert Comment

by:McOz
ID: 40274375
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:BlakeMcKenna
ID: 40274592
Well I did a Select MAX(column) on each of the columns. See the screenshot for the results!
Screenshot.jpg
0
 
LVL 9

Expert Comment

by:McOz
ID: 40274609
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
 

Author Comment

by:BlakeMcKenna
ID: 40274856
Would you know why then...that whenever I select all the records from this table, I get that Arithmetic Overflow error?
0
 
LVL 9

Expert Comment

by:McOz
ID: 40274918
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
 

Author Comment

by:BlakeMcKenna
ID: 40275072
Here is a screenshot of what I get. Instead of Selecting the TOP 1000 Rows, I select them all.
Screenshot.jpg
0
 
LVL 9

Accepted Solution

by:
McOz earned 2000 total points
ID: 40276090
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
 

Author Comment

by:BlakeMcKenna
ID: 40276300
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
 
LVL 9

Expert Comment

by:McOz
ID: 40278863
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
 

Author Comment

by:BlakeMcKenna
ID: 40278875
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question