Arithmetic overflow error but only on one server

Question

I have a running database in SQL Server 2012 which was running correctly and suddenly I am getting error msg 8115 is coming, but only on one server, and not on others (SQL Server 2008, SQL Server 2019, etc).

The following statement is running perfectly without any error in all servers except the one I'm talking about:

declare @number decimal(10,2);
set @number = '6.001'

On this one server, the error message I'm getting is:

Msg 8115, Level 16, State 7
An arithmetic overflow error occurred while converting from numeric to numeric data type.

Which setting I have to look into in order to fix this issue? Is it related to corrupted master database?

The version where I have the problem is:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Web Edition (64-bit)

While this server hasn't changed, the errors just started happening today.

Unrelated to your issue: Have you considered moving to a newer version? SQL Server 2012 is no longer in support, and if you must use SQL Server 2012 for some reason, please get off 2012 and onto Service Pack 4 + GDR. What host is offering this version in 2020, so I know to steer people away from them? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 17, 2020 at 15:08

Aaron Bertrand · Accepted Answer · 2020-04-17 14:30:49Z

3

No, this isn't corruption.

Sounds like an SSMS session setting or database configuration, not a server-specific one.

To see if it's SSMS, compare these two snippets on any server:

set numeric_roundabort off;
declare @number decimal(10,2);
set @number='6.001';

And:

set numeric_roundabort on;
declare @number decimal(10,2);
set @number='6.001';

Check if this happens in another (new) query window against the same server.

If that window also produces the error, then try changing database context. It may be that someone changed this specific database setting:

SELECT name, is_numeric_roundabort_on FROM sys.databases;

Which you can fix with ALTER DATABASE but you should first determine whether that setting is in place for a reason (we can't help with that - that's a business thing).

edited Apr 17, 2020 at 14:30

answered Apr 17, 2020 at 13:32

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

2 Comments

Kemal AL GAZZAH Over a year ago

Perfect! this fixed my pb, but with numeric_roundabort on and with numeric_roundabort off, I always get is_numeric_roundabort_on=0 when I run this query SELECT name, is_numeric_roundabort_on FROM sys.databases where name ='mydb', anyway my problem is fixed

Aaron Bertrand Over a year ago

@KemalALGAZZAH Right, that was possibly an explanation, not definitely.

Collectives™ on Stack Overflow

Arithmetic overflow error but only on one server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related