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 Aaron Bertrand · Accepted Answer · 2020-04-17T13:32:30

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).

Arithmetic overflow error but only on one server

1 Answers