Following errors can happen when any field in the database is attempted to insert or update larger data of the same type or other data type.
Msg 8115, LEVEL 16, State 2, Line 2 Arithmetic overflow error converting expression TO data type <ANY DataType>
Example is if integer 111111 is attempted to insert in TINYINT data type it will throw above error, as well as if integer 11111 is attempted to insert in VARCHAR(2) data type it will throw above error.
Fix/Solution/Workaround:
1) Verify the inserted/updated value that it is of correct length and data type.
2) If inserted/updated value are correct modify the definition of the table to accommodated new data type length.
Reference : Pinal Dave (https://blog.sqlauthority.com)
33 Comments. Leave new
In my case the fields are numeric with a comma: 99999,99 before change and 99999,9999 after change using a alter table.
I get sqlcode 8115 on it.
Do you now how I can realize this?
Yours truly,
Willem van Loon
I used ‘alter table XXX alter column XXX decimal (5,4) not null’ in SQL Server.
I am inserting into a temp table by doing
select col1,col2,col3,…… into #t
select avg(col1) as ave from #t
I still get this error.
hello sir
i have a table “trndetail” which have three columns
1=id
2=name
3= trndate
when i select a from the table as
select * from trndetail
where trndate=2010-02-01
then out put is blank without any error while
above date is exist in this table
pls tell me how can i do
thnk u
deepak chauhan
Hi Kena,
Query seems ok, It should run if that table exists in same database you run a query.
Could you please post an Error Description, so we can figure it out?
Thanks,
Tejas Shah
Hello,
I’m having this same error but i get the error when running a Stored Procedure. I also noticed that I get the error only for a 10 day range in February. I can run the same stored procedure for all other date ranges this year and last year.
I’m not really sure how to determine the data field length in a Stored procedure, when the data is not getting outputted into a table.
,SUM(CASE WHEN QIV.PSRouteReasonId = 11 THEN DATEDIFF(second,QIV.PSBeginTime,QIV.PSEndTime) ELSE NULL END) AS AvgTakenLastQueue
,SUM(CASE WHEN QIV.PSRouteReasonId = 11 THEN DATEDIFF(second,QIV.QSQBeginTime,QIV.PSEndTime) ELSE NULL END) AS AvgTakenContQueue
Those are the two lines that are giving me the error message.
Any thoughts?
Thank you
10 years of SQL Server from 7 to 2008 and we never had any issues in our INSERT code.
Moving into SQL Server 2008 R2, we immediately encounter Msg. 8115 – Arithmetic overflow error converting tinyint to data type numeric.
But the field being inserted was varchar not numeric !!
for example:
use Source Table
insert into j5_Test_Inserts.dbo.jwjobs
(
job_no
)
select
job_no
from import_jobs
Any ideas?
Thanks,
Msg 8115, Level 16, State 6, Line 6
Arithmetic overflow error converting tinyint to data type numeric.
WHAT WAS THE CHANGE IN SQL SERVER 2008 R2 ??
Dave:
Thanks for your quick reply. I understand your logic perfectly. The problem, is that when the EXACT same tables are used in SQL Server 2008 R1 (10.0.1600.22) there is no problem. This same code works perfectly.
Now just restore the identical tables to SQL Server 2008 R2, and we get the error. It’s consistent it happens repeatedley in R2 (10.50.1600.1) and is does NOT happen in R1.
The data are identical, the code is identical, R1 works great and R2 raises the stated error. How can the data be causing the problem in this case? I don’t see how a .bak Restore from R1 into R2 can cause changes in data.
Paul W.
Lol, I did catch this one:
1> select CONVERT (NUMERIC(7,3),-7.02000 );
2> go
Msg 8115, Level 16, State 7, Server DRAFTMS, Line 1
No idea about this MS SQL %(
Very strange, I got this error in MS SQL SERVER 2008 Express R2:
update ZC10CONGTRINH set TIENVNPSCO = 1234567890 + 1234567890
Whereas no error for this:
update ZC10CONGTRINH set TIENVNPSCO = 1234567890 + 12345678901
Please note that the second amount is bigger, but no error !
TIENVNCO ‘s type is float.
This is real scenario because we use Vietnamse currency
Looking forward to your idea…. Thanks
Can you post the table structure? float can handle big numbers that you specified
Hello,
Msg 8115, Level 16, State 6, Procedure Line 86
Arithmetic overflow error converting varchar to data type numeric.
when i execute this:
Exec REFINE 0,’COTTON’,1.1,100,8
alter PROCEDURE [dbo].[REFINE]
(
@STOREID INT,
@SEARCHKEYWORD VARCHAR(50),
@FROMPRICE numeric(7,2),
@TOPRICE numeric(7,2),
@GARMENTID INT
)
AS
DECLARE @SELECT VARCHAR (5000)
DECLARE @ORDER VARCHAR (255)
DECLARE @Criteria VARCHAR (255)
BEGIN
SET NOCOUNT ON;
SELECT @Select = ‘SELECT product.sku ,
product.name AS ProductName,
”$”+CONVERT(VARCHAR(20), Str((product.price/100.00), 12, 2)) as price,
product.gender as genderID,
CASE product.gender WHEN ”0” Then ”Unisex” WHEN ”1” Then ”Female” Else ”Male” End as gender ,
product.has_pockets as has_pocketsID,
CASE product.has_pockets WHEN ”1” Then ”Yes” Else ”No” End AS has_pockets ,
Fabric_Blend.Fabric_Blend,
product.blendID,
Fabric_Texture.Fabric_Texture,
product.textureID AS textureID,
Sleeve_Length.Sleeve_Length,
product.sleeveID,
Garment_Type.Garment_Type,
product.garmentID,
Brands.brand_name,
product.brandID,
store_product.storeID
FROM product
INNER JOIN
store_product ON product.sku = store_product.sku
INNER JOIN
Fabric_Texture ON product.textureID = Fabric_Texture.TextureID
LEFT OUTER JOIN
Sleeve_Length ON product.sleeveID = Sleeve_Length.SleeveID
INNER JOIN
Garment_Type ON product.garmentID = Garment_Type.GarmentID
INNER JOIN
Brands ON product.brandID = Brands.BrandID
INNER JOIN
Fabric_Blend ON product.blendID = Fabric_Blend.blendID
where store_product.storeID=’ + CONVERT(VARCHAR,@STOREID)
SELECT @ORDER = ‘ order by ProductName’
SELECT @Criteria = ”
BEGIN
IF @SEARCHKEYWORD IS NULL
SELECT @Criteria = NULL
ELSE
IF @GARMENTID = 0
BEGIN
SELECT @Criteria = @Criteria + ‘ AND product.name LIKE ‘ + CHAR(39) + ‘%’ + @SEARCHKEYWORD+’%’ + CHAR(39)
END
ELSE
IF CONVERT(numeric,@TOPRICE) = 0 AND CONVERT(numeric,@FROMPRICE)=0
BEGIN
SELECT @Criteria = @Criteria + ‘ AND product.name LIKE ‘ + CHAR(39) + ‘%’ + @SEARCHKEYWORD+’%’ + CHAR(39) + ‘ AND product.GARMENTID = ‘ + CONVERT(VARCHAR,@GARMENTID)
END
ELSE
IF CONVERT(numeric,@TOPRICE) = 0
BEGIN
SELECT @Criteria = @Criteria + ‘ AND product.name LIKE ‘ + CHAR(39) + ‘%’ + @SEARCHKEYWORD+’%’ + CHAR(39)+ ‘ AND PRODUCT.GARMENTID = ‘ + CONVERT(VARCHAR,@GARMENTID) + ‘ AND SUBSTRING(CONVERT(VARCHAR,PRODUCT.PRICE), 2, LEN(CONVERT(VARCHAR,PRODUCT.PRICE)) – 2) >= ‘ + CONVERT(numeric,@FROMPRICE)
END
ELSE
SELECT @Criteria = @Criteria + ‘ AND product.name LIKE ‘ + CHAR(39) + ‘%’ + @SEARCHKEYWORD+’%’ + CHAR(39)+ ‘ AND PRODUCT.GARMENTID = ‘ + CONVERT(VARCHAR,@GARMENTID) + ‘ AND SUBSTRING(CONVERT(VARCHAR,PRODUCT.PRICE), 2, LEN(CONVERT(VARCHAR,PRODUCT.PRICE)) – 2) between ‘ + CONVERT(numeric,@FROMPRICE) + ‘ AND ‘ + CONVERT(numeric,@TOPRICE)
exec (@Select + @Criteria + @ORDER)
END
END
GO
Hello,
I have a field called id in a table of data type nvarchar(max). when i use this field in stored procedure by applying id!=000111 in where condition,its working fine in sql 2005. but when i run the application its giving
”arithmetic overflow error converting nvarchar to data type numeric” error.
Please help me…
Your application was not recognising it. Use single quotes around the value
we created sqljob but it is failing intermittently……with following error message
Arithmetic overflow error converting expression to data type nvarchar. [SQLSTATE 22003] (Error 8115). The step failed.
please find the code which we have created the job
CREATE procedure [dbo].[auto_create_index]
as
/***********************************************************************
** File : auto_create_index.sql
**
** Name : auto_create_index
**
** Version : v1.1
**
** Desc : this is keep track of index susggestions.
**
**
**
** Called By SQL jobs : SQL MDW: Auto Index Management
**
** Calls :
**
** Uses :
**
** Author: Gunag.
** Date: 12/04/2006
*******************************************************************************
** CHANGE HISTORY
*******************************************************************************
** Date Author Description
** ———- ——— —————————————————
*05/15/2009 v-drajan included sys.databases to avoid running this job during mssales transition [mssops 2-2519638337 ]
*06/01/2009 v-drajan add a condition to apply only for ONLINE DBs [mssops 2-2752950635 ]
******************************************************************************/
— NOTE: This sp will create indexes recommended by the Missing Index DMVs.
—
set nocount on
— required for creating index on ICC/IVs
set ansi_warnings on
set ansi_padding on
set arithabort on
set concat_null_yields_null on
set numeric_roundabort off
declare @exec_stmt nvarchar(6000)
declare @table_name nvarchar(721)
declare @column_name sysname
declare @column_usage varchar(20)
declare @column_id smallint
declare @index_handle int
declare @database_id int
declare @object_id int
— find the top 5 indexes with maximum total improvent
declare ms_cri_tnames cursor local static for
Select Top 5 mid.database_id, mid.object_id, mid.statement as table_name, mig.index_handle as index_handle
from
(
select
(user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs
) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid,
sys.databases mim
where
mid.database_id = mim.database_id
and migs_adv.group_handle = mig.index_group_handle
and mig.index_handle = mid.index_handle
and (mim.user_Access 1 and mim.is_read_only = 0 and mim.state =0)
and migs_adv.index_advantage > 10
order by migs_adv.index_advantage DESC
— create temporary table to store the table names on which we just auto created indexes
create table #tablenametab
( table_name nvarchar(521) collate database_default
)
truncate table #tablenametab
open ms_cri_tnames
fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle
–print @table_name
while (@@fetch_status -1)
begin
— don’t auto create index on same table again
— UNDONE: we may try to filter out local temp table in the future
if (@table_name not in (select table_name from #tablenametab ))
begin
— these are all columns on which we are going to auto create indexes
declare ms_cri_cnames cursor local for
select column_id, quotename(column_name,'[‘), column_usage
from sys.dm_db_missing_index_columns(@index_handle)
— now go over all columns for the index to-be-created and
— construct the create index statement
open ms_cri_cnames
fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage
declare @index_name sysname
declare @include_column_list nvarchar(517)
declare @key_list nvarchar(517)
select @index_name = ‘_MS_Sys’
select @key_list = ”
select @include_column_list = ”
declare @num_keys smallint
declare @num_include_columns smallint
select @num_keys = 0
select @num_include_columns = 0
while @@fetch_status >= 0
begin
— construct index name, key list and include column list during the loop
— Index Name in the format: _MS_Sys_colid1_colid2_…_colidn
if (@column_usage = ‘INCLUDE’)
begin
if (@num_include_columns = 0)
select @include_column_list = @column_name
else
select @include_column_list = @include_column_list + ‘, ‘ +@column_name
select @num_include_columns = @num_include_columns + 1
end
else
begin
if (@num_keys = 0)
select @key_list = @column_name
else
select @key_list = @key_list + ‘, ‘ +@column_name
select @num_keys = @num_keys + 1
select @index_name = @index_name + ‘_’+cast ( @column_id as nvarchar(10))
end
fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage
end
close ms_cri_cnames
deallocate ms_cri_cnames
–print @index_name
–print @table_name
–print @key_list
–print @include_column_list
— construct create index statement
— “CREATE INDEX @INDEX_NAME ON @TABLE_NAME (KEY_NAME1, KEY_NAME2, …) INCLUDE (INCLUDE_COL_NAME1, INCLUDE_COL_NAME2, …) WITH (ONLINE = ON)” (Note: for recommendation mode, we don’t use online option)
if (@num_include_columns > 0)
select @exec_stmt = ‘CREATE INDEX ‘ + @index_name + ‘ ON ‘ + @table_name + ‘(‘ + @key_list + ‘) INCLUDE (‘+ @include_column_list + ‘)’– WITH (ONLINE = ON)’
else
select @exec_stmt = ‘CREATE INDEX ‘ + @index_name + ‘ ON ‘ + @table_name + ‘(‘ + @key_list + ‘)’– WITH (ONLINE = ON)’
–print @exec_stmt
declare @id int
declare @create_date datetime
DECLARE @result int;
BEGIN TRANSACTION xAddCreateIdxRecommendation
EXEC @result = dbo.add_recommendation @exec_stmt, ‘CI’, @id OUT
if (@result 10)
EXEC dbo.add_recommendation_details_index @id, @database_id, @object_id
DECLARE @Error int
SET @Error = @@ERROR
IF @Error 0
BEGIN
ROLLBACK TRANSACTION xAddCreateIdxRecommendation
RETURN @Error
END
COMMIT TRANSACTION xAddCreateIdxRecommendation
–EXEC (@exec_stmt)
— insert the table name into #tablenametab
insert into #tablenametab values (@table_name)
end
fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle
end
deallocate ms_cri_tnames
return(0) — auto_create_index
——————-
Hi,
I have data in a temp table of data type VARCHAR(50),
I need to insert this data into a table say TableA, of data type Float
1. Need to check data conversion errors
2. Arithmetic overflow errors.
By ignoring this errors, need to insert (Merge) data into TableA , without stopping the batch and need to raise error message one time to the UI
How to handle this?
Thanks man,
It worked out for me.
Similar request… beginner SQL, different query. I’m getting 8114, Level 16, state 5, line 1:
USE KudlerFineFoodsDB;
GO
/* DROP TABLE Employee;
**GO
*/
CREATE TABLE Employee
(EmployeeID int IDENTITY NOT NULL,
CONSTRAINT PKEmployeeID PRIMARY KEY (EmployeeID),
LastName varchar (20) NOT NULL,
FirstName varchar (20) NOT NULL,
[Address] varchar (60) NULL,
City varchar (20) NULL,
[State] varchar (2) NULL,
AreaCode varchar (3) NULL,
Phone varchar (8) NULL,
HireDate date NOT NULL,
HourlyRate decimal (8,2) NULL,
Salary decimal (10,2) NOT NULL,
Gender varchar (1) NOT NULL,
AGE varchar (2) NOT NULL,
JobTitle varchar (50) NOT NULL,
CONSTRAINT FKEmployeeID FOREIGN KEY (JobTitle)
REFERENCES JobTitle (JobTitle)
);
GO
INSERT INTO Employee (LastName, FirstName, [Address], City, [State], AreaCode, Phone, HireDate, HourlyRate, Salary, Gender, Age, JobTitle)
VALUES (‘Edelman’, ‘Glenn’, ‘175 Bishops Lane’, ‘La Jolla’, ‘CA’, ‘619’, ‘5550199’, ‘20031007’, ‘10.75’, ‘$21500.00’, ‘M’, ’64’, ‘Cashier’),
(‘McMullen’, ‘Eric’, ‘763 Church Street’, ‘Lemon Grove’, ‘CA’, ‘619’, ‘5550135’, ‘20000601’, ‘6.75’, ‘$13500.00’, ‘M’, ’34’, ‘Bagger’),
(‘Slentz’, ‘Raj’, ‘123 Torrey Drive’, ‘North Clairmont’, ‘CA’, ‘619’, ‘5550123’, ‘20031007’, ‘NULL’, ‘48000.00’, ‘M’, ’64’, ‘Assistant Manager’),
(‘Broun’, ‘Erin’, ‘2045 Parkway Apt 2B’, ‘Encinitas’, ‘CA’, ‘760’, ‘5550100’, ‘20030312’, ‘6.75’, ‘$10530.00’, ‘F’, ’24’, ‘Bagger’),
(‘Carpenter’, ‘Donald’, ‘927 Second Street’, ‘Encinitas’, ‘CA’, ‘619’, ‘5550154’, ‘20031101’, ‘7.50’, ‘$15000.00’, ‘M’, ’18’, ‘Stocker’),
(‘Esquivez’, ‘David’, ‘10983 North Coast Hwy Apt 902’, ‘Encinitas’, ‘CA’, ‘760’, ‘5550108’, ‘20030715’, ‘9.25’, ‘$18500.00’, ‘M’, ’25’, ‘Retail Assistant Butchers & Seafood Specialists’),
(‘Sharp’, ‘Nancy’, ‘10793 Monticino Road’, ‘Ramona’, ‘CA’, ‘858’, ‘5550135’, ‘20030712’, ‘10.50’, ‘$21000.00’, ‘F’, ’24’, ‘Cashier’)
;
GO
SELECT *
FROM Employee
;
GO
/* When I isolate each section, they run fine until this last insert. That is where I hit the error. It should seem simple, but I’m just not seeing it. There’s one field that needs to either be blank or null. I’ve tried it both ways. I’ve also tried the entire insert with a $ value in that field. I still get the error. I have numerous other queries to run for class, all due Monday night, but can’t run them until I get past this error. Help?
*/
Hello,
i am executing the below code and sql server 2008 R2 raises the same error.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
select pa11.CustCol_1 CustCol_1,
max(case when a12.Flagpop=1 then ‘Vivo Random Sample’ when a12.Flagpop=2
then ‘ACE matched sample’ end) CustCol_2,
count(*) CUSTOMERCOUNT
from (select distinct a12.Flagpop CustCol_1,
a11.id_lnha WJXBFS1
from ACE_States a11
join Random_and_ACE_Match a12
on (a11.id_lnha = a12.id_lnha)
where a11.id_lnha is not null
) pa11
join Random_and_ACE_Match a12
on (pa11.CustCol_1 = a12.Flagpop)
group by pa11.CustCol_1
any idea why the issue comes
Double click on the error message and see where it points out to
If i remove the last join (outside the subquery) and make comment custcol_2 statement from select clause, then query shows no issue and result is displayed
Actually it is showing the same error if i use join on any other column than id_lnha. although other columns have same datatype as id_lnha, they all are showing this error if i use them in join.
please suggest.
Thanks in advance.
declare @variable varchar(10)
set @declare = 100.00
Expense has been declared numeric(18,2) in table
while checking
convert(varchar(100),Expense) = @variable in where clause
Error:..Arithmetic overflow error converting varchar to data type numeric.
Hi Pinal,
In my case, there are three columns ExchangeRate, CurrencyAmount and Amount. All these column having data type as Numeric(30,12). I am using following formula to calculate amount in Euro but it throws 8115 Error.
@Amount = @CurrencyAmount * (@ExchangeRate / @EuroExchangeRate)
Msg 8115, Level 16, State 1, Line 22
Arithmetic overflow error converting numeric to data type numeric.
How can I do this? As per my understanding it’s due to maximum allowed precision.
Thanks in advance.
Hello, Sir i want guidance of how to recover deleted records from database table in Sql server i’ve refered raresql.com but i could’nt understood the code