Home:ALL Converter>SQL server 2008 R2 Arithmetic overflow error converting numeric to data type numeric

SQL server 2008 R2 Arithmetic overflow error converting numeric to data type numeric

Ask Time:2013-06-28T17:07:35         Author:enenkey

Json Formatter

I have a confusing error that I can not understand on 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:

On the local 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)

On the remote server:

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)

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?

Author:enenkey,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/17361097/sql-server-2008-r2-arithmetic-overflow-error-converting-numeric-to-data-type-num
yy