none
SSMA - Data Migration of a table from Oracle to SQL Server yields "Errors: Arithmetic operation resulted in an overflow." RRS feed

  • Question

  • Hello,

    Below is the datatype mapping for Oracle and SQL Server for the table.

    ORACLE:

    SQL> desc PMIS.EMP_ACC_HISTORY
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMP_ID                                    NOT NULL VARCHAR2(5)
     PROP_NO                                   NOT NULL VARCHAR2(30)
     MR_NO                                     NOT NULL VARCHAR2(15)
     CM_NO                                              VARCHAR2(15)
     REMARKS                                            VARCHAR2(2000)
     USER_ID                                   NOT NULL VARCHAR2(15)
     L_UPDATE                                  NOT NULL DATE
     ACC_DATE                                           DATE
     ACC_TAG                                   NOT NULL NUMBER(38)
     XLS_REMARKS                                        VARCHAR2(2000)
     ACKNOWLEDGE                                        NUMBER(38)
     LUP_BY                                             VARCHAR2(15)
     YEAR_BACK                                          VARCHAR2(4)
     LUP_DATE                                           DATE

    SQL SERVER:

    EXEC SP_COLUMNS EMP_ACC_HISTORY
    GO

    COLUMN_NAME TYPE_NAME PRECISION LENGTH
    EMP_ID varchar 5 5
    PROP_NO varchar 30 30
    MR_NO varchar 15 15
    CM_NO varchar 15 15
    REMARKS varchar 2000 2000
    USER_ID varchar 15 15
    L_UPDATE datetime2 19 38
    ACC_DATE datetime2 19 38
    ACC_TAG numeric 38 40
    XLS_REMARKS varchar 2000 2000
    ACKNOWLEDGE numeric 38 40
    LUP_BY varchar 15 15
    YEAR_BACK varchar 4 4
    LUP_DATE datetime2 19 38

    What could be causing this "Errors: Arithmetic operation resulted in an overflow." error? I already tried to change the datatypes for the numbers and dates, to no avail.

    Wednesday, July 3, 2019 9:39 AM

Answers

  • I found the root cause. A column which has a NOT NULL property had NULL values in the Oracle DB. Deleted those rows and the data migration was a success.
    • Marked as answer by RJ Timtiman Thursday, July 4, 2019 5:40 AM
    Thursday, July 4, 2019 5:39 AM

All replies

  • There are reports that using the Oracle driver/provider instead of the Microsoft solves this issue.
    Wednesday, July 3, 2019 10:29 AM
  • Are you pertaining to the server-side data migration?
    Thursday, July 4, 2019 1:03 AM
  • When you run SSMA you have the option to use the ODBC provider, the OLEDB Provider, and ADO.Net Provider. Configure an ODBC connection to your Oracle server using the Oracle Driver instead of the Microsoft one.
    Thursday, July 4, 2019 1:12 AM
  • I am currently using the Oracle Data Provider for .NET. Should I be using another?
    Thursday, July 4, 2019 1:14 AM
  • I found the root cause. A column which has a NOT NULL property had NULL values in the Oracle DB. Deleted those rows and the data migration was a success.
    • Marked as answer by RJ Timtiman Thursday, July 4, 2019 5:40 AM
    Thursday, July 4, 2019 5:39 AM
  • I have encountered similar issue. What I observed is some how Oracle date which is valid in Oracle is not compatible with SQL server and it was giving me the same error. I have changed the column on SQL server to date time 2 but still it just fails with same error message.

    sorry, error message for me is little different:

    Arithmetic operation resulted in an overflow.



    Friday, September 20, 2019 12:16 PM