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:
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
- 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.
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
Run the following statements to reproduce the problem:
SELECT dbo.[myfunc1](123456789012345.123456789012345) AS CLR_DECIMAL GO
You receive the following error message:
REFERENCES
For more information about the System.Decimal data type, visit the following Microsoft Developer Network (MSDN) Web site:
Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005clr KB932288