none
Arithmetic overflow error converting numeric to data type numeric. RRS feed

  • Question

  • I'm running Microsoft SQL SMS 2005 on my pc with Microsoft SQL Server Enterprise Edition (64-bit) installed on the server.

    Every night a stored proc is run to update a table on the database.

    I have recently encoutered very unusual situation whereby I receive the following error message:

    Msg 8115, Level 16, State 8, Line 6
    Arithmetic overflow error converting numeric to data type numeric.
    The statement has been terminated.

    The stored proc truncates the existing table and then inserts updated information, this usually gives no problems. The updated information comes from tables that are sourced from other databases that contains pricing info maintained by users on wide network. There are contraints in place that prevent funny prices from being captured or entering the summary tables stored on my local database.

    A table is dropped if it exists and a new one is created, then the INSERT statement is run to update the table. I have commented out columns which I beleive are not the cause of the problem, mostly string valued fields.

    Then I try to run the INSERT for the columns Scheme_Abbreviation, Twelve price columns for each month, and a Retail_Price column.

    The source columns in the summary tables have the fields defined as:
    vSO.Scheme_Abbreviation - VARCHAR(3) | This is not the problem field
    OccD."Month" - DECIMAL(18,0) NULL | This is possibly part the problem field
    vDL.Retail_Price - NUMERIC(8,2) NULL | This is part of the problem

    Retail Price is defined in the table created in the stored proc as NUMERIC(10,2) NULL


    There are strange cases when I get the error message an when I don't:

    1. When I run the query for all 14 fields (see below) then I get the error message.
    2. When I exclude Retail_Price and include the other 13 fields, I don't get an error message
    3. When I exclude the twelve month fields and include the two others, I don't get an error message

    It seems for any combination of 8 month fields along with the Retail_Price field yields the error. Why? I don't know. What I do know is that excluding Retail_Price from the insert prevents the error, but including Retail_Price does not necessarily yield the error, which makes me doubt whether the problem lies with the data in the tables?

    The query I run is as follows:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO

    IF EXISTS(SELECT OBJECT_ID('tempdb..#AfA_CACostMeds')) DROP TABLE #AfA_CACostMeds

    CREATE TABLE #AfA_CACostMeds (
    [App_ID] INT NOT NULL,
    [Scheme_Abbreviation] VARCHAR(3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [Scheme_Option_ID] INT NOT NULL,
    [MemberNumber] VARCHAR(18) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [DependantCode] VARCHAR(2) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [RegisteredDate] DATETIME NOT NULL,
    [Status] VARCHAR(45) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [EventCode] VARCHAR(5) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [EventDate] DATETIME NOT NULL,
    [OD_ID] INT NOT NULL,
    [OccurrenceID] INT NOT NULL,
    [DrugName] VARCHAR(40) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [ATC_Code] VARCHAR(9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [ATC_Number] TINYINT NULL,
    [Nappi] VARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [NappiSuffix] VARCHAR(3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [Schedule] INT NULL,
    [Strength] VARCHAR(15) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [Quantity] REAL NOT NULL,
    [Dosage] VARCHAR(5) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [Manufacturer] VARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [GOB] VARCHAR(1) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [DateCommence] DATETIME NULL,
    [DateEnd] DATETIME NULL,
    [CurrentlyAuthorised] BIT NOT NULL,
    [CurrentDrug] BIT NOT NULL,
    [Replaced] TINYINT NULL,
    [DateAuthorise] DATETIME NULL,
    [Jan] DECIMAL(18, 0) NULL,
    [Feb] DECIMAL(18, 0) NULL,
    [Mar] DECIMAL(18, 0) NULL,
    [Apr] DECIMAL(18, 0) NULL,
    [May] DECIMAL(18, 0) NULL,
    [Jun] DECIMAL(18, 0) NULL,
    [Jul] DECIMAL(18, 0) NULL,
    [Aug] DECIMAL(18, 0) NULL,
    [Sep] DECIMAL(18, 0) NULL,
    [Oct] DECIMAL(18, 0) NULL,
    [Nov] DECIMAL(18, 0) NULL,
    [Dec] DECIMAL(18, 0) NULL,
    [chargeable_seq] INT NULL,
    [package_seq] INT NULL,
    [price_type] VARCHAR(20) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
    [Retail_Price] NUMERIC(10, 2) NOT NULL,
    [SE_Price] NUMERIC(10, 2) NULL,
    [EXMAN_Price] NUMERIC(10, 2) NULL,
    [RECOM_Price] NUMERIC(10, 2) NULL,
    [Annual_Quantity] DECIMAL(18, 0) NULL,
    [Auth_Mths] INT NULL,
    [Jan_Price] NUMERIC(10, 2) NULL,
    [Feb_Price] NUMERIC(10, 2) NULL,
    [Mar_Price] NUMERIC(10, 2) NULL,
    [Apr_Price] NUMERIC(10, 2) NULL,
    [May_Price] NUMERIC(10, 2) NULL,
    [Jun_Price] NUMERIC(10, 2) NULL,
    [Jul_Price] NUMERIC(10, 2) NULL,
    [Aug_Price] NUMERIC(10, 2) NULL,
    [Sep_Price] NUMERIC(10, 2) NULL,
    [Oct_Price] NUMERIC(10, 2) NULL,
    [Nov_Price] NUMERIC(10, 2) NULL,
    [Dec_Price] NUMERIC(10, 2) NULL,
    [Annual_Price] NUMERIC(10, 2) NULL,
    [RefNappiCode] VARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [RefTradeName] VARCHAR(40) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [RefStrength] VARCHAR(60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
    [RefPackSize] NUMERIC(7, 2) NULL,
    [RefRetailPrice] NUMERIC(10, 2) NULL,
    [CompRetailPrice] NUMERIC(10, 2) NULL,
    [JanRefPrice] NUMERIC(10, 2) NULL,
    [FebRefPrice] NUMERIC(10, 2) NULL,
    [MarRefPrice] NUMERIC(10, 2) NULL,
    [AprRefPrice] NUMERIC(10, 2) NULL,
    [MayRefPrice] NUMERIC(10, 2) NULL,
    [JunRefPrice] NUMERIC(10, 2) NULL,
    [JulRefPrice] NUMERIC(10, 2) NULL,
    [AugRefPrice] NUMERIC(10, 2) NULL,
    [SepRefPrice] NUMERIC(10, 2) NULL,
    [OctRefPrice] NUMERIC(10, 2) NULL,
    [NovRefPrice] NUMERIC(10, 2) NULL,
    [DecRefPrice] NUMERIC(10, 2) NULL,
    [AnnualRefPrice] NUMERIC(10, 2) NULL
    )

    GO
    SET ANSI_PADDING OFF

    INSERT INTO #AfA_CACostMeds(
    -- App_ID,
    Scheme_Abbreviation,
    -- omitted columns
    Jan,
    Feb,
    Mar,
    Apr,
    May,
    Jun,
    Jul,
    Aug,
    Sep,
    Oct,
    Nov,
    [Dec],
    -- omitted columns
    Retail_Price
    )

    SELECT
    -- App.[ID],
    vSO.Scheme_Abbreviation,
    -- omitted columns
    OccD.Jan,
    OccD.Feb,
    OccD.Mar,
    OccD.Apr,
    OccD.May,
    OccD.Jun,
    OccD.Jul,
    OccD.Aug,
    OccD.Sep,
    OccD.Oct,
    OccD.Nov,
    OccD.[Dec],
    -- omitted columns
    vDL.Retail_Price

    FROM BatchDMS..t_Application AS App
    INNER JOIN BatchDMS..t_Application_Program AS AppP ON App.[ID] = AppP.ApplicationID
    INNER JOIN BatchDMS..t_Application_Status AS AppS ON AppP.ApplicationStatusID = AppS.[ID]
    INNER JOIN LookupAfA..v_Scheme_Options AS vSO ON App.Scheme_Option_ID = vSO.Scheme_Option_ID
    INNER JOIN BatchDMS..t_Occurrence AS Occ ON App.[ID] = Occ.ApplicationID
    INNER JOIN BatchDMS..t_Occurrence_Drugs AS OccD ON Occ.[ID] = OccD.OccurrenceID
    INNER JOIN BatchDMS..t_Drug_List AS vDL ON OccD.Nappi = vDL.Nappi_Code AND OccD.NappiSuffix = vDL.Pack_ID

    WHERE AppP.ProgramID = 1
    AND AppP.ApplicationStatusID = 1
    AND OccD.CurrentlyAuthorised = 1
    AND OccD.CurrentDrug = 1
    AND OccD.Captured_in_error = 0
    AND CONVERT( VARCHAR(8), OccD.DateCommence, 112) < CONVERT( VARCHAR(8), ISNULL( OccD.DateEnd, '01 Jan 2100'), 112)

    I am perplexed and don't know why SQL behaves like this.

    Any help is appreciated.

    Thursday, July 16, 2009 11:46 AM

All replies

  • convert all the decimal and numeric to Numeric(10,2) and try.

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 16, 2009 12:26 PM
  • <<vDL.Retail_Price - NUMERIC(8,2) NULL | This is part of the problem
    <<Retail Price is defined in the table created in the stored proc as NUMERIC(10,2) NULL

    You should make your source and destination data types match.  As it stands you are able to put larger values into the sp table than in your destination, thus when you try to insert, the retail value will not fit.  Below is a small scale sample:  You can change @t amt to match that of the sp_table and it will work.

    DECLARE @t TABLE(
    id INT,
    amt NUMERIC(8,2)
    )
    
    DECLARE @sp_table TABLE(
    id INT,
    amt NUMERIC(10,2)
    )
    
    INSERT INTO @sp_table (
    	[id],
    	[amt]
    ) VALUES (1,12345689.50 ) 
    
    INSERT INTO @t
    SELECT * FROM @sp_table

    http://jahaines.blogspot.com/
    Thursday, July 16, 2009 2:17 PM
    Moderator