Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    The Netherlands

    Question Unanswered: Arithmetic overflow error

    I got the following error when running a SP:

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

    The stupid thing is, that there is no data conversion at all. It's an insert into SLQ server table where data is retrieved from an Oracle View (using ADO DB link). I got 4 other SP's, doing the same thing for resp 4 other tables, which works fine. Those SP won't work. I don't know why. Below I put the table structure, view structure and SP I used:

    HTML Code:
      Contract_No            varchar (20) NOT NULL
      Registration_Date_Time datetime NOT NULL
      AGC                    varchar (4) NOT NULL
      Salesgroup             varchar (4) NOT NULL
      Group_                 varchar (8) NOT NULL
      Activity_Type          varchar (4) NULL
      Type                   char (1) NULL
      Group_Description      varchar (50) NULL
      Stock_Um               varchar (4) NULL
      B_Qty                  numeric(11, 4) NULL
      B_Cost                 numeric(23, 4) NULL
      C_Qty                  numeric(11, 4) NULL
      C_Cost                 numeric(24, 4) NULL
      D_Qty                  numeric(11, 4) NULL
      D_Cost                 numeric(24, 4) NULL 
    Oracele view:
      CONTRACT_NO            VARCHAR2(20)
      AGC                    VARCHAR2(4)
      SALESGROUP             VARCHAR2(4)
      GROUP_                 VARCHAR2(8)
      ACTIVITY_TYPE          VARCHAR2(4)
      TYPE                   CHAR(1)
      STOCK_UM               VARCHAR2(4)
      B_QTY                  NUMBER
      B_COST                 NUMBER
      C_QTY                  NUMBER
      C_COST                 NUMBER
      D_QTY                  NUMBER
      D_COST                 NUMBER
    Stored procedure:
      CREATE PROCEDURE mis_Upload_Contract_Kosten
        @strType varchar(10),
        @strDate varchar(19)
        declare @strInsert as varchar(1000);
        declare @strSelect as varchar(1000);
        declare @strWhere as varchar(1000);
        declare @strSql as varchar(3019);
        SET @strWhere = ''
        SET @strInsert = 'INSERT C_Contract_Kosten (
          ,  AGC
          ,  Salesgroup
          ,  Group_
          ,  Activity_Type
          ,  Type
          ,  Group_Description
          ,  Stock_Um
          ,  B_Qty
          ,  B_Cost
          ,  C_Qty
          ,  C_Cost
          ,  D_Qty
          ,  D_Cost
          ,  Registration_Date_Time)'
      SET @strSelect = '    SELECT gLCK.Contract_No
          ,  gLCK.AGC
          ,  gLCK.Salesgroup
          ,  gLCK.Group_
          ,  gLCK.Activity_Type
          ,  gLCK.Type
          ,  gLCK.Group_Description
          ,  gLCK.Stock_Um
          ,  gLCK.B_Qty
          ,  gLCK.B_Cost
          ,  gLCK.C_Qty
          ,  gLCK.C_Cost
          ,  gLCK.D_Qty
          ,  gLCK.D_Cost
          ,  ' + char(39)  + @strDate + char(39) + '
          WHERE gLCK.Contract_No NOT LIKE ''            IND*''
          AND NOT EXISTS 
          ( SELECT vCC.Contract_No
            FROM V_Contracts_Closed as vCC
            WHERE vCC.Contract_No = gLCK.Contract_No)
          AND EXISTS
          ( SELECT cc.Contract_No
            FROM C_Contracten as cc
            WHERE cc.Registration_Date_Time = ' + char(39)  + @strDate + char(39) + '
            AND cc.Contract_No = gLCK.Contract_No)'
      IF @strType = 'closed'
        SET @strWhere = ' AND NOT(gLCK.Contract_Close_Date IS NULL)'
      IF @strType = 'open'
        SET @strWhere = ' AND gLCK.Contract_Close_Date IS NULL'
      SET @strSql = @strInsert + @strSelect + @strWhere
      EXEC (@strSql)

  2. #2
    Join Date
    May 2004
    The Netherlands
    Well, at least the problem is solved. There was something wrong in the data itself. The x_QTY en x_COST fields contained sometimes a value with 41 numbers (huge list of numbers after the decimal sign). I changed it in the Oracle views, and now it's all working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts