Microsoft KB Archive/932288

= Error message when you use a number that is more than 28 in the common language runtime for SQL Server 2005: &quot;System.OverflowException: Conversion overflows&quot; =

Article ID: 932288

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Express Edition with Advanced Services

-



SYMPTOMS
When you use the SqlDecimal data type in the common language runtime (CLR) for Microsoft SQL Server 2005, the data type is converted to the System.Decimal data type. When you use a number that is more than 28 in the CLR for SQL Server, you receive the following error message:

Msg 6522, Level 16, State 2, Line 3

A .NET Framework error occurred during execution of user-defined routine or aggregate &quot; &quot;: System.OverflowException: Conversion overflows.



CAUSE
This issue occurs because the precision is different for the SqlDecimal data type and the System.Decimal data type.

In the CLR for SQL Server, the SqlDecimal data type has a precision of 38 in the System.Data.SqlTypes namespace.

The CLR for the Microsoft .NET Framework is the native CLR equivalent to the CLR for SQL Server. In the CLR for the .NET Framework, the System.Decimal data type has a precision of 28.

When you use a number than is more than 28, you receive the error message that is mentioned in the &quot;Symptoms&quot; section.



RESOLUTION
To resolve this issue, do not use a number that exceeds the precision limit of 28.



STATUS
This behavior is by design.



MORE INFORMATION
When you declare the scale of a number, be careful that the length of the following parts of the number does not exceed 28:
 * The integral part
 * The scale of the number

For example, if you declare a number that has a precision of 28 and a scale of 15, the length of the integral part of the number cannot exceed 13.

Additionally, the scale is always appended to the number as trailing zeros. For example, the number 1234567890123.123456789 is actually 1234567890123.123456789000000.

Steps to reproduce the behavior
 In Microsoft Visual Studio 2005, create a Visual C# project by using the SQL Server Project template. Name the project SQLServerProject1. Add a user-defined function to the project.  Replace the code of the newly created user-defined function with the following code example. using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {   [Microsoft.SqlServer.Server.SqlFunction] public static SqlDecimal overFlowRepro(SqlDecimal a)   { if (a.IsNull) return SqlDecimal.Null; return new SqlDecimal(a.Value * 1); } };  Deploy the project to a database of SQL Server 2005. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.</li>  Run the following statements against the database. CREATE FUNCTION [dbo].[myfunc1](@p_num1 numeric(38,18)) RETURNS NUMERIC(38,18) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLServerProject1].[UserDefinedFunctions].overFlowRepro GO

GRANT EXECUTE ON [dbo].[myfunc1] TO PUBLIC GO

sp_configure 'clr enabled', 1 GO

reconfigure GO </li>  Run the following statements to reproduce the problem: SELECT dbo.[myfunc1](123456789012345.123456789012345) AS CLR_DECIMAL GO You receive the following error message:

Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'myfunc1': System.OverflowException: Conversion overflows.

System.OverflowException:

at System.Data.SqlTypes.SqlDecimal.ToDecimal

at System.Data.SqlTypes.SqlDecimal.get_Value

at UserDefinedFunctions.overFlowRepro(SqlDecimal a).

</li></ol>

<div class="references_section">