Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Overflow exception when reading decimal values from SQL Server

I'm wondering whether this is a bug or if I'm going something wrong.

I'm loading values with a SqlDataReader from a SQL Server 2008 database but under certain circumstances, it fails to convert the SQL values into .net values. (.NET 4.0)

I have traced it down to an test-case which demonstrates the actual problem:

Working example:

"select convert(decimal(38, 19), 260000 ) as test"
rs.GetValue(1);
--> returns 260000 (decimal)

Not working exmaple:

"select convert(decimal(36, 26), 260000 ) as test"

rs.GetValue(1);
--> throws
   System.OverflowException: Conversion overflows.
   at System.Data.SqlClient.SqlBuffer.get_Decimal()
   at System.Data.SqlClient.SqlBuffer.get_Value()
   at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)

I have examined the actual values that the SQL Server retured. They differ that the non-working one uses 4 integers to express the value, the working one only 3.

I did also check the source code with .net Reflector which unveiled that an exception is thrown if the value exists of more then 3 values, but I dont understand the mechanics behind them.

So, I'm wondering whether this is a genuine bug in the .net framework.

like image 947
Chuck Avatar asked Jul 04 '12 09:07

Chuck


People also ask

Why do I get an overflowexception when trying to retrieve decimal values?

The .Net Decimal structure just can't represent the value that is stored in your SQL Server decimal so an OverflowException is thrown. Either you need to manipulate the value to something compatible in the database before you retrieve it or, read the data out in a raw binary or string format and manipulate on the .Net side.

What causes arithmetic overflow error converting numeric to data type numeric?

Arithmetic overflow error converting numeric to data type numeric in SQL Server. And there’s no way to recover from it, and no way to catch it – what do? This seems to be caused by a precision mismatch between the datatypes Decimal in Microsoft SQL Server decimal in Entity Framework (or .NET in general).

Why is my decimal not working in Entity Framework?

This seems to be caused by a precision mismatch between the datatypes Decimal in Microsoft SQL Server decimal in Entity Framework (or .NET in general). Essentially, your numeric data types stored in the SQL Server won’t “fit” in your property in .NET.

Can overflow occur if precision is less than 31?

overflow can occur even if the precision of the result is less than 31. are both 5. In your case, you have DEC (26,3)/ DEC (15,8) * DEC (15,8). For the division, the result of the division is DEC (31,6). (The scale is 6 because you have current precision='D31,6'.)


1 Answers

Its not somthing you are doing wrong, apart from being overly precise perhaps. I don't think its a new problem either.

You could argue its a bug, or just a gap in functionality. The .Net Decimal structure just can't represent the value that is stored in your SQL Server decimal so an OverflowException is thrown.

Either you need to manipulate the value to something compatible in the database before you retrieve it or, read the data out in a raw binary or string format and manipulate on the .Net side.

Alternatively, you could write a new type that handles it.

It's probably simpler just to use a compatible decimal definition in the first place, unless you really need that precision. If you do I'd be interested to know why.

like image 96
Jodrell Avatar answered Oct 08 '22 22:10

Jodrell