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 badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gbn Over a year ago

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

Boomer Over a year ago

but is it a database setting or a query setting, i mean should i set this code before each query executed?

Thomas Over a year ago

@Boomer - Both. You can override it on a query and you can set a default at the database level.

Boomer Over a year ago

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.

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric to data type numeric

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related