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
but is it a database setting or a query setting, i mean should i set this code before each query executed? — Boomer

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

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)

Arithmetic overflow error converting numeric to data type numeric

1 Answers