I have been working on this all day and now I'm stuck, so hopefully someone out there can help me :)
The challenge.
Migrate data from MS SQL to MySQL.
The MS SQL I received as a bak file, which I restored using SQL server management studio on a PC with Windows 7 Home edition.
I have created a source MySQL database on a webserver, running LAMP.
The solution (maybe) I'm currently trying to convert the database, initially just one table for testing, using MySQL Workbench with the database->migrate wizard, but now I'm stuck at the Bulk Data Transfer. I would expect this step to create the table in my MySQL database and transfer the data, but that never happens.
For the source I choose Connection Method = ODBC (native) No problems connecting to the source and destination databases
I choose to keep schema info as table prefix, so imported tables look like: database.dbo_table_name
Migration step succeeds (migrate selected objects & Generate SQL Create Statements)
The Create statements look like this if I don't edit them CREATE TABLE IF NOT EXISTS 'restored_database_name'.'dbo_table_name' … I think the 'restored_database_name' part causes a permission error. It does if I type this in the SQL tab directly in phpMyAdmin. Therefore I change it to: CREATE TABLE IF NOT EXISTS 'dbo_table_name' …
Also per default this is part of the SQL: DROP SCHEMA IF EXISTS 'restored_database_name'; CREATE SCHEMA IF NOT EXISTS 'restored_database_name … I think this also causes some permission issues, so I commented these out.
In the next step I uncheck the 'Create schema in target RDBMS' since I don't think I want this.
The problem: Nothing interesting for the next steps, but then at the "Bulk data Transfer" I get this error: ERROR: 'restored_database_name'. 'dbo_database_name': mysql_stmt_prepare: SELECT command denied to user 'mysql_user_name'@host' for table 'dbo_database_table_name. Finished copying 0 rows.
I think the error is somehow related to permissions on the destination database. I wonder if it is possible to make sql file, not just with the create table commands, but also with INSERT commands so I could just take the sql and import it using command line or phpmyadmin.
I'm using Workbench 5.2 CE
Any help is appreciated
