- Remove From My Forums
-
Question
-
I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I’m having trouble getting the Publication Wizard to create the Publication. Sample table definition that I’m replicating:
USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GOWhen the wizard gets to the step where it is creating the publication, I get the following error message:
Arithmetic overflow error converting expression to data type bigint. Changed database context to ‘dbPSMAssist_Development’. (Microsoft SQL Server, Error: 8115).
I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.
Thanks in advance …
David L. Collison
Any day above ground is a good day.
Answers
-
Greg …
This has to be due to the size of my key on the file. I resized the field ID to NUMERIC(12,0) and now the replication wizard completes the setup properly.
I would have anticipated the replication engine to utilize the definition — obviously it doesn’t like big numbers. Ok, I know it will take a long time to create that many records to worry about filling up the key at 12 digits let alone 19, but I like to plan ahead.
Have a good one!
David L. Collison
Any day above ground is a good day!
Question
Arithmetic overflow error converting expression to data type bigint» error from data collector.
Answer
Contents
Problem
«Arithmetic overflow error converting expression to data type bigint» error from data collector.
An error has occurred: «Error aggregating canister statistics for server: 1 — Error occurred while aggregating PATH_STAT: Error occurred while executing the non-query command: Arithmetic overflow error converting expression to data type bigint».? The key message is: «Arithmetic overflow error converting expression to data type bigint».
The statement has been terminated. — SQL: pr_AggregateRS_PATH
Solution
In some cases the data collected by the data collection process results in numbers too large to match the maximum calculation allowed by SQL Server.? In this case the referenced data requires purging from the SQL Server database. With the calculations no longer requiring completion things should resume with normal functionality.? This is usually caused by a time problem between your Canister or Capture Servers.? The system times on both require synchronization.
Article Reference
00001281
Applies to version(s):? 8.0;8.1;8.2;8.3;8.4
«
[{«Business Unit»:{«code»:»BU055″,»label»:»Cognitive Applications»},»Product»:{«code»:»SSERNK»,»label»:»Tealeaf Customer Experience»},»Component»:»»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»All Versions»,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}}]
insert maximum value into ‘bigint’ column fails — «Arithmetic overflow error converting expression to data type bigint»
from the test error log:
CREATE TABLE types_table (id int identity, col2 bigint)
INSERT INTO types_table (col2) VALUES (null)
INSERT INTO types_table (col2) VALUES (-9223372036854776000)
SELECT * FROM types_table ORDER BY id
assert.js:10444: Uncaught Error: 22003: [Microsoft][SQL Server Native Client 11.
0][SQL Server]Arithmetic overflow error converting expression to data type bigin
t.
Minimum value for bigint column is -9223372036854775808. The value you given is less (greater by modulo), so this is expected behavior as for me.
The same is returned from SQL Management Studio
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type bigint.
Ha. I hadn’t noticed that. Thanks! I will close the bug.
wread
removed their assignment
Apr 1, 2014
With SQL Server, how to avoid this error : “Arithmetic overflow error converting expression to data type int”. Let’s insert some data into a table using a stored procedure for example. Facing this SQL Server error message can be disturbing. Even if SQL conversion errors are classical ones, finding the solution and apply the industry best practices to avoid them is very useful.
In this case, the stored procedure executing the query generates some dynamic T-SQL code. Then it executes the code and insert data from various tables into one single target table. It’s a typical SQL Server int arithmetic overflow conversion error.
How to avoid the SQL Server Arithmetic overflow error?
While running the same piece of T-SQL code independently, that is outside the stored procedure, directly into the SQL Server Management Studio windows, no error is faced. But when integrating the same code into the stored procedure, this error is thrown:
Msg 50000, Level 16, State 2, Procedure MyStoredProcedure, Line 123
Arithmetic overflow error converting expression to data type int.
After analysis, the conversion error message didn’t come from the SELECT clause. The SQL INSERT clause from the same T-SQL stored procedure is throwing the error. The root cause is the the insertion of a big integer value into the table. For integer Data Type in SQL Server, the number must be between -2^31 (-2 to the power of 31) and 2^31 (2 to the power of 31).
Namely the range for the INTEGER type is exactly between -2,147,483,648 and 2,147,483,648.
Use another data type to avoid the conversion error
The solution to avoid Arithmetic overflow error converting expression is to use a bigger data type. The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.
Please note that in this case the conversion error is because the integer number is too big. It can also be an insertion of a text value into an integer field. Check out the int, bigint, smallintand tinyint official documentation for SQL Server.
Other classical conversion questions
This article shows how to simply avoid an Arithmetic overflow error converting expression to int in SQL Server. It happens when inserting a bigger number in an integer data type. Check out more classical SQL Server errors like the insert of a duplicate key.
What is a SQL Server arithmetic overflow error?
An arithmetic overflow error is an error faced when converting from one SQL Server data type to another one. For example from a numeric figure to an integer because the integer type can handle smaller figures compared to the numeric type.
What’s the biggest number for a SQL Server INTEGER?
The biggest number for the SQL Server integer data type is from -2 to the power of 31 till 2 to the power of 31. It means from -2^31 to 2^31 which is exactly this range: from -2,147,483,648 to 2,147,483,648. To avoid conversion error, always consider the maximum value for the data and size the database and the data types accordingly.
What’s the difference between INT and BIGINT?
A bigint is an integer, i.e. a number without decimals, and in SQL Server it can go from -2^63 that is exactly -9,223,372,036,854,775,808 to 2^63-1 which is 9,223,372,036,854,775,807 and is using 8 Bytes in storage.
While an INTEGER is from -2^31 which equals -2,147,483,648 to 2^31-1 which is exactly 2,147,483,647. An INTEGER data is stored on 4 Bytes.
How to avoid conversion errors with SQL Server?
One good practice to avoid conversion errors during is to put in place controls for the inputs. And moreover make sure to control the values inserted in the database. For example, users can only enter valid dates in a date field.
Check out how to manage time difference in hours minutes and also seconds with T-SQL.
If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting expression to data type int in SQL Server, it could be that you’re performing a calculation that results in an out of range value.
This can happen when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type.
Example of the Error
Here’s an example of code that produces the error:
SELECT SUM(bank_balance)
FROM accounts;Result:
Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.
In this case I used the SUM() function to get the sum of the bank_balance column, which has a data type of int.
The error occurred because the result of the calculation is outside the range of the int data type.
Here’s all the data in my table:
SELECT bank_balance
FROM accounts;Result:
+----------------+ | bank_balance | |----------------| | 1300000000 | | 1200000000 | | 800500000 | +----------------+
Those are some big bank balances… and adding the three of them results in a larger number than an int can handle (the int range is -2,147,483,648 to 2,147,483,647).
The Solution
We can deal with this error by converting the int column to a bigint when we run the query:
SELECT SUM(CAST(bank_balance AS bigint))
FROM Accounts;Result:
3300500000
This time it worked.
You could also change the data type of the actual column for a more permanent solution.
In case you’re wondering, the bigint range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Same Error in Different Scenarios
The same error (Msg 8115) can also occur (with a slightly different error message) when you try to explicitly convert between data types and the original value is outside the range of the new type. See Fix “Arithmetic overflow error converting int to data type numeric” in SQL Server to fix this.
The same error (Msg 8115) can also occur (with a slightly different error message) when you try to insert data into a table when its IDENTITY column has reached its data type’s limit. See Fix: “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server for how to fix this.