Arithmetic overflow error converting numeric to data type numeric

Question

I have executed this query on 2 different databases:

Update table1 set PresencePayFactor = cast(30 as decimal (4,2))/ 30

it is working on one but not on the other. the 2 databases are sql server 2008 R2

it is giving the following error "Arithmetic overflow error converting numeric to data type numeric."

What may be the problem?

have you checked that that column is defined the same way in both databases? — Mat
– Mat, Commented May 13, 2011 at 5:36
but is it a database setting or a query setting, i mean should i set this code before each query executed? — Boomer
– Boomer, Commented May 13, 2011 at 6:56

Thomas · Accepted Answer · 2011-05-13 05:57:46Z

2

Is NUMERIC_ROUNDABORT set differently between the two?

SET NUMERIC_ROUNDABORT OFF
GO
Declare @TestTable Table ( PresencePayFactor decimal(4,2) null )
Insert @TestTable( PresencePayFactor )
Select Cast( 30 As decimal(4,2) ) / 30
GO
-- No error

SET NUMERIC_ROUNDABORT ON
GO
Declare @TestTable Table ( PresencePayFactor decimal(4,2) null )
Insert @TestTable( PresencePayFactor )
Select Cast( 30 As decimal(4,2) ) / 30
-- Arithmetic overflow error converting numeric to data type numeric.

SET NUMERIC_ROUNDABORT (Transact-SQL)

answered May 13, 2011 at 5:57

Thomas

64.8k12 gold badges97 silver badges142 bronze badges

+1 good call. One of those setting you don't think about usually

gbn
– gbn

05/13/2011 06:56:03
Commented May 13, 2011 at 6:56
but is it a database setting or a query setting, i mean should i set this code before each query executed?

Boomer
– Boomer

05/13/2011 07:05:09
Commented May 13, 2011 at 7:05
@Boomer - Both. You can override it on a query and you can set a default at the database level.

Thomas
– Thomas

05/13/2011 16:42:11
Commented May 13, 2011 at 16:42
Hello Thomas. But i executed this script at a database Lvl and executed a CLR script but was still getting this error. It worked only after inserting this script before the execution of my script. anyways it is working now. Thanks again.

Boomer
– Boomer

05/23/2011 08:30:11
Commented May 23, 2011 at 8:30

Add a comment |

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric to data type numeric

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related