Arithmetic Overflow Error Converting Numeric to Data Type Numeric

Arithematic Overflow : Converting numeric to data type numeric error

If you're getting an arithmetic overflow error, then it's likely that your percentage calculation is returning at least one record which is greater than 100.0000 (or 100%). Try increasing the decimal's precision to increase the number of digits that can be stored until the error no longer occurs, e.g. DECIMAL(8,4) or greater.

Why SQL Server throws Arithmetic overflow error converting int to data type numeric?

Numeric defines the TOTAL number of digits, and then the number after the decimal.

A numeric(3,2) can only hold up to 9.99.

Arithmetic overflow error converting varchar to data type numeric - SQL Server

Because numeric(18,3) means: 18 digits in total, thereof 3 after the decimal point - so you only get 15 digits before the decimal point.

Entering 18 digits (before the decimal point) will obviously cause an overflow!

If you really need 18 digits before the decimal point, you need to define your column as numeric(21,3)

ALTER TABLE dbo.TableName 
    ALTER COLUMN MinimumValue NUMERIC(21, 3)

For more details, read the fabulous docs

Arithmetic overflow error converting numeric to data type numeric. EF Core

I created a fail-over iterative save for when a batch fails. Since I made my database connection "global" for this class I was inadvertantly running into a problem. I would add the recordset and try to save it (error pops), then try to iterate smaller and smaller batches to discover the offending record(s). The problem was I did not remove the offending recordset before iterating. Therefore, every iteration carried the error condition in the DB connection!

    internal void IterateSave<TModel>(List<TModel> items) where TModel : class
    {
        using (LogContext.PushProperty("Data: Class", nameof(RatBaseCommandHandler)))
        using (LogContext.PushProperty("Data: Method", nameof(IterateSave)))
        using (LogContext.PushProperty("Data: Model", nameof(items)))
        {
            int max = items.Count;
            int skip = 0;
            int take = (max > 20) ? (max / 5) : 1;
            int lastTake = take;
            List<TModel> subItems = new List<TModel>();

            while (skip <= max)
            {
                try
                {
                    subItems = items.Skip(skip).Take(take).ToList();
                    Log.Verbose("Working {Max} | {Take} | {Skip}", max, take, skip);

                    skip += take;
                    _db.Set<TModel>().AddRange(subItems);
                    _db.SaveChanges();
                }
                catch (Exception ex)
                {
/***** Was not removing the faulty record/recordset! *****/
                    _db.Set<TModel>().RemoveRange(subItems);
/***** Was not removing the faulty record/recordset! *****/

                    if (take == 1 && skip < max)
                    {
                        Log.Error(ex, "Error saving specific record in this data batch! {GuiltyRecord}", JsonConvert.SerializeObject(subItems));
                        if (skip >= max - 1)
                        {
                            depth--;
                            return;
                        }
                    }
                    else if (take > 1)
                    {
                        Log.Warning("Something is wrong saving this data batch! {RecordCount}  Running a smaller batch to isolate.", take);
                        IterateSave(subItems);
                    }
                }
            }
        }
    }

With those 2 lines added (commented section in catch) the error literally popped right out!

Error saving specific record in this data batch!
"[{\"block_chain\":\"Ethereum\",\"block_reduction\":\"\",\"block_reward\":\"0\",\"block_time\":0.0,\"consensus_method\":\"\",\"decimals\":18.0,\"difficulty_retarget\":\"\",\"genesis_address\":\"0x3520ba6a529b2504a28eebda47d255db73966694\",\"hash_algorithm\":\"\",\"mineable\":\"False\",\"p2p_port\":0,\"rpc_port\":0,\"token_role\":\"\",\"float\":0.0,\"minted\":60000000000000000000000000.0,\"total_supply\":60000000000000000000000000.0,\"max_supply\":60000000000000000000000000.0,\"wallet\":\"\",\"genesis_timestamp\":0.0}]" Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred
while updating the entries. See the inner exception for details. --->
System.Data.SqlClient.SqlException: Arithmetic overflow error
converting numeric to data type numeric. The statement has been
terminated.

While C# can handle 60000000000000000000000000.0 in a DECIMAL data type, our SQL is defined at DECIMAL(28,6). Because of the 6 digit precision that only leaves space for a 10^22 value.

(It appears that SQL can now handle DECIMAL(38,6). Time to play with column definitions without losing production data.)

Arithmetic overflow error converting numeric to data type numeric in sql

Are you sure that is how you want to get your percentage? Should you move your *100.00 after the division like this?

rextester: http://rextester.com/NNOEH44668

declare @LastMonthDeployCount numeric(7,2) = 10.00;
declare @TotalMachines numeric (7,2) = 100.00;

declare @CurrentPatchDeployPercentage numeric(9,2);
    set @CurrentPatchDeployPercentage = 
      convert(numeric(5,2), 
        ( isnull(@LastMonthDeployCount, 0)
          / 
          isnull(nullif(@TotalMachines, 0), 1.0)
          ) * 100.00
        );

select @CurrentPatchDeployPercentage;

Also, make sure your @CurrentPatchDeployPercentage data type can support the highest number of @LastMonthDeployCount *100.00 for when @TotalMachines = 0 and is changed to null and then changed to 1.

Decimal/Numeric Data Type - MSDN

Precision  Storage bytes
---------  --------------
1 - 9      5
10-19      9
20-28      13
29-38      17

---

Related Topics