You need to use the MySQL ODBC driver to create a Linked Server to the MySQL Server, and then run a SQL query to copy your database, table by table. The query is (write the database name, server name, user and password specific to your server):

EXEC master.dbo.sp_addlinkedserver @server = N’MYSQL’, @srvproduct=N’MySQL’, @provider=N’MSDASQL’, @provstr=N’DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=Db_NAME; USER=myusername; PASSWORD=mypass; OPTION=3′

Then you’ll have to copy your database table by table. The code for one of the tables will be:

select * into MSSQL_DB_Name.dbo.TableName from openquery(MySQL, ’select * from Db_Name’)

btw, this method is not only for mysql, but also for any database that has ODBC Driver, such as Access, csv, excel, SQLite.

This entry was posted on Friday, November 16th, 2007 at 4:42 pm and is filed under English, tutorial. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.