asked on

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

Pavel Celba

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.

edwarddoylesr

ASKER

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

Pavel Celba

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?

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

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

Start Free Trial

edwarddoylesr

ASKER

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?

edwarddoylesr

ASKER

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

Pavel Celba

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?

edwarddoylesr

ASKER

Right on the money. Two entries were over the max. Thanks for your help.