SqlClient fails to convert decimal(38,20) to .Net decimal for values within the range of .Net's decimal type

This program

        static void Main(string[] args)
        {
            using (var con = new SqlConnection("server=.;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = new SqlCommand("select cast(4210862852.86 as decimal(38,20))  val", con);
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    rdr.Read();

                    var val = rdr.GetDecimal(0);
                    Console.WriteLine(val);
                }
            }
        }

fails with

System.OverflowException: 'Conversion overflows.'

at SqlBuffer.cs line 254:

                if (StorageType.Decimal == _type)
                {
                    if (_value._numericInfo.data4 != 0 || _value._numericInfo.scale > 28)
                    {
                        throw new OverflowException(SQLResource.ConversionOverflowMessage);
                    }

This value is well within the range of a .NET decimal, and it's a value that the SqlClient can write using a SqlParameter. So it should be converted to .NET decimal.

The byte pattern in SQL and TDS for this value is:

0x261400010000D877FB4DEE8B51699A5005000000

And it looks like SqlBuffer refuses to convert any value with a non-zero value in the last 4 bytes. But a .NET Decimal is stored as a 12-byte integer, along with a sign and a scaling factor.

So the apparent intent is to determine if the SqlDecimal uses a larger-than-12 byte integer and trigger the overflow.

So this conversion has a bug. It looks like the SqlClient is confused on the byte ordering SQL Server is using, and the last 4 bytes of this buffer are not the 4 least-significant bytes of the 16-byte integer embedded in the decimal.

This number requires uses 12 (base-10) decimal digits to store, so it requires fewer than 12 (base-256) bytes to store.

https://docs.microsoft.com/en-us/dotnet/api/system.decimal?redirectedfrom=MSDN&view=netframework-4.8

I've got a debuggable reproduction but I don't really understand what the problem is even though I can see it occurring. The sql decimal requires 16 bytes to represent because that 5 spills into the last uint, and I think you're saying that even though it's 16 bytes in sql the actual value can be represented in a decimal because they use different scales. Is that right? and if so how do you convert from one representation to the other?

I'll ask around for an authoritative layout for the decimal(38,20).

The layout seems fine and I agree with your assertion that the value it is representing is in range of a .net decimal type, so there needs to be some sort of conversion I just don't know that isn't being done or how it would be.

I think the problem is that the scale is excessive for that value. If you lower the scale it'll work so the value itself is fine but forcing that precision and scale makes the TDS representation use 16 bytes to represent it which hits the check that you point out fails.

Ok, I think I know what the bug is.

This
select cast(cast(4210862852.86 as decimal(38,20)) as varbinary(40))
outputs
0x261400010000D877FB4DEE8B51699A5005000000

0x261400 <- metadata (precision=0x26 scale= 0x14, ??=0x00) may not be present in TDS

                01 <- sign byte

                    0000D877FB4DEE8B51699A5005000000 <-16-byte Little Endian Integer

You can see the byte order like this:

set nocount on
go
declare @d decimal(38,0) = 1

while @d <= 999999999999999999999999999
begin
   print cast(@d as varbinary(40))
   set @d = @d * 16
end

outputs

0x2600000101000000
0x2600000110000000
0x2600000100010000
0x2600000100100000
0x2600000100000100
0x2600000100001000
0x2600000100000001
0x2600000100000010
0x260000010000000001000000
0x260000010000000010000000
0x260000010000000000010000
0x260000010000000000100000
0x260000010000000000000100
0x260000010000000000001000
0x260000010000000000000001
0x260000010000000000000010
0x26000001000000000000000001000000
0x26000001000000000000000010000000
0x26000001000000000000000000010000
0x26000001000000000000000000100000
0x26000001000000000000000000000100
0x26000001000000000000000000001000
0x26000001000000000000000000000001

So .Net is failing whenever any of most-significant bytes is non-zero. That is the correct behavior for decimal(38,0) as that would indicate a number larger than can fit in a 12-byte integer. However this 16-byte integer stores the numeric value multiplied by 10^s for decimal(p,s). And for non-zero s, the non-zero most-significant bytes might not represent an overflow value.

The 16-byte integer has to be divided by 10^s to determine if it's out of bounds.

IE the position of the non-zero bytes in the 16-byte integer will change depending on the scale. eg

print cast(cast(4210862852.86 as decimal(38,20)) as varbinary(40))
print cast(cast(4210862852.86 as decimal(38,22)) as varbinary(40))
print cast(cast(4210862852.86 as decimal(38,24)) as varbinary(40))
print cast(cast(4210862852.86 as decimal(38,26)) as varbinary(40))
print cast(cast(4210862852.86 as decimal(38,28)) as varbinary(40))

outputs

0x261400010000D877FB4DEE8B51699A5005000000
0x26160001000060D03A7616A9DA23517C13020000
0x2618000100008065F92EC60C6A01B28F9CCF0000
0x261A0001000000A66B596AFD6C8D882128195100
0x261C0001000000D80CEE8AFD923E5719ADD3AD1F

In other words if the 4 most-significant bytes are 0s, then there can be no overflow. But if the most-significant bytes are non-zero, and the declared scale is high, and some of the least-significant bytes are zero then there is also no overflow.

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

This same exception occurs in .NET Framework and Core. It seems like a fairly basic bug but I'm guessing there is a good reason it hasn't been discovered yet. Priority wise, I'm going to consider this an edge case for now unless there is more information that is occurs in a more general use case.

Fix for decimal conversion overflow bug

@David-Engel

This same exception occurs in .NET Framework and Core. It seems like a fairly basic bug but I'm guessing there is a good reason it hasn't been discovered yet. Priority wise, I'm going to consider this an edge case for now unless there is more information that is occurs in a more general use case.

@David-Engel, I have the same issue, I am trying to retrieve value: 1234567891234.56789 stored in SQL as: 1234567891234.56789000000000000, because of a column data type decimal(35,17), as you can see it does not exceed the limit of the .Net decimal type if you ignore the 0's. Can you recommend an interim solution while this is set as low priority?

As a workaround, use an intermediate conversion to string. Either on SQL Server with a CAST or on the client like this:

var val = Decimal.Parse(rdr.GetSqlDecimal(0).ToString());

@dbrownems

As a workaround, use an intermediate conversion to string. Either on SQL Server with a CAST or on the client like this:

var val = Decimal.Parse(rdr.GetSqlDecimal(0).ToString());

Thank you for the reply @dbrownems, much appreciated. Note that I am using Entity Framework Core 2.1, any suggestion would be great?

Would it be possible to remove or exclude the trailing zeros when the _value._numericInfo.scale exceed 28 in the below extract, in that way you keep your precision there is no rounding issue, and throwing the conversion overflow is more valid?

if (StorageType.Decimal == _type)
{
if (_value._numericInfo.data4 != 0 || _value._numericInfo.scale > 28)
{
throw new OverflowException(SQLResource.ConversionOverflowMessage);
}

I'm hitting the same issue. We have a test which writes a random decimal value to the database, and then ensures that the same value can be retrieved from the database intact. Which is failing intermittently because of this.

Same problem here. A simple entity project generated from a sql server database cannot write decimals from c# back to the database, even when they are both of same type decimal.

Any updates on this? This issue is present since 3 years.

It is on the to-do list this semester.

This error still persist, I have a decimal (38,20) column which is configured in EF like this where Cubage is a decimal property:
entity.Property(e => e.Cubage)
.HasColumnType("decimal(38, 20)");

If I set the property in EF to 1584000000, it fails and 1584000000 is far off the max value of decimal (38,20).

Yep. Not sure this is exactly the same issue, but here's a repro on Microsoft.Data.SqlClient 6.0.2 and 6.1.0-preview1.25120.4:

using var con = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;TrustServerCertificate=true");
con.Open();
using var cmd = con.CreateCommand();
cmd.CommandText = "select @d d";
var p = new SqlParameter("@d", System.Data.SqlDbType.Decimal);
p.Precision = 38;
p.Scale = 20;
p.Value = 1584000000;
cmd.Parameters.Add(p);
try
{
    var s = cmd.ExecuteScalar();
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

System.OverflowException: Conversion overflows.
   at System.Data.SqlTypes.SqlDecimal.ToDecimal()
   at Microsoft.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
   at Microsoft.Data.SqlClient.TdsParser.TDSExecuteRPCAddParameter(TdsParserStateObject stateObj, SqlParameter param, MetaType mt, Byte options, SqlCommand command, Boolean isAnonymous)
   at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Program.<Main>$(String[] args) in C:\Users\david\source\repos\EfTest\EfTest\Program.cs:line 15

After a bunch of research I have found that SqlClient uses Decimal.GetBits and SqlDecimal.ConvertToPrecScale during the conversion in my case. Your case is probably similar. The problem here seems to be that the .NET decimal is perceived by SqlDecimal to only support numbers that can be represented by 96 bits https://github.com/microsoft/referencesource/blob/master/System.Data/System/Data/SQLTypes/SQLDecimal.cs#L509

As such, while SqlDecimal.ConvertToPrecScale would succeed, SqlClients desire to convert the SqlDecimal back into a decimal value again after parsing it with ConvertToPrecScale leads to the Conversion overflow exception.
https://github.com/dotnet/SqlClient/blob/b8948f2e35ea4e0d8959b9528a12bb700ebc4907/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlBuffer.cs#L249

Why? Because when reconverting the value back to a Net decimal, it internally checks if the fourth integer of the return value of Decimal.GetBits and if its higher than 0, the exception will be thrown. https://github.com/microsoft/referencesource/blob/master/System.Data/System/Data/SQLTypes/SQLDecimal.cs#L1135 It will effectively be thrown once the number in SqlDecimal is represented by more than three integers.

A simple way to reproduce the issue
var valueB = SqlDecimal.Parse("1584000000.00000000000000000000");
var testV = SqlDecimal.ConvertToPrecScale(valueB, 38, 20).Value;

To fix this, I have changed all decimal(38,20) to match the precision you can achieve with 96 bit, in other words decimal(29,X)

Navigation Menu

Repository navigation

SqlClient fails to convert decimal(38,20) to .Net decimal for values within the range of .Net's decimal type #95

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SqlClient fails to convert decimal(38,20) to .Net decimal for values within the range of .Net's decimal type #95

Description

Activity

Wraith2 commented on May 11, 2019

dbrownems commented on May 11, 2019

Wraith2 commented on May 11, 2019

Wraith2 commented on May 11, 2019

dbrownems commented on May 12, 2019

divega commented on May 16, 2019

David-Engel commented on May 17, 2019

NathanJonker commented on May 31, 2020

dbrownems commented on Jun 1, 2020

NathanJonker commented on Jun 1, 2020

NathanJonker commented on Jun 2, 2020

branko-d commented on Aug 19, 2020

scidec commented on Apr 13, 2021

klemmchr commented on Jul 6, 2021

DavoudEshtehari commented on Jul 9, 2021

pabnemlig commented on May 22, 2025

dbrownems commented on May 22, 2025

pabnemlig commented on May 22, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions