SQL SERVER – FIX : Error : msg 8115, Level 16, State 2, Line 2 – Arithmetic overflow error converting expression to data type

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 (http://blog.SQLAuthority.com)

About these ads

30 thoughts on “SQL SERVER – FIX : Error : msg 8115, Level 16, State 2, Line 2 – Arithmetic overflow error converting expression to data type

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

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

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

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

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

  6. 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 ??

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

  8. 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 %(

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

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

  11. 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…

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

    ——————-

  13. 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?

  14. 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?
    */

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

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

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

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

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

  19. Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.
    Exception ‘SqlException’: Sql execution failed. Error 8115, Level 16, State 1, Procedure ManagedEntityChange, Line 237, Message: Arithmetic overflow error converting IDENTITY to data type int.

    One or more workflows were affected by this.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.Synchronization.ManagedEntity
    Instance name: a31e6956-92a1-41cb-93aa-bc6cbc9e6f65
    Instance ID: {DFF19BC1-EF79-8E0B-B9AB-86061F88DE11}
    Management group: SCOM2012

  20. Hi Pinal,
    I have a below scenario,

    DECLARE @zero_num numeric(5,4)
    SET @zero_num = 0.00

    SELECT colA,colB,@Zero_num as ratio ,colC into #tmp
    FROM table
    UPDATE #tmp
    SET ratio = colA/ colb WHERE colC 0

    when we divide colA and ColB and assign to ratio columm iam getting overflow error because the output of the division was 248.9787565454656 .

    So can you please suggest a work around.Iam using sql server 2008 R2.Thanks in advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s