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
go
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
