Migrate from Mysql to Ms Sql using odbc and linked server

2011-01-24T19:59:00.000-08:00

Recently, there are needs for me to migrate data from MySQL to Ms SQL 2005.

The common way to accomplish this is by using DTS (Data Transformation Service) feature of Ms SQL, unfortunately it didn't work for me. I have created ODBC source for MySQL database and field mappings, but after I click Preview or Finish button it gives me following error:

This error happened on every MySQL databases and tables I've tried.

Not willing to waste anymore time, I write following script to do the migration.

Its simple but it works and I hope it will be useful for anyone.

Thanks..

exec master.dbo.sp_addlinkedserver @server = N'mysql' ,

@srvproduct =N'MySQL', @provider=N'MSDASQL' ,

@provstr =N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=mysqlserver ; DATABASE=mysqldb; UID=root; PASSWORD=; OPTION=3'

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mysql' ,@useself= N'True',

@locallogin =NULL,@rmtuser=NULL, @rmtpassword=NULL

declare @name varchar (100), @sql nvarchar( max)

declare tablec cursor for select * from openquery(mysql ,'show tables')

open tablec

while 1 = 1

begin

fetch next from tablec into @name

if @@FETCH_STATUS <> 0 break

print @name

set @sql = 'select * into [' + @name + '] from openquery(mysql,''select * from `' + @name + '`'')'

exec(@sql )

end

deallocate tablec

kittenheart

Migrate from Mysql to Ms Sql using odbc and linked server