MS SQL to MySQL

[jenni]

I am helping someone move their data from one server to another. Currently, the info is stored in MS SQL, but it needs to be converted to MySQL. Unfortunately, I do not have access to the MS SQL database and the db administrator does not know how to provide a dump that will work with MySQL.

I don't know a thing about MS SQL, so I have no clue if MS SQL be dumped into a MySQL compatible dump or even a comma-deliminated text file. Is there a command I can give him or free software that would do it?

Thanks as always for your help...

[mhalloran]

You can do this in two ways but you will need some access to the MS SQL server.

The first way is to simply have the administrator do a CSV export. Loading the exported data into MySQL is fairly straight forward but it will take a while if the data structure is complex. Do a search for "load data infile" on this site or Google for instructions.

If there are a lot of tables, the best way will be to use ODBC. You'll need a PC with both the MS SQL server tools installed (can be downloaded for free from the Microsoft website) and the MySQL ODBC driver installed (can grab this at the MySQL website)

Once you install these two applications, you have to add an entry to the PCs ODBC data source application using the MySQL odbc driver that you installed.

You also have to add a user to the MySQL database that grants access from your IP to the new database.

Once that's done, you can use DTS (part of the MS SQL Server tools) to transfer the data from MS SQL to MySQL. It will create and populate the tables automatically. It's totally bad ass.

These are very vague directions but I can write you a more detailed tutorial if you decide to go the ODBC route and can't find a decent tutorial out there.

Best of luck