locked
Oracled to SQL migration issue with Parameter name: scale RRS feed

  • Question

  • I am migrating Oracle 9 to SQL Server 2014 using SSMA (SQL Server Migration Assistant for Oracle).

    Most of tables have been migrated successfully except for five tables where I am getting the following error:

    "Decimal's scale value must be between 0 and 28, inclusive.

      

    I found out that SQL Server only allows maximum of 53 precision under float, and SSMA cannot handle the conflict during the migration.

    Type mapping is float(126) in Oracle to float(53) in SQL.


    Alexander Skordos Network Engineer Gold Certified Professional Geniusnet SA - Hellas

    Friday, April 7, 2017 1:19 PM

Answers

  • Hi Alexandros,

     

    By default, float[54..*] is mapped to float[53] in SQL Server. As a result, you will meet this problem. You can customize the data tpye mapping, please refer to the following steps:

     

    1. On the Tools menu, select Project Settings; In the left pane, select Type Mapping.
    2. Click Edit, under Source type, select the Oracle data to map.
    3. Under Target type, select the target SQL Server data type, click OK.

     

    For more information, please refer to this document: Mapping Oracle and SQL Server Data Types (OracleToSQL) -> https://docs.microsoft.com/en-us/sql/ssma/oracle/mapping-oracle-and-sql-server-data-types-oracletosql You can use this method to change float([54-126]) mapping to float.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 2:02 AM

All replies

  • Hi Alexandros,

     

    By default, float[54..*] is mapped to float[53] in SQL Server. As a result, you will meet this problem. You can customize the data tpye mapping, please refer to the following steps:

     

    1. On the Tools menu, select Project Settings; In the left pane, select Type Mapping.
    2. Click Edit, under Source type, select the Oracle data to map.
    3. Under Target type, select the target SQL Server data type, click OK.

     

    For more information, please refer to this document: Mapping Oracle and SQL Server Data Types (OracleToSQL) -> https://docs.microsoft.com/en-us/sql/ssma/oracle/mapping-oracle-and-sql-server-data-types-oracletosql You can use this method to change float([54-126]) mapping to float.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 2:02 AM
  • Hi again Teige,

    At some point I tried to re-run this conversion for a second time. I also decided to put this mapping in the default SSMA project settings so as a setting it was available both in the default settings AND in the current project settings. Unfortunately the second time the error came back! Any ideas on what I may have done wrong?


    Alexander Skordos Network Engineer Gold Certified Professional Geniusnet SA - Hellas

    Monday, June 5, 2017 12:27 PM
  • Hi Alexandros,

     

    By default, float[54..*] is mapped to float[53] in SQL Server. As a result, you will meet this problem. You can customize the data tpye mapping, please refer to the following steps:

     

    1. On the Tools menu, select Project Settings; In the left pane, select Type Mapping.
    2. Click Edit, under Source type, select the Oracle data to map.
    3. Under Target type, select the target SQL Server data type, click OK.

     

    For more information, please refer to this document: Mapping Oracle and SQL Server Data Types (OracleToSQL) -> https://docs.microsoft.com/en-us/sql/ssma/oracle/mapping-oracle-and-sql-server-data-types-oracletosql You can use this method to change float([54-126]) mapping to float.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi again Teige,

    At some point I tried to re-run this conversion for a second time. I also decided to put this mapping in the default SSMA project settings so as a setting it was available both in the default settings AND in the current project settings. Unfortunately the second time the error came back! Any ideas on what I may have done wrong? Is it possible to get a different behavior if the destination SQL database during the conversion has a full recovery model selected?



    Alexander Skordos Network Engineer Gold Certified Professional Geniusnet SA - Hellas


    Wednesday, June 14, 2017 9:47 AM