Entrar
 
HomeBibliotecaWikiAprenderGaleriaDownloadsSuporte ComunidadeFóruns
Recursos para Profissionais de TI >
> 
> 
SSMA: Error migrate data mysql => Sql Server

Beantwortet SSMA: Error migrate data mysql => Sql Server

  • segunda-feira, 2 de janeiro de 2012 13:32
     
     
    Entrar para Votar
    0

    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 ?

     

Todas as Respostas

  • segunda-feira, 2 de janeiro de 2012 11:13
     
     
    Entrar para Votar
    0

    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 ?
     
  • terça-feira, 3 de janeiro de 2012 08:54
     
     
    Entrar para Votar
    0
     
    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
     
  • terça-feira, 3 de janeiro de 2012 09:11
     
     
    Entrar para Votar
    0
    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 ...
     
  • terça-feira, 3 de janeiro de 2012 16:06
     
     
    Entrar para Votar
    0
    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.
    • Editado Drakken666 terça-feira, 3 de janeiro de 2012 16:16
    •  
     
  • terça-feira, 3 de janeiro de 2012 16:16
     
     
    Entrar para Votar
    0

    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.

     
  • quarta-feira, 4 de janeiro de 2012 02:55
    Moderador
     
     
    Entrar para Votar
    0

    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.
     
  • quarta-feira, 4 de janeiro de 2012 10:11
     
     
    Entrar para Votar
    0

    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.

     
  • quarta-feira, 4 de janeiro de 2012 15:52
     
     
    Entrar para Votar
    0

    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.

     
  • quinta-feira, 5 de janeiro de 2012 05:22
    Moderador
     
     
    Entrar para Votar
    0

    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.
     
  • quinta-feira, 5 de janeiro de 2012 10:25
     
     
    Entrar para Votar
    0
    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'
     
  • sexta-feira, 6 de janeiro de 2012 09:19
    Moderador
     
     
    Entrar para Votar
    0

    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.
     
  • terça-feira, 1 de maio de 2012 10:39
     
     
    Entrar para Votar
    0

    hi you can also ask your query on the below given forum.

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

    • Sugerido como Resposta JimmyRocky quinta-feira, 7 de junho de 2012 22:43
    • Não Sugerido como Resposta JimmyRocky quinta-feira, 7 de junho de 2012 22:43
    •  
     
  • quinta-feira, 7 de junho de 2012 22:46
     
     Respondido
    Entrar para Votar
    3
    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.
    • Sugerido como Resposta JimmyRocky quinta-feira, 7 de junho de 2012 22:47
    • Marcado como Resposta Iric WenModerator quarta-feira, 1 de agosto de 2012 01:09
    •  
     
  • sexta-feira, 22 de junho de 2012 10:23
     
     
    Entrar para Votar
    0
    In this situation I would recommend you following application http://www.mysql.repairtoolbox.com it helps you migrate/import/open/read/recover mysql data
     
  • segunda-feira, 9 de julho de 2012 12:42
     
     
    Entrar para Votar
    0
    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
     
  • terça-feira, 31 de julho de 2012 16:00
     
     
    Entrar para Votar
    0
    This worked for me to. Not sure how you figured this one out Jimmy, but thank you!
     
  • quarta-feira, 22 de agosto de 2012 22:06
     
     
    Entrar para Votar
    0
    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.
    • Editado DMSoft quarta-feira, 22 de agosto de 2012 22:07
    •  
     
  • segunda-feira, 29 de outubro de 2012 21:34
     
     
    Entrar para Votar
    0

    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?


    • Editado despiehl quarta-feira, 31 de outubro de 2012 20:02 clarified issue
    •