locked
MySQL datetime(precision) not mapped. Unable to map it. M2SS0044 RRS feed

  • Question

  • I'm using SQL Server Migration Assistant for MySql v6.0.0 to convert some tables and migrate the data.

    In the MySQL database there are a number of types that are datetime(5) which gives the following error when trying to convert the schema:

    M2SS0044: Type 'datetime(5)' was not converted because there is no mapping for it. Add a mapping and then convert again.

    I tried to create a mapping but there is no datetime(precision) as SourceType to select. Is it possible to create one somehow?

    Thanks!

    Sunday, August 9, 2015 11:15 AM

Answers

  • Hi Samuel,

    According to your description, you get the error about datetime (5) data type when converting the schema from MySql to SQL Server with SQL Server Migration Assistant(SSMA) . In SSMA, it is impossible to create a mapping for the datetime(5) data type because only the data types included in Default Project Settings can be added or edited.

    To solve the issue about datetime (5) data type, you can create a linked server in SQL Server pointing to MySql, then migrate data of datetime(5) type columns to SQL Server datetime2 type columns. The detailed steps about the process are as follows:

    1. Create a table in SQL Server named “SQLtable” ,the table contains two columns (time1 and time2) of datetime2 data type. Assume that the table in Mysql is named “Mysqltable” and it contains two datetime(5) type columns (time3 and time4) .

    2. Create a linked server for MySql in SQL Server. For more details about setting up linked server for MySql, please review this link: https://www.packtpub.com/books/content/mysql-linked-server-sql-server-2008 .

    3. Using cast() function to convert the source data of datetime(5 )data type columns to string.

    4. Using the OPENQUERY method to select the source data from MySql.

    5. Insert the result into the target columns of SQL Server table using the following commands.

    Insert into SQLtable (time1,time2)
    SELECT  mytable1.column1 ,mytable1.column2  FROM OPENQUERY (MysqlLinkedServer, ‘SELECT  cast ([time3] as varchar(50)) column1,  cast ([time4] as varchar(50))  column2 FROM [Mysqltable]’) as mytable1


    Thanks,
    Ice Fan

    



    Tuesday, August 11, 2015 7:53 AM