Lucian's weblog

YOU ARE SOOOO FREAKING AWESOME!!!

Thank you for this tip, this worked great!  There's nothing better in the world than creating the solution yourself!  Thank you again for this tip.

Thanks man, that really helped me out today.

Many thanks. You've just made my life easier. :-)

Hmm, doesn't work for me, I get

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "MySQL" reported an error. The provider reported an unexpected catastrophic failure.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "MySQL".

Poo!

Yep, installed 3.51.17 and get the same result from the OSX server I was originally trying to export the data from and on a WinXP machine with the same database. I get the exact same error on both. All the tables are MyISAM although when I tried to copy over a table with only 6 rows it went fine. The ones I'm trying to work with now though are 111,897 rows, 147,856 rows and 157,294 rows each. Wondered if the size was too much, although the actual data itself doesn't exceed 12,476KB in each of those tables.

Probably something I've done wrong when creating the MS SQL database or configuration in SQL Server Management Studio Express

Yeah, I've read that post already but it just plain doesn't work. Also, some of the information that has been copied over has converted oddly. For example, we've got some tables with BLOB data which is actually holding text. When it moves over to MSQL it changes to IMAGE. I think I'm going to have to talk the company into buying a proper solution. It doesn't help that when the MySQL database was originally created it was very haphazardly done.

Here is a somewhat painful solution.  First I created the destination database in MS SQL.  Then, I connected to the MySQL database from Microsoft Access and used myODBC to import all of the tables.  Next, I created a linked server (using the Jet provider) pointing directly to the .mdb file. Finally, I used lucianb's select syntax (first post) to copy one table at a time into the MS SQL database.  Unfortunately, I did not get my primary and foreign keys created, but at least all of my data was there.

Good Luck!

Do you need to remove the virtual server afterwards?

If so, how?

great tip...thanks!

Does it preserve any of its Foreign key,primary key etc.

Hi,

How can I migrate stored procedure????

Thanks Nicola

PingBack from http://www.blogexmachina.com/mysql/migration-from-mysql-to-sql-server-2005-express-edition/

I tried the code above and got a login timeout error.

What can I do to connect to mySQL?

Thanks.

OLE DB provider "SQLNCLI" for linked server "MySQL" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "MySQL" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

Problem is, as lucianb said, it doesn't preserver keys, either foreign or primary ones, doesn't keep auto_increment information, neither default values. As for myself, I will use this method only to avoid converting INSERT statements to mssql.

But anyways, this is a great procedure.

Thanks. Lucian.

I went to the url, read the instructions, followed the steps. Enabled remote access. Stopped and re-started the sqlserver.

Opened the firewall and granted exceptions to MYSQL, mysql, mysqlimport.

The same error as shown in the last post persists.

Please advise how else I can do to configure mysql to migrate data from mysql to sql 2005.

Thanks and regards.

Hi, Lucian

Thanks for the info.

We have solved the problem in 2 ways.

1. Using the control panel/administive tools, I manually added the system DNS for the ocbc driver by browsing and selecting the ocbc from the location.

2. I noted that my copy of mysql is of version 5.1 instead of 3.51. So I changed the driver version to 5.1 in the string you provided and then the linked serve works.

Regards

When I try it it show the wrong ip address, I try another with the url and it shows the wrong IP address, the one it always shows is 76.180.28.199

Also, How do you delete the linked server ?

Thanks. Lucian.

There is a minor typo error in your article. Most people would just ignore it. Since you are doing some amendment to your page, you might as well amend this too.

"select * into MSSQL_DB_Name.dbo.TableName

from openquery(MySQL, 'select * from Db_Name')"

The last part should read:

'select * from Db_Name.Tbl_Name'

Regards.

already tried this one and its a nice script but the problem is this does not import stored procedure and triggers but a nice post though thanks...

Thanks! Saved my day!

There is another way to delete a linked server through the Microsoft SQL Server Management Studio:

In the “Object Explorer”

1. Expand the node “Server Objects”

2. Expand the node “Linked Servers”

3. Right click on the name of the linked server you want to delete

4. Click “Delete”

5. Done!

Regards

OldStd

Worked like a charm..

Hi,

I got an error saying that provider 'MSDASQL' is not registered.

Can anyone help me out of this..

Thanks

Thank you so much.  This has saved me countless hours of work.

Maybe a useful select to generete the openquery statements on the MySQL server to be run at the SQL Server.

SELECT concat('SELECT * INTO ', table_name, ' FROM openquery(MySQL, ''select * from zensor_ct.', table_name, ''');'

FROM information_schema.`TABLES` T

where table_schema = 'zensor_ct'

and table_type = 'BASE TABLE' order by table_name;

Thanks a lot. It solved my problems

I am a newbie to migration and MS-SQL both. I would like to know if there is any way I can automate migration from MySQL to MS-SQL for all tables in my dbase. FYI.. My dbase contains 634 tables..

Thanks!

Vikram

I did not follow it earlier...but I got the hint...thanks a lot!

Oh My God,  i have to thank you so so much as ive been looking for somthing like this for some time now and this solution is PERFECT.

5 stars for figuring it out and another 5 for sharing :)

We Love you!

PingBack from http://studentclub.ro/lucians_weblog/archive/2008/09/12/satisfactia-de-a-scrie-un-blog-semi-tehnic.aspx

thanks....it worked nicely for me...made life a lot easier.....

FYI, I had a few issues with the linked server setup once I ran it, namely the user I used didn't have the permissions that I needed to get the job done, so I had to delete the configuration that EXEC master.dbo.sp_addlinkedserver added -- so you know, it uses:

EXEC master.dbo.sp_dropserver @server=@LINKED_SERVER_NAME, @droplogins=' droplogins'

This gets rid of the linked server, thought it might be useful for someone else on this thread!

Thanks,

-nate

Thanks for the info!

I wanted to add a quick additional note.  I had several columns that were "longtext" in my MySQL database.  When I would try to copy those tables I was getting a "catastrophic error".

I finally got it to work by putting a LEFT on the LONGTEXT column.  For example:

SELECT * FROM openquery(

'SELECT UNIQUE_ID,

LEFT(DISSERTATION, 4000) AS DISSERTATION'

)

Anyhow, took me three days to figure that out so I thought I would share.

Greetings comrades:

Here they left a query from mssql to insert records from mysql to mssql.

The atmosphere was worked windows 2003 server, SQL 2005 standard edition, and mysql

5.0.51b

Standards:

The BD-be created in mssql

-Install odbc driver for mysql

PHASE I: CREATING A CONNECTION IS MySQL ODBC

======================================

Goal: To create two connections to MySQL ODBC. The first to bd INFORMATION_SCHEMA

and the second on the bd with the data

migrate (the other factors did not alter the product = P)

1.1 Control Panel / Administrative Tools / Data Sources (ODBC)

1.2 User DSN, Add

MySQL ODBC 1.3 Driver Driver (I use 3.51)

1.4 Data Source Name: MySqlInformationSchema

1.5 Description: INFORMATION_SCHEMA database in MySQL

1.6 Server: Server Name MySQL

1.7 User: Users of the bd mysql

1.8 Password: password of the user mysql

1.9 DataBase: select the BD INFORMATION_SCHEMA

1.10 User DSN, Add

MySQL ODBC 1.11 Driver Driver (I use 3.51)

1.12 Data Source Name: MySqlDataBaseUsuario

1.13 Description: user database in MySQL

1.14 Server: Server Name MySQL

1.15 User: Users of the bd mysql

1.16 Password: password of the user mysql

1.17 DataBase: select the BD containing the records to migrate

PHASE II: CREATING TWO linked server in SQL SERVER

================================================== ====

Goal: To create linked servers in SQL Server to be able to connect to the mysql bd

In the administrative console of sql server:

Objects 2.1 server / servers linked New linked server ...

Linked Server 2.2: MSIS

Type Server 2.3: Another Source of data

2.4 Producer: Microsoft OLE DB Provider for ODBC Drivers

2.5 Product Name: MySQL (name descriptive of odbc)

2.6 Data source: MySQlInformationSchema (here put the name created under 1.4)

2.7 Click OK

Objects 2.8 server / servers linked New linked server ...

Linked Server 2.9: MSDBU

2.10 Type Server: Another Source of data

2.11 Producer: Microsoft OLE DB Provider for ODBC Drivers

2.12 Name of Product: MySQL (name descriptive of odbc)

2.13 Source Data: MySQlDataBaseUsuario (here put the name created in Section 1.12)

2.14 Click OK

PHASE III: RUN IN THE QUERY SQL SERVER

=========================================

Objective: Run the query to copy records from the LSO in bd bd in MySQL to MSSQL

Note: bd should be created in mssql, this query deletes all records in the database

destination

q so if they want to attach to comment on the line 28 (q but you will have to deal

with the duplicity of key fields LSO)

Here is the query:

USE DataBaseMSSQL-- insert here name bd mssql

SET DATEFORMAT ymd

DECLARE @table_name VARCHAR(100)

,@sqlqry VARCHAR(8000)

,@name VARCHAR(100)

,@CAMPOS VARCHAR(8000)

,@contador int

,@separador VARCHAR(1)

,@bdmysql varchar(100)

SET @bdmysql='DataBaseMySQL' --insert here name db MySQL

SET @campos=''

SET @contador=1

SET @separador=','

DECLARE mysql_cursor CURSOR FOR

SELECT table_name FROM OPENQUERY(msis, 'SELECT * FROM tables') WHERE

table_schema=@bdmysql

OPEN mysql_cursor

FETCH NEXT FROM mysql_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlqry='delete from dbo.'+@table_name

EXEC (@sqlqry)

DECLARE campos_cursor CURSOR FOR

SELECT T2.NAME

FROM dbo.sysobjects t1

INNER JOIN dbo.syscolumns t2

ON t1.id=t2.id

WHERE t1.name =@table_name

OPEN campos_cursor

FETCH NEXT FROM campos_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

IF @contador=1

BEGIN

SET @separador=''

SET @campos=''

END

SET @campos=@campos+@separador+@name

--print @campos

SET @contador=@contador+1

SET @separador=','

FETCH NEXT FROM campos_cursor INTO @name

END

SET @contador=1

CLOSE campos_cursor

DEALLOCATE campos_cursor

IF (SELECT COUNT(*) FROM dbo.sysobjects t1 INNER JOIN dbo.syscolumns t2

ON t1.id=t2.id WHERE t1.name =@table_name and t2.colstat=1) >0

BEGIN

SET @sqlqry='set identity_insert dbo.'+@table_name+' ON '+'insert

into dbo.'+@table_name+'('+@campos+') '+'SELECT '+@campos+' FROM OPENQUERY(msdbu,

'+CHAR(39)+'SELECT * FROM '+@table_name+CHAR(39)+');'

PRINT @sqlqry

EXEC (@sqlqry)

END

ELSE

BEGIN

SET @sqlqry='insert into bugtracker.dbo.'+@table_name+' '+'SELECT *

FROM OPENQUERY(msdbu, '+char(39)+'SELECT * FROM '+@table_name+char(39)+')'

PRINT @sqlqry

EXEC (@sqlqry)

END

FETCH NEXT FROM mysql_cursor INTO @table_name

END

CLOSE mysql_cursor

DEALLOCATE mysql_cursor

Microsoft Access 2007 has a great feature for moving data from one table to the next. All I had to do was create an ODBC connection to my MySQL server (using the ODBC connector MySQL AB provides for you (I had to create a new user in MySQL 'root'@'my-remote-pc' because the MySQL server wasn't on my machine) and an ODBC connection to the Microsoft SQL server. I imported the data from the MySQL database into Access then on the "Database Tools" tab, clicked "SQL Server" under "Move Data". I then picked my MS-SQL ODBC Connection and it did the rest!

Now, getting my own application to work with this, is another story ;)

Hello

This procedures, copy all objects from MySql, include autonumbers, foreign keys ,unique keys and indexes?

Lo unico que hace es copiar los datos de las tablas de la base de datos de origen, mas no toma en cuenta las claves foráneas

Hi All,

I have also tried this approach and it is working for some of the tables.  I am trying to migrate MySQL 5.0.67 to SQL Server 2008 and I used ODBC 5.1.5 as the provider.  But some of the tables are showing the following error.  I feel that the tables having BLOB fields are having this issue.

OLE DB provider 'MSDASQL' for linked server 'MYSQL' returned data that does not match expected data length for column '[MSDASQL].RecKey'. The (maximum) expected data length is 120, while the returned data length is 42.

Also I believe that indexes, keys are not getting copied to SQL Server Schema while migrating.  It would be really helpful if you could provide some pointes here.

Regards,

Praveen

Lucian:  I am about to give your solution a try.  Thought I would preemptively thank you for posting this.  Definitely cool of you the way you have been trying to help people out.

OldStd:  First, sorry about your luck with the STD but with proper treatment.. who knows..  :)    Seriously though, I was just reading and thinking, "This guy needs to just admit defeat and find someone who knows what the heck they are doing." HOWEVER, you redeemed yourself big time by not only solving the issue but by being considerate enough to post what your solution was.

I hate, and I mean HATE, those a$$holes that post online for days about their problem and then come back in and say.. "Oh, I fixed it. Thanks!"  -- I mean I laugh at idiots who do that but really it does annoy me.

Let's all remember that the world is a big place and other people may share your problems so don't go out seeking help without realizing that your solution can also benefit others.

PingBack from http://iratonline.net/?p=10685

PingBack from http://trsplet.com/blog/2009/07/06/pretvorba-php-mysql-aplikacije-v-php-ms-sql-aplikacijo-apache-php-mdb2-mysql-iis-php-mdb2-mssql/

I could hug you and kiss you!!! Thank you for saving me tone of time and money

I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool.

Excellent! Helped me to go from MySQL 5.1 to MSSQL 2005 Express with little to no hassles.

Thanks for your work and for posting the results of your research!

Works like a dream

Caracasssssssss salvou minha vida veioooooooo!!!!!!!!!!

Brigadão!!!!!!

Thanks Man.

I really need this kind of thing

I am in the same situation.

Have a MySQL 5 database with 100 tables. Now I need to change to SQL Server 2008 Express.

What I do not understand:

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

Where can I put this command? In a batch file or in SQL Server ?

Must I install SQL Server Express and MySQL on the same machine?

Thanks a lot

hawk

hey thanks for your add, was so usefully, but I have a issue Im triyng to execute this code:

INSERT OPENQUERY (lmysql, 'SELECT campo2 FROM mtraficv3.htrafich')

SELECT A.*

FROM openquery(lmysql, 'select campo2 from mtraficv3.htrafic') AS A

I get the error

"[MySQL][ODBC 5.1 Driver][mysqld-5.1.41]No database selected"

also select and update are allowed

Ill be gratefull with your help

thanks!

PingBack from http://www.forexwebs.info/?p=1749

PingBack from http://websiteofthestate.interactiveinfonet.info/state-web/state-of-georgia-web-site.html

another thnx from me :D, i ddnt try it yet cause i dont have time right now.. but i need it.. so i have to come back ;)

PingBack from http://facewash.interactiveinfonet.info/olay-wash/face-natural-wash.html

Woww... Tnks So much!... say the first coment... U are soo FREAKING AWESOME!!!

Tnks again

Thanks for the help on this.  Based on your work, I've written a script that will go through and migrate the data for all tables in a database.   The scripts I wrote assume you already have the table structure set up on MS SQL Server, but that can be changed if you want.  Enjoy!

http://adventuresindotnet.blogspot.com/2010/10/migrating-umbraco-from-mysql-to-mssql.html

Thank you very very very very vert much~

Thank you very very very very very very very very much~

your code works * great, VERY thanks to post it.

Hello.

I trying to convert but have troubles... It is possible to use new driver 5.1?

We have created an opensource database plugin for MySQL that allows to insert data directly in oracle from within MySQL. You can also do both, query oracle tables or insert/update/delete oracle table data just like a JDBC connector but without the need to do any Java/Perl/PHP code.

The plugin that I have written that is compatible Debian/Redhat/Windows platforms and MySQL 5.1.X and 5.5.X

The plugin works the same way as an MySQL Federated table or as a ORACLE DBLINK type of thing...

You create a table and you set the connection string to an Oracle, MySQL, MSSQL server and table name.

Feel free to try the MySQL Plugin at : https://launchpad.net/datacontroller

you can also visit our website and ask for more information via the download page or forum @ http://www.theacesolutions.com/

Thanks

Mysql to SQL 10.0 steps:

1. Use some software's trial version for creating the db structure (I used http://www.convert-in.com/sql2mss.htm)

2. Install MySQL ODBC driver that this article suggests

3. Import data to SQL using SQL Server Import and Export Wizard (in Management Studio) - one by one for each table

This is what I have used - works like a charm:)

Thank you. It works for me ^o^ b

GREAT..!!!