Are there any free solutions for automatically migrating a database from MySQL to SQL Server Server that "just works"?
I've been attempting this simple (at least I thought so) task all day now. I've tried:
- SQL Server Management Studio's Import Data feature
- Create an empty database
- Tasks -> Import Data...
- .NET Framework Data Provider for Odbc
- Valid DSN (verified it connects)
- Copy data from one or more tables or views
- Check 1 VERY simple table
- Click Preview
- Get Error:
The preview data could not be retrieved. ADDITIONAL INFORMATION: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"table_name"' at line 1 (myodbc5.dll)
A similar error occurs if I go through the rest of the wizard and perform the operation. The failed step is "Setting Source Connection" the error refers to retrieving column information and then lists the above error. It can retrieve column information just fine when I modify column mappings so I really don't know what the issue is.
I've also tried getting various MySql tools to output ddl statements that SQL Server understand but haven't succeeded.
I've tried with MySQL v5.1.11 to SQL Server 2005 and with MySQL v5.1.45 to SQL Server 2008 (with ODBC drivers 3.51.27.00 and 5.01.06.00 respectively)