Convert data from MS SQL Server to MySQL

2009-06-22T06:21:00.000-07:00

Today I was given a task to develop a program in VB to transfer data from our local MSSQL Database to MySQL Database running on remote web hosting.

As I was quite comfortable with MS SQL Server and new to MySQL that to, on remote web server, I went straight to work.

The first thing I did is downloaded the MySQL ODBC Driver 3.51. Which I got quite easily through Google search and MySQL website. The link is http://dev.mysql.com/odbc/win32

Then, next thing I did is developed a form with list box control and combo box control. I wanted to populate combo box control with the list of databases and when I select a database in combo box the list box should show the list of tables in that database.

To connect to the MySQL Database and get the list of databases and tables I used a ADO DB object and specified the connection string as

Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;

As for testing I tried to connect to local MySQL Database first. When I run the form and click the connect button to connect to mySQL I got the following error

Can't connect to MySQL server on ‘localhost' (10061)

After digging into the internet and searching a lot I got the solution. The reason why I was getting the error was because the Firewall installed in the machine is preventing my program to access localhost through port 3306. So I added my program name in allowed list in firewall and then tried again. Wow, I was successfully connected to MySQL and I was able to populate combo box with the list of database by issuing the command "SHOW DATABASES".

After I tested connecting to local MySQL Database server successfully, I tried to connect to the MySQL running on remote host, so I change the connection string as follows

Driver={MySQL ODBC 3.51 Driver};Server=mydomain.com;Port=3306;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;

And click connect button again, oops I got the following error

And then again I googled into the internet nearly spend a couple of hours before I finally got the solution.

The solution is you have to add your IP address in the access list of MySQL database hosted in your share web host account. For doing this, you have to login to CPanel in your share web account and click on MySQL Databases link. At the botton of the page you will find Access Hosts section as shown below

in this add the IP address of your machine and click OK. If you don't know your IP address you can visit the WhaitismyIP.com

After entering my IP address in Access list, I again hit the "Test" button and wow! now I was able to connect successfully to the MySQL databases at our Shared Web Hosting account.

So at last I was able to connect successfully to MySQL database hosted on Shared Webhosting and then went on writing the program which was not so difficult.

But it was becoming tedious with data getting changed weekly and I have to modify programs. Then I searched on internet for any data migration tools, there were plenty of tools available on the internet. After testing several tools I zeroed on Data Loader which has a nice interface and have it's own scheduler and it's ability to map source table columns to target table columns got my favor and finally convinced by manager to purchase it.

You can download a trial edition of Data Loader by clicking the following link Convert MSSQL to MySQL

convert-mssql-to-mysql

Convert data from MS SQL Server to MySQL