This week someone handed me a SQL Server backup, if I could read that data into a MySQL database. Off course was my initial answer, it's my job, sort of. However, after restoring the database in SQL Server there was no easy way to convert it into MySQL. I tried using de SQL Server Import and Export Wizard, after i had setup a correct ODBC connection to the newly created MySQL database.
No matter what i tried, SQL Server was not able to execute any query. The reason for this was the fact that SQL Server escapes table and field names with a double quote ("). MySQL on the other hand does not understand that kind of quoting. Unless....
Unless you tell MySQL to go into ansi-sql mode. Which proved to be the solution. To force this, you need to edit your ODBC connection;
Open your ODBC connections, select the connection you use to connect to your export server, click on configure , open the details pane, click on Misc Options and enter:
set session sql_mode='ansi'
in the 'Initial statement' field.
You can now run your SQL Server import export wizard and your data will be copied this time.