[opensuse] MS SQL to My SQL (MariaDB)
hi, I am having trouble migrating a database from M$ SQL to My SQL. The source is a server running MS SQL 2012 R2 and all the server does is run a moodle site. Destination is our opensuse 42.1 web server. running mariadb. I have tried using the MySQL Workbench, but it fails on most of the data on the Bulk Data Transfer part. It has numerous errors. Most commonly (with almost all the tables) it says the data already exists at the destination. (It was a brand new database and I have checked with phpmyadmin and there is nothing there!!) Also it errors with incorrect database encoding. The old database is in Latin General. I have tried making a copy (on the ms server) to UTF-8 but this does not work I have also tried using a collation Latin General on the target but it still says it is wrong. Does anyone have any better solution? Can the database not be simply exported to a .sql and imported? (I tried but phpmyadmin won't accept the file nor will mysql Thanks. Paul -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Paul Groves wrote:
hi,
I am having trouble migrating a database from M$ SQL to My SQL. The source is a server running MS SQL 2012 R2 and all the server does is run a moodle site. Destination is our opensuse 42.1 web server. running mariadb.
I have tried using the MySQL Workbench, but it fails on most of the data on the Bulk Data Transfer part. It has numerous errors.
Most commonly (with almost all the tables) it says the data already exists at the destination. (It was a brand new database and I have checked with phpmyadmin and there is nothing there!!)
Also it errors with incorrect database encoding. The old database is in Latin General.
I googled that charset a bit, and there seems to be some issues in converting it to UTF-8, see e.g.: http://stackoverflow.com/questions/10124930/encoding-sql-latin1-general-cp1-...
I have tried making a copy (on the ms server) to UTF-8 but this does not work I have also tried using a collation Latin General on the target but it still says it is wrong.
If you can, I would go for UTF-8, it's far more future proof.
Does anyone have any better solution? Can the database not be simply exported to a .sql and imported?
Knowing nothing about M$SQL, it seems to me you ought to be able to export all the data (in the style of mysqldump) to a text file, and then load that into mysql. The data might need some massaging I suppose. -- Per Jessen, Zürich (9.6°C) http://www.hostsuisse.com/ - virtual servers, made in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On Tuesday, 26 April 2016 09:23:36 EEST Paul Groves wrote:
hi,
I am having trouble migrating a database from M$ SQL to My SQL. The source is a server running MS SQL 2012 R2 and all the server does is run a moodle site. Destination is our opensuse 42.1 web server. running mariadb.
I have tried using the MySQL Workbench, but it fails on most of the data on the Bulk Data Transfer part. It has numerous errors.
Most commonly (with almost all the tables) it says the data already exists at the destination. (It was a brand new database and I have checked with phpmyadmin and there is nothing there!!)
Also it errors with incorrect database encoding. The old database is in Latin General. I have tried making a copy (on the ms server) to UTF-8 but this does not work I have also tried using a collation Latin General on the target but it still says it is wrong.
Does anyone have any better solution? Can the database not be simply exported to a .sql and imported? (I tried but phpmyadmin won't accept the file nor will mysql
One option is to use a program to connect to both databases and write queries to copy the data manually via ODBC or JDBC. One such program is MS Access.
Thanks.
Paul -- Regards, Peter -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Per, you can export the data but it only runs the query and cannot save the file. Alternatively I can general an sql script but the data is unreadable by mysql (and phpmy admin with MSSQL compatibility). auxsvr, I have tried multiple migration tools. including MS access Oracle MySQL workbench Oracle mysql migration tool DPRU Database Converter-MS SQL to MySQL They all error in the ways i mentioned above. (or in the case of DPRU will not even authenticate to MSSQL, and access only works with up to 255 rows and ignores the rest). Anyone would thing M$ are trying to force you to use their product.... :/ Very stumped here.... are there any official migration processes? (that work because oracle's or MS's do not actually transfer the data, only the schema). On 26 April 2016 at 09:46, auxsvr <auxsvr@gmail.com> wrote:
On Tuesday, 26 April 2016 09:23:36 EEST Paul Groves wrote:
hi,
I am having trouble migrating a database from M$ SQL to My SQL. The source is a server running MS SQL 2012 R2 and all the server does is run a moodle site. Destination is our opensuse 42.1 web server. running mariadb.
I have tried using the MySQL Workbench, but it fails on most of the data on the Bulk Data Transfer part. It has numerous errors.
Most commonly (with almost all the tables) it says the data already exists at the destination. (It was a brand new database and I have checked with phpmyadmin and there is nothing there!!)
Also it errors with incorrect database encoding. The old database is in Latin General. I have tried making a copy (on the ms server) to UTF-8 but this does not work I have also tried using a collation Latin General on the target but it still says it is wrong.
Does anyone have any better solution? Can the database not be simply exported to a .sql and imported? (I tried but phpmyadmin won't accept the file nor will mysql
One option is to use a program to connect to both databases and write queries to copy the data manually via ODBC or JDBC. One such program is MS Access.
Thanks.
Paul -- Regards, Peter -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Paul Groves wrote:
Per,
you can export the data but it only runs the query and cannot save the file. Alternatively I can general an sql script but the data is unreadable by mysql (and phpmy admin with MSSQL compatibility).
Hi Paul if you can get the data out as a text file with sql, it might take a little work to load it, but it should be possible. If you want, put it up somewhere or send it to me, and I'll be happy to have a look or try to load it into a database. (I even used to host a moodle setup for a customer some years ago). -- Per Jessen, Zürich (9.7°C) http://www.dns24.ch/ - your free DNS host, made in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On Tuesday, 26 April 2016 10:13:19 EEST Paul Groves wrote:
you can export the data but it only runs the query and cannot save the file. Alternatively I can general an sql script but the data is unreadable by mysql (and phpmy admin with MSSQL compatibility).
Error messages, please. A data dump from MSSQL may need extra care to import due to line terminator differences. Also, did you try using CHARACTER SET in the dump file, cf. load data[1]?
I have tried multiple migration tools. including MS access Oracle MySQL workbench Oracle mysql migration tool DPRU Database Converter-MS SQL to MySQL
They all error in the ways i mentioned above. (or in the case of DPRU will not even authenticate to MSSQL, and access only works with up to 255 rows and ignores the rest).
Anyone would thing M$ are trying to force you to use their product.... :/
In general, I avoid automated tools to convert databases, because they almost always fail and often mangle data. Did you link or did you import the tables in Access? Importing may fail due to the 2GB size limit. -------- [1] http://dev.mysql.com/doc/refman/5.7/en/load-data.html -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On 2016-04-26 13:15, auxsvr wrote:
In general, I avoid automated tools to convert databases, because they almost always fail and often mangle data. Did you link or did you import the tables in Access? Importing may fail due to the 2GB size limit.
If my memory doesn't fail me much (I used access ages ago), with access you can open the tables or import (I forget which) into excel. From there, you could dump as csv. Or save and open in Linux with LibreOffice. -- Cheers / Saludos, Carlos E. R. (from 13.1 x86_64 "Bottle" at Telcontar)
per, I would but when I output a text file from M$ SQL it comes out as gobledegook M$ format and I cannot view it in anything. auxsvr, I linked the table, Access is useless because it only allows me to view / manage / export 255 rows! Anyway... some progress.., I created an odbc for the M$ SQL on my workstation. Then installed http://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.... then made an ODBC to the new database on the mariadb (MySQL) Server Opened up two new files in LibreOffice Base, one for each database. If I drag and drop the tables over (selecting all columns on each one) It moves the data! yay :D and I can see it in phpmyadmin in a readable format! (In UTF-8 too). So I assume libreoffice is doing a select * from table query and then an insert table on the target DB. Once I have done all 67 tables and moved the moodle files, hopefully it will work fine. I will post back with progress. See you in a few decades... On 26 April 2016 at 12:59, Carlos E. R. <robin.listas@telefonica.net> wrote:
On 2016-04-26 13:15, auxsvr wrote:
In general, I avoid automated tools to convert databases, because they almost always fail and often mangle data. Did you link or did you import the tables in Access? Importing may fail due to the 2GB size limit.
If my memory doesn't fail me much (I used access ages ago), with access you can open the tables or import (I forget which) into excel. From there, you could dump as csv. Or save and open in Linux with LibreOffice.
-- Cheers / Saludos,
Carlos E. R. (from 13.1 x86_64 "Bottle" at Telcontar)
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
OK, it works! Using libreoffice and manually copying the tables worked perfectly. Moodle is now running on the linux server no problems. On 26 April 2016 at 14:08, Paul Groves <paul.groves.787@gmail.com> wrote:
per,
I would but when I output a text file from M$ SQL it comes out as gobledegook M$ format and I cannot view it in anything.
auxsvr,
I linked the table, Access is useless because it only allows me to view / manage / export 255 rows!
Anyway... some progress..,
I created an odbc for the M$ SQL on my workstation. Then installed http://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.... then made an ODBC to the new database on the mariadb (MySQL) Server
Opened up two new files in LibreOffice Base, one for each database.
If I drag and drop the tables over (selecting all columns on each one) It moves the data! yay :D and I can see it in phpmyadmin in a readable format! (In UTF-8 too). So I assume libreoffice is doing a select * from table query and then an insert table on the target DB.
Once I have done all 67 tables and moved the moodle files, hopefully it will work fine. I will post back with progress.
See you in a few decades...
On 26 April 2016 at 12:59, Carlos E. R. <robin.listas@telefonica.net> wrote:
On 2016-04-26 13:15, auxsvr wrote:
In general, I avoid automated tools to convert databases, because they almost always fail and often mangle data. Did you link or did you import the tables in Access? Importing may fail due to the 2GB size limit.
If my memory doesn't fail me much (I used access ages ago), with access you can open the tables or import (I forget which) into excel. From there, you could dump as csv. Or save and open in Linux with LibreOffice.
-- Cheers / Saludos,
Carlos E. R. (from 13.1 x86_64 "Bottle" at Telcontar)
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
participants (4)
-
auxsvr -
Carlos E. R. -
Paul Groves -
Per Jessen