Microsoft KB Archive/932288

Knowledge Base

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

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 "FunctionName": 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 "Symptoms" 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

1. In Microsoft Visual Studio 2005, create a Visual C# project by using the SQL Server Project template.
2. Name the project SQLServerProject1.
3. Add a user-defined function to the project.

4. 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);
       }
   };

5. Deploy the project to a database of SQL Server 2005.
6. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.

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

8. 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) .

REFERENCES

For more information about the System.Decimal data type, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/system.decimal.aspx

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005clr KB932288

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.