locked
SSMA: Error migrate data mysql => Sql Server RRS feed

  • Question

  • Hello,

    I'm trying to migrate a database Mysql 5.1 to SQL Server 2008 with SSMA.
    The server hosting the SQL database is on Windows 2008 Enterprise x64.
    The user connecting to the SQL database is sysadmin.
    Phases to convert and sync happen without problems, but the migration of data blocks it.
    The log tells me out:

    Data migration operation has finished.
    0 table (s) successfully migrated.
    0 table (s) Partially migrated.
    46 table (s) failed to migrate.

    The Data Migration Report is empty with only a red cross next to my table names.

    I tried to migrate in client mode and server mode = same result. Any ideas ?

     

    EDIT: miss, this thread shoud be in section SQL Server Migration. A moderator can move my post please ?

    • Edited by Drakken666 Monday, January 2, 2012 2:53 PM
    • Moved by Janet Yeilding Tuesday, January 3, 2012 5:46 AM SSMA question (From:SQL Server Data Tools)
    Monday, January 2, 2012 1:32 PM

Answers

  • I had this same error and it turned out to be the case of the database name, in your example I would assume the new MS SQL Database name is something like TestLink.  Try having both database names be lower case, that worked for me.
    • Proposed as answer by JimmyRocky Thursday, June 7, 2012 10:47 PM
    • Marked as answer by Iric Wen Wednesday, August 1, 2012 1:09 AM
    Thursday, June 7, 2012 10:46 PM

All replies

  • Hello,

    I'm trying to migrate a database Mysql 5.1 to SQL Server 2008 with SSMA.
    The server hosting the SQL database is on Windows 2008 Enterprise x64.
    The user connecting to the SQL database is sysadmin.
    Phases to convert and sync happen without problems, but the migration of data blocks it.
    The log tells me out:

    Data migration operation has finished.
    0 table (s) successfully migrated.
    0 table (s) Partially migrated.
    46 table (s) failed to migrate.

    The Data Migration Report is empty with only a red cross next to my table names.


    I tried to migrate in client mode and server mode = same result.

    Any ideas ?
    Monday, January 2, 2012 11:13 AM
  •  
    Did you do this: In addition to the SSMA program files, you must also install the SSMA for MySQL Extension Pack on the SQL Server machine.

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Edited by Mr. Wharty Tuesday, January 3, 2012 8:56 AM
    • Proposed as answer by Carsten Karcher Wednesday, July 16, 2014 2:02 PM
    Tuesday, January 3, 2012 8:54 AM
  • Yes, that's the version of SSMA I use and expansion pack is also installed on the SQL Server machine.

    It is more than a week since I searched the net looking for a solution and I must be totally out of ideas ...
    Tuesday, January 3, 2012 9:11 AM
  • I just found another tool SSMA to migrate to MySql SLQ Server: ESF Database Migration Toolkit.
    Migration without problems ... except that this tool is not free and replaces the first character of text fields by T. .. frustrating ...

    This tool uses the same ODBC driver and I use the same credentials to connect to databases.

    I'll go mad.

    • Edited by Drakken666 Tuesday, January 3, 2012 4:16 PM
    Tuesday, January 3, 2012 4:06 PM
  • Je repasse en Français, ça sera plus pratique.

    Je viens de trouver un autre outil que SSMA permettant de migrer MySql vers Slq Server : Esf Database Migration Toolkit.
    La migration se passe sans probléme... sauf que cet outil est payant et remplace le premier caractére des champs texte par T... frustant...

    L'outil se sert du même driver ODBC et je me sers des mêmes identifiants pour me connecter aux bases.

    C'est à devenir fou.

    Tuesday, January 3, 2012 4:16 PM
  • Hi Drakken666,

    It seems that there may be some unsupported date values in your MySQL database, please try to set the out-of-range date values with NULL or the nearest date SQL Server can support. You can select to replace unsupported dates in your Project Settings. After that, please try migrating your database again follow this link: http://blogs.msdn.com/b/ssma/archive/2011/02/07/mysql-to-sql-server-migration-how-to-use-ssma.aspx

    If anything unclear, please feel free to let me know.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Edited by Iric Wen Friday, January 6, 2012 1:17 AM
    Wednesday, January 4, 2012 2:55 AM
  • Hi, Thanks for your answer.

    I just try with the options of the following dates:

    Same result...

    To know that I have absolutely no errors in the logs SSMA.

    Wednesday, January 4, 2012 10:11 AM
  • I grew ... last.
    Migration has finally started after passing the parameter conversion 'Default schema mapping' to 'Database to Schema'.

    But, of course, an error never comes alone.

    'Column xxxx does not allow DBNull.Value' that is triggered on several tables.

    Wednesday, January 4, 2012 3:52 PM
  • Drakken,

    Please check if any tables set date type as their primary key. 'Column xxxx does not allow DBNull.Value', this error always occurs if there is some value you are migrating which is a primary key but SQL Server unsupported.

    If the workround above is not helpful, you can try to set ‘default’ mode in ‘project settings’ see if the error remains.

    Waiting for your feedback.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Edited by Iric Wen Thursday, January 5, 2012 6:40 AM
    Thursday, January 5, 2012 5:22 AM
  • Apparently the error occurs only on the columns of type DateTime, default 0000-00-00 00:00:00 but not on primary key.

    Example of data to be converted: 2011-04-06 10:39:32.

    SSMA is proposing to convert them datetime2 (0).

    I tried manually edited with Datetime conversion, datetime2, smalldatetime => all end up in error.

    And if I put the 'Date Zero In migration' = 'Error' instead of 'Set Null' another error appears:

    'Can not convert String provided for the data source type datetime2'
    Thursday, January 5, 2012 10:25 AM
  • Drakken666,

    Have you ever tried set the "project settings" mode to "default"? What's the result?


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, January 6, 2012 9:19 AM
  • hi you can also ask your query on the below given forum.

    http://technet.microsoft.com/en-us/magazine/hh334645.aspx

    • Proposed as answer by JimmyRocky Thursday, June 7, 2012 10:43 PM
    • Unproposed as answer by JimmyRocky Thursday, June 7, 2012 10:43 PM
    Tuesday, May 1, 2012 10:39 AM
  • I had this same error and it turned out to be the case of the database name, in your example I would assume the new MS SQL Database name is something like TestLink.  Try having both database names be lower case, that worked for me.
    • Proposed as answer by JimmyRocky Thursday, June 7, 2012 10:47 PM
    • Marked as answer by Iric Wen Wednesday, August 1, 2012 1:09 AM
    Thursday, June 7, 2012 10:46 PM
  • In this situation I would recommend you following application http://www.mysql.repairtoolbox.com it helps you migrate/import/open/read/recover mysql data
    Friday, June 22, 2012 10:23 AM
  • I had this same error and it turned out to be the case of the database name, in your example I would assume the new MS SQL Database name is something like TestLink.  Try having both database names be lower case, that worked for me.
    This fixed it for me, the destination database name must be all lowercase
    Monday, July 9, 2012 12:42 PM
  • This worked for me to. Not sure how you figured this one out Jimmy, but thank you!
    Tuesday, July 31, 2012 4:00 PM
  • As an alternative to SSMA try DBConvert for MSSQL and MySQL It is able to convert structure, table data and views. Approximately 500 thousand records  can be converted 3-5 min.
    • Edited by DMSoft Wednesday, August 22, 2012 10:07 PM
    Wednesday, August 22, 2012 10:06 PM
  • I am also getting the 'Column xxxx does not allow DBNull.Value'  error as described above. I have already tried changing the destination database to lower case and that did not help. The MySQL data has zero date values '00-00-0000' in several tables as the default value and these are non-nullable fields.

    I have set zero dates to be replaced with a constant (1-1-1900) on the Project settings --> general --> conversion menu

    However, on the project settings --> general --> migration menu there is a separate menu option for handling zero dates. The only two options are set to NULL or ERROR out.

    How do these two options interact with each other? Will it get replaced with 1-1-900 or get converted to null? Seems like its being converted to null and the replacement value is being ignored. Any way to change this behavior?


    • Edited by despiehl Wednesday, October 31, 2012 8:02 PM clarified issue
    Monday, October 29, 2012 9:34 PM
  • For me it was also the db name needed to be all lowercase matching the MySQL schema. 
    Monday, August 12, 2013 9:06 PM
  • I really can't believe SSMA isn't smart enough to handle capitalization in a database name.  This resolved the problem for me... after several hours of trying other things.
    Tuesday, February 11, 2014 6:51 PM
  • @desipiehl

    Strongly agree with your points above, Were you able to resolve the issue?

    Monday, April 21, 2014 7:15 PM
  • Hi Mr. Wharty,

    Thanks for your Answer!

    Just i use and expansion pack is also installed on the SQL Server machine.

    Thanks 

    Wednesday, July 16, 2014 2:07 PM
  • Thanks a lot worked like a charm
    • Edited by Bryida Sunday, February 22, 2015 7:53 PM
    Sunday, February 22, 2015 7:52 PM
  • This worked for me. Thank you guys
    Friday, January 15, 2016 1:14 PM
  • Great! Saved my day.
    Thursday, March 8, 2018 9:30 AM