• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type numeric.

Our program closes year end and purges data.
It errors with [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type numeric.
From what I have been finding this statemet gets the error...
UPDATE InvWarehouse SET PrevYtdSalesVal = YtdSalesValue, PrevYearQtySold = CONVERT (DECIMAL(14,2), YtdQtyIssued + YtdQtySold), YtdSalesValue = 0, YtdQtySold = 0, YtdQtyIssued = 0

I think there must be a bad value somewhere in either of these fields PrevYtdSalesVal or YtdSalesValue
Is there a way to find where the bad data is? I am a newbie to writing sql queries.
0
edwarddoylesr
Asked:
edwarddoylesr
  • 4
  • 4
1 Solution
 
pcelbaCommented:
What value returns:

SELECT MAX(YtdSalesValue), MAX(YtdQtyIssued + YtdQtySold)
  FROM InvWarehouse

And does the result fit into  PrevYtdSalesVal and PrevYearQtySold columns?

You could also check for minimum values if negative numbers are allowed.
0
 
edwarddoylesrAuthor Commented:
When I run that MAX query I get "The decimal field's precision is too small to accept the numeric you attempted to add.
When I run MIN I get
EXPR1000 -3490.46   EXPR1001 -2200

Not sure what this means
0
 
pcelbaCommented:
So, split the SELECT MAX() into three statements:

SELECT MAX(YtdSalesValue)
  FROM InvWarehouse

SELECT MAX(YtdQtyIssued)
  FROM InvWarehouse

SELECT MAX(YtdQtySold)
  FROM InvWarehouse

It should show which column causes the problem. You could also post column data types.

EXPR1000 and EXPR1001 points to Access. How it is releated to SQL Server 2008?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pcelbaCommented:
The error message "The decimal field's precision is too small to accept the numeric you attempted to add" points to the old Jet engine (http://support.microsoft.com/kb/270704/en-us) and you should upgrade it. If you have newer Access version already then you could play with data types on SQL Server and refresh the structures of linked tables in Access.
0
 
edwarddoylesrAuthor Commented:
Sorry I was running queries from access.

Ok so here is what I get...
For the MAX on all three I get
YtdSalesValue 451834.80   YtdQtyIssued 9901264.000   YtdQtySold 273000.000
The column data types are
YtdSalesValue (decimal(14,2), null)
YtdQtyIssued  (decimal(10,3), null)
YtdQtySold      (decimal(10,3), null)

So looks like they are OK
When I run MIN I get
YtdSalesValue -3490.46   YtdQtyIssued -2200.000   YtdQtySold -427.000

Any idea?


0
 
edwarddoylesrAuthor Commented:
What I am thinking is that there may be a space or other than numeric character in one of these 400 some odd thousand entries.

What would the SQL query be to ask

SELECT "ANYTHING THAT IS NOT A NUMERIC CHARACTER" (YtdSalesValue)
  FROM InvWarehouse

0
 
pcelbaCommented:
If all your columns are decimal then they cannot contain "ANYTHING THAT IS NOT A NUMERIC CHARACTER".

The problem is in values which are too big somewhere.

If PrevYearQtySold column is e.g. decimal(10,3) and its value is possibly calculated as 9901264.000 + 100000.000 then the update must report Arithmetic overflow error converting numeric to data type numeric.

So what is the data type of PrevYtdSalesVal and PrevYearQtySold column?
0
 
edwarddoylesrAuthor Commented:
Right on the money. Two entries were over the max. Thanks for your help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now