mssql2mysql.0.10.txt (version 0.10, Nov 12th 2003 for MySQL 3.23 and 4.0)
mssql2mysql.txt is a script file. To use it, you have to change a few lines of code.
Other versions:
- Converter with GUI: If you prefer more comfort, there are also two graphical user interfaces (GUIs) available. Please read the instructions for GUI versions of the converter!
- Converter Access to MySQL: Andre Steenveld has adapted mssql2mysql for Microsoft Access. If you want to convert Access databases to MySQL, see msacc2mysql.
mssql2mysql is available for free.
License: GPL
Initial copyright: Michael
Kofler (2001).
- Converts both schema (tables and indices) and
data (numbers, strings, dates etc.).
- Copes with table and column names which are not legal in
MySQL. (Spaces etc. are replaced by _.)
- Either writes all SQL commands into a text file or executes these commands directly at a connected MySQL server (i.e. no need for huge temporary files).
mssql2mysql has been tested using Microsoft SQL Server 7 + 2000, MySQL 3.23.28 / 4.0.4 / 5.0.2, Connector/ODBC 3.51 and Visual Basic 6 / VBA 6. Please note:
- mssql2mysql 0.10 should work with all MySQL versions beginning with 3.23.
- mssql2mysql 0.11 and higher assumes MySQL 4.1 or higher!
- If you use MySQL 4.1 or higher, you MUST use Connector/ODBC 3.51.10 or higher. Otherwise, you will get an 'authentification error'.
mssql2mysql seems to be able to correctly convert pubs, northwind and a number of sample databases of the author. I won't guarantee for the correctness of data, though.
- No support for foreign keys, SPs, triggers, views and user defined data types.
- AUTO_INCREMENT 1: MySQL does not support tables with more than one AUTO_INCREMENT column; the converter does not check this, so change the table definition in Microsoft SQL Server before starting the conversion.
- AUTO_INCREMENT 2: The AUTO_INCREMENT column must be a key column; the converter does not check this, so use Microsoft SQL Server to add an index to the AUTO_INCREMENT column before starting the conversion.
- No conversion of privileges/access infos (the idea of logins/users in Microsoft SQL Server is incompatible with the user/group/database/table/column privileges system of MySQL).
- Microsoft SQL Server TIMESTAMPS are incompatible with the TIMESTAMPS of MySQL. Therefore, they are converted to TINYBLOBs.
- The script version offers no visible feedback during conversion process.
- mssql2mysql is a Visual Basic script; you need either
Visual Basic 6 or a program with VBA 6 environment (Word 2000/XP, Excel
2000/XP, Access 2000/XP etc.) to run this script. Unfortunately, Windows
Scripting Host is not sufficient.
The script uses the ADO library (tested with versions 2.5, 2.7 and 2.8, but should work with other versions also), the SQLDMO library (tested with the version which comes with SQL Server 7 and 2000) and the Microsoft Scripting library. All of this should be on your computer if you have both SQL Server and VBA6/VB6 installed.
- Microsoft SQL Server. (Tested with version 7 and 2000.)
- MySQL. (Tested with 3.23.38, 4.0.4 and 5.0.2.)
Please use the old mssql2mysql 0.10 version for MySQL 3.23 and 4.0. Use the current version of mssql2mysql for MySQL 4.1 or higher.
- MyODBC alias Connector/ODBC. (Tested with 2.50.36 and 3.51.)
Connector/ODBC is only needed if you want to connect directly to
MySQL. You can also write a text file with SQL statements. In this
case, Connector/ODBC is not needed.
If you use MySQL 4.1 or higher, you MUST use Connector/ODBC 3.51.10 or higher. Otherwise, you will get an 'authentification error'.
- Copy mssql2mysql.txt into an empty form of a new VB6 project.
- Change the constants at the beginning of the code.
- Hit F5 to run.
If you have a VBA6 capable program
- Start Excel 2000 or Word 2000 or another program with VBA editor, hit Alt+F11, execute Insert|Module. Then copy mssql2mysql.txt into the new VBA 6 module.
- Change the constants at the beginning of the code.
- Hit F5 and execute Main().
' MSSQL login
Const MSSQL_SECURE_LOGIN = True 'login type (True for NT security)
Const MSSQL_LOGIN_NAME = "" 'login name (for NT security use "" here)
Const MSSQL_PASSWORD = "" 'password (for NT security use "" here)
Const MSSQL_HOST = "mars" 'hostname (if localhost: use "(local)")
Const MSSQL_DB_NAME = "pubs" 'database name
' mode
Const OUTPUT_TO_FILE = 0 '1 --> write file;
'0 --> connect to MySQL, execute SQL
' commands directly
'output file (only needed if OUTPUT_TO_FILE=1)
Const OUTPUT_FILENAME = "c:\export.sql"
'connect to MySQL (only needed if OUTPUT_TO_FILE=0)
Const MYSQL_USER_NAME = "root" 'login name
Const MYSQL_PASSWORD = "xxx" 'password
Const MYSQL_HOST = "hostname" 'if localhost: use "localhost"
Const MYSQL_PORT = 3306 'change if you use another port
Const MyODBCVersion = "MySQL" 'for MyODBC 2.51.*; if you use MyODBC 3.51*, use
'this setting instead: "MySQL ODBC 3.51 Driver"
' options
Const NEW_DB_NAME = "" 'name of new MySQL database ("" if same
' as MSSQL db name)
Const DROP_DATABASE = True 'begin with DROP dbname?
Const MAX_RECORDS = 0 'max. nr of records per table
'(0 for all records, n for testing purposes)
' new options in mssql2mysql 0.11 or higher
Const VARCHAR_MAX = 255 'max. no. of characters in VARCHAR
'255 for MySQL <= 5.0.2
'ca. 32000 for MySQL >= 5.0.3 (depends on char. set, max. 65535 bytes)
Const TABLE_ENGINE = "InnoDB" 'MyISAM or InnoDB
Const CHARSET = "utf8" 'character set for all text columns
Const COLLATION = "" 'collation; leave empty if you want to use the default collation for
'the specified character set
' old option for mssql2mysql 0.10 or lower (no longer needed in 0.11, see CHARSET)
Const UNICODE_TO_BLOB = False 'unicode -> BLOBs (True) or ANSI (False)
Some more details:
MSSQL_SECURE_LOGIN (True/False) specifies whether mssql2mysql should use the NT/2000 security system to login at MSSQL. If this is True, neither username nor password need to be specified.
DROP_DATABASE (True/False) specifies whether mssql2mysql should start by dropping the MySQL database of a previous conversion attempt (if one already exists).
MAX_RECORDS (n) specifies how many records per table should be converted. 0 means all records. This option allows to do a quick check (i.e. MAX_RECORDS=10) before trying to convert the whole database (which may take some time and may give an error at the very last table).
TABLE_ENGINE: Specifies the table type (MyISAM or InnoDB).
CHARSET and COLLATION: Specify which characterset and collation should be used for all text columns. If COLLATION is empty, the default collation for the choosen charset is used.
UNICODE_TO_BLOB (True/False) specifies how mssql2mysql 0.10 or lower should deal with unicode strings. True means, that Unicode columns are converted to BLOBs. (MySQL cannot order/sort BLOBs with unicode data, though!) If UNICODE_TO_BLOB is set to False, mssql2mysql converts Unicode strings to ANSI strings. This works fine if MSSQL contains ANSI data anyway. Otherwise, you will loose data! As of mssql2mysql 0.11, UNICODE_TO_BLOB is obsolete (see CHARSET).
Version 0.02 (June 23th 2001): better handling of decimal numbers, changes by dave.whitla-at-ocean.net.au
Version 0.03 (August 7th 2001): compatibility with Office 97, changes by DaveMeaker-at-angelshade.com (uncomment Replace function at the end of the script!)
Version 0.04 (September 30th 2001): slightly faster (ideas by janivar-at-acos.no)
Version 0.05 (April 8 2002): compatible with MyODBC 3.51 (set variable MyODBCVersion accordingly!; thanks to Silvio Iaccarino)
Version 0.05a (October 17 2002): support for BIGINT (thanks to Ugo Gioia)
Version 0.06 (December 18 2002): support for adBinary (thanks to Roberto Alicata); new handling of TIMESTAMPs (Michael Kofler): TIMESTAMPs (MSSQL) are now converted to TINYBLOBs (MySQL)
Version 0.07 (January 27 2003): allow specifiaction of MySQL Server port (Michael Kofler)
Version 0.08 (April 27 2003): Boolean now converts to 1/0 instead of -1/0 for True/False (Michael Kofler)
Version 0.09 (August 19 2003): better GUID conversion (thanks to Hermann Wiesner)
Version 0.10 (November 12 2003): better handling of table and field names in function MySQLName (thanks to Carlo)
Version 0.11 (Mar. 2nd 2005): needs MySQL 4.1 or higher! New
functions:
- supports myisam and innodb tables
- per
default all text columns are now created with character set utf8 and
collation utf8_general_ci; you may choose another charset + collation
(constants CHARSET and COLLATION)
Version 0.12 (Mar. 31st 2005): ignores hypothetical indexes (thanks to Brian K. Andersen)
For those who don't want to deal with code directly, two user interfaces based on mssql2mysql are available. Both are based on Visual Basic 6. To run the programs, you need the VB6 runtime libraries including ADO 2.7. To change the programs, you need Visual Basic 6. If you don't meet these requirements, you are better off with the script version!
mssql2mysql-gui.zip from Lee Leahu (lee-at-ricis.com) provides a configuration dialog and gives feedback about the progress of the conversion process. The conversion is based on the same code as in mssql2mysql 0.05.
mssql2mysql-gui2.zip from Roberto Alicata (not75-at-katamail.com) has an even nicer user interface. You can individually select every table you want to convert. The conversion uses the same code as in mssql2mysql 0.06.
Andre Steenveld (papa-at-catv2206-dot-extern-dot-kun-dot-nl) has adapted mssql2mysql for Microsoft Access. The resulting script is msacc2mysql.txt. Please see the script for further instructions.
MySQL Buch (original German edition) von Michael Kofler. Hier finden Sie auch eine Menge MySQL Internet-Links.
Zurück zur deutschen Homepage.