SQL server 2008 R2 将数字转换为数字数据类型的算术溢出错误 [英] SQL server 2008 R2 Arithmetic overflow error converting numeric to data type numeric

问题描述

我在 SQL Server 2008 R2 上遇到了一个我无法理解的令人困惑的错误.

I have a confusing error that I can not understand on SQL Server 2008 R2.

但是当我在本地服务器(SQL Server 2008 R2 也是)上尝试相同的请求时，一切正常.

But when I try the same request on a local server (SQL Server 2008 R2 also) everything works fine.

所以这是提出问题的请求:

So here is the request raising the problem:

select cast(cast(1.260 as numeric(13,3)) as numeric(10,2))

我还添加了一些查询的结果，指示每个服务器的环境:

I also added the result of some queries indicating the environment of each server:

在本地服务器上:

---------------------------------------
1.26

(1 row(s) affected)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) 
    Apr 22 2011 19:23:43 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

ARITHABORT
---------------------------------------------------------------------------------------------
1

(1 row(s) affected)

ARITHIGNORE
---------------------------------------------------------------------------------------------
NULL

(1 row(s) affected)

ANSI_WARNINGS
---------------------------------------------------------------------------------------------
1

(1 row(s) affected)

在远程服务器上:

消息 8115，第 16 级，状态 7，第 1 行
将数值转换为数值数据类型时出现算术溢出错误.

Msg 8115, Level 16, State 7, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
    Jun 17 2011 00:54:03 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)
(1 row(s) affected)

ARITHABORT
------------------------------------------------------------------------------------------------------------
1

(1 row(s) affected)

ARITHIGNORE
------------------------------------------------------------------------------------------------------------
NULL

(1 row(s) affected)

ANSI_WARNINGS
------------------------------------------------------------------------------------------------------------
1

(1 row(s) affected)

我的问题是如何重现远程服务器上发生的问题.如您所见，参数 ARITH... 和 ANSI_.. 在两台服务器上都是相同的.SQL Server 上是否有针对此类错误的配置?

My question is how can I reproduce the problem that is occuring on the remote server. As you can see, the parameters ARITH... and ANSI_.. are the same on both servers. Is there any configuration on that kind of errors on the SQL Server?

推荐答案

NUMERIC_ROUNDABORT 选项开启

当 SET NUMERIC_ROUNDABORT 为 ON 时，表达式中出现精度损失后会产生错误.关闭时，精度损失不会生成错误消息，结果会四舍五入到存储结果的列或变量的精度.

When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. When OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

通常这是关闭的，因为当打开索引视图等可能会失败.

Normally this is OFF because when ON indexed views etc can fail.

我从来没有改变过这一点.

I've never changed this, ever.

SET NOCOUNT ON;
GO
PRINT 'ON'
set NUMERIC_ROUNDABORT ON;
select cast(cast(1.260 as numeric(13,3)) as numeric(10,2));
GO
PRINT 'OFF'
set NUMERIC_ROUNDABORT OFF;
select cast(cast(1.260 as numeric(13,3)) as numeric(10,2));
GO

给予

ON

---------------------------------------
Msg 8115, Level 16, State 7, Line 3
Arithmetic overflow error converting numeric to data type numeric.

OFF

---------------------------------------
1.26

这篇关于SQL server 2008 R2 将数字转换为数字数据类型的算术溢出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！