Introduction

MSSQL-to-MySQL is a program to move MS SQL databases to MySQL server. Depending on your privileges on the target MySQL server you can export MS SQL data into new database or overwrite the contents of existing MySQL database.

Features

  • All versions of Microsoft SQL are supported (including SQL Azure)
  • All versions of Linux/Unix and Windows MySQL servers are supported
  • All MS SQL data types and attributes are supported
  • Option to export MS SQL database into a local MySQL dump file (see Customize Conversion article for related information)
  • Option to customize types mapping (see Edit Table article for related information)
  • Option to convert individual tables (see Select Tables article for related information)
  • Filter data to convert via SELECT-queries (see Using Queries article for related information)
  • Special approach for VServer users
  • Supports multibyte character sets
  • Converts indexes with all necessary attributes
  • Converts foreign keys
  • Converts MS SQL views into MySQL format
  • Command line support
  • Option to merge MS SQL data into an existing MySQL database
  • Synchronizes MySQL database with MS SQL data
  • Stores configuration into profile
  • Quick Launch
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • MS SQL environment (server or client components of MS SQL should be installed on the same machine where you run MSSQL-to-MySQL from)
  • Necessary privileges to write into the destination database

Limitations

  • Does not convert stored procedures


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be migrated to MySQL server as if it would be a regular table. Select name of table where you wish to store query results in the desintation database. Enter query in the corresponding field. Also, you can test query on source database by pushing "Test Query" button. If query is not correct please check that it is a correct SQL SELECT-statement and all table and field names are spelled properly. You can always check query by running it from SQL Query Analyzer or any similar tool.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call conversion procedure from an automation script you can use console version of MSSQL-to-MySQL called M2SAGENT.EXE. Find this file in MSSQL-to-MySQL installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--as_dbo | connect to MS SQL server as database owner
--charset=... | MySQL character set
--dest=... | MySQL database or dump file name
--dump | convert MS SQL database into dump file
--engine=... | MySQL storage engine (MyISAM, MEMORY, InnoDB, BerkeleyDB, ARCHIVE, FEDERATED, etc). Please refer to MySQL documentation for the further information.
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge, 4 - synchronize)
--mssqlh=... | MS SQL server IP address or network name
--mssqlu=... | MS SQL user name
--mssqlp=... | MS SQL user password
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--owned | convert tables owned by the user only
--port=... | MySQL port
--profile=... | path to the file to load conversion settings from
--silent | use this option to disable program output
--skip_idx | skip converting indexes
--src=... | MS SQL database name
--tab_def | convert table definitions only
--tab_file=... | name of the file containing table names to convert (one table name per line)
--views | convert MS SQL views into MySQL

In the following example the program converts local MS SQL database "db1" into MySQL database "db1 from mssql" on the remote MySQL server "mysqlhost" using table names file "c:\tabfile1.txt":

M2SAGENT.EXE --src=db1 --dest="db1 from mssql" --mysqlh=mysqlhost --mysqlu=administrator
--mysqlp=the_passsword --tab_file=c:\tabfile1.txt

Table names file should be formatted as follows:

Table_1
Table_2
...
Table_N

Notes:

  1. If you specified a profile, there is no need to specify any other parameters
  2. You can omit 'mssqlh' or 'mysqlh' to connect local database server
  3. You can omit 'mssqlu' and 'mssqlp' parameters to connect to MS SQL server using Windows authentication
  4. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  5. If you omit 'tab_file' parameter, all database tables will be converted
  6. If you omit 'mode' parameter, default mode 'overwrite the entire database' will be used
  7. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")

MySQL character sets

This parameter specifies correct encoding of text values in the destination MySQL database. It can have one of the following values:

value | Description | value | Description
big5 | Big5 Traditional Chinese | dec8 | DEC West European
cp850 | DOS West European | hp8 | HP West European
koi8r | KOI8-R Relcom Russian | latin1 | cp1252 West European
latin2 | ISO 8859-2 Central European | swe7 | 7bit Swedish
ascii | US ASCII | ujis | EUC-JP Japanese
sjis | Shift-JIS Japanese | hebrew | ISO 8859-8 Hebrew
tis620 | TIS620 Thai | euckr | EUC-KR Korean
koi8u | KOI8-U Ukrainian | gb2312 | GB2312 Simplified Chinese
greek | ISO 8859-7 Greek | cp1250 | Windows Central European
gbk | GBK Simplified Chinese | latin5 | ISO 8859-9 Turkish
armscii8 | ARMSCII-8 Armenian | utf8 | UTF-8 Unicode
ucs2 | UCS-2 Unicode | cp866 | DOS Russian
keybcs2 | DOS Kamenicky Czech-Slovak | macce | Mac Central European
macroman | Mac West European | cp852 | DOS Central European
latin7 | ISO 8859-13 Baltic | cp1251 | Windows Cyrillic
cp1256 | Windows Arabic | cp1257 | Windows Baltic
binary | Binary pseudo charset | geostd8 | GEOSTD8 Georgian
cp932 | SJIS for Windows Japanese | eucjpms | UJIS for Windows Japanese

Please refer to MySQL documentation to better understand the concept of character sets and collations.


Connect to MS SQL Server

On this wizard screen you should enter the necessary settings to establish connection with Microsoft SQL Server. In "MS SQL Server" field you should specify the name of an instance of MS SQL Server to which you want to connect. If you leave it empty, the local server is assumed. Also, you can specify custom MS SQL port (other than 1433) in "MS SQL Server" field as follows:

Sql_server_name,1234

where 1234 is the custom port. Then you should specify the type of authentication to use when connecting to MS SQL Server:

  • Windows authentication - SQL Server will validate the connection using the Windows NT user information. This option is only available when connecting to an instance of MS SQL Server on Windows NT. The client should be member of a Windows NT/2000 domain or workgroup. The user needs to be validated as a Windows NT user before access is granted.
  • SQL Server authentication - SQL Server will use the standard security validation. To log in you should specify user name and password recognized on the target MS SQL Server.

In "SQL Server authentication" mode MSSQL-to-MySQL shows database tables owned by the specified user only. You can change it using "Connect as database owner" checkbox. It will force the program to display all tables from "dbo" tablespace, but only if the specified user has enough privileges to read them. Otherwise, the option will not take effect.

If you are connecting to Azure SQL, see How to connect to Windows Azure SQL article for related information.


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. MSSQL-to-MySQL is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MSSQL-to-MySQL from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import MS SQL database contents into an existing MySQL database. See VServer User Notes article for related information.

If you're willing to connecting to MySQL over SSH, see Connecting to MySQL over SSH article for related information.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MSSQL-to-MySQL. See Troubleshooting article to learn how to resolve this problem.


Connecting to MySQL over SSH

Database administrators and security people know that it is good practice to open only local access to MySQL server for security reasons. However, if you want to connect any of Intelligent Converters applications to MySQL server hosted on a Linux/Unix system, it is necessary to open remote access from your local IP address on the destination server. Obviously it makes MySQL server less secure. To improve the situation you can use SSH protocol for secure data transfer between local machine running Intelligent Converters application and MySQL server.

The most easy way of connecting MySQL through SSH is to use port-forwarding through an SSH tunnel. This technique is based on creating an alias that makes any client application think it's connecting to "localhost" machine, while it's really connecting to the remote server through the tunnel.

If you are using the command line ssh, the command would look like this:

ssh -L <localport>localhost<remoteport> <username>@<servername>

If you use the standard MySQL port, both "localport" and "remoteport" must be set to 3306. You can do the same via tools having graphical user interface like Putty or SecureCRT.

Once you've got the ssh tunnel going, you can open any of Intelligent Converters application and enter in the details for remote MySQL server, using 'localhost' as the server host. See below example of MySQL connection page for Access-to-MySQL converter.


Conversion Settings

On this wizard screen you can customize the conversion process using the following settings:

  • Convert table definitions only - select this checkbox to convert only table definitions of MS SQL database. All the data will not be migrated.
  • Convert views - select this checkbox to convert views
  • MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
  • MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
  • Skip converting indexes - select this checkbox if you don't want to convert indexes
  • View tables owned by the user - by default the program lists all tables found in the database specified. Select this checkbox to work with tables owned by the specified MS SQL user only.


Customize Conversion

MSSQL-to-MySQL provides two options to convert MS SQL database into MySQL data source:

  • Move to MySQL server directly - use this approach to export the contents of the source database into a database on the target MySQL server. You need MySQL server launched and also necessary privileges to write into a database on the target server. See Connect to MySQL Server article for related information.
  • Store into dump file - use this approach to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the source database into a local dump file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. See Importing dump files article for related information.

You can choose one of these approaches on the first page of MSSQL-to-MySQL wizard using the corresponding radio buttons.


Edit Table

On this wizard screen users can edit table name and customize types mapping.

MSSQL-to-MySQL converts Microsoft SQL types into the most appropriate MySQL types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes for the original Microsoft SQL database, new custom types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.

See Types Mapping article to learn more about safe mapping Microsoft SQL types into MySQL types.


Frequently Asked Questions

On this page you can find the most frequently asked questions regarding MSSQL-to-MySQL. If your question is not answered here, please contact us at service@convert-in.com

  • What versions of MS SQL are supported?
  • Does MSSQL-to-MySQL require MS SQL server installation?
  • How to specify custom MS SQL port (other than 1433)?
  • Does MSSQL-to-MySQL support batch conversion?
  • All table names include schema name as a prefix. How can I avoid it?
  • How to change destination table name?
  • How can MSSQL-to-MySQL connect to Azure SQL?
  • It is possible to connect to MySQL over SSH with MSSQL-to-MySQL?
  • How to uninstall MSSQL-to-MySQL?

What versions of MS SQL are supported?
MSSQL-to-MySQL works with all versions of MS SQL including Azure SQL.
Go to Top

Does MSSQL-to-MySQL require MS SQL server installation?
MSSQL-to-MySQL requires MS SQL environment (server or client components of MS SQL must be installed on the same machine where you run MSSQL-to-MySQL from).
Go to Top

How to specify custom MS SQL port (other than 1433)?
You can specify custom port on 'Step 1 of 6' wizard page in 'MS SQL Server' field as follows:

Sql_server_name,1234

where 1234 is the custom port.
Go to Top

Does MSSQL-to-MySQL support batch conversion?
Yes, MSSQL-to-MySQL supports command line arguments and so it can handle batch conversion. See Command Line article for related information.
Go to Top

All table names include schema name as a prefix. How can I avoid it?
By default the program lists all tables found in the database. Because different schemas could contain tables with the same name, MSSQL-to-MySQL compose full table name as [schema name].[table name] to guaranty that it is unique. If you need to work with tables owned by the user specified, select "View tables owned by the user" checkbox on "Select Databases" wizard page. This will force the program to not include schema name as prefix.
Go to top

How to change destination table name?
You can convert contents of the table as result of SELECT-query with custom name. Click "Add Query" button on "Select tables" wizard page and compose simple query as follows:

SELECT * FROM <original table name>
Then store it with the name you wish to assign. Of course, you need to place actual MS SQL table name in the query above.
Go to top

How can MSSQL-to-MySQL connect to Azure SQL?
See How to connect to SQL Azure article for related information.
Go to top

It is possible to connect to MySQL over SSH with MSSQL-to-MySQL?
Yes, MSSQL-to-MySQL supports SSH connection to MySQL server. Please refer to this article for the further information.
Go to Top

How to uninstall MSSQL-to-MySQL?
If you need to remove MSSQL-to-MySQL products from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE mss2sql.log
Go to top


How to connect to SQL Azure

This article described how to connect to Windows SQL Azure from MSSQL-to-MySQL. First, you should make sure that your IP address in the list of allowed IPs:

1. open the Dashboard and click on the server you want to configure

2. click on "Show firewall settings" link

3. click on "Add client IP" link and make sure that the control "Allow access to Azure services" is ON

Now it's time to get the name of Azure Server:

  • go back to Dashboard and click on database name you want to use (it is "test0425" on the 1st screenshot above)
  • you will see window like on the screenshot below
  • locate "Server name" item and name of the Azure Server follows right after it ("ic0425.database.windows.net" for our example)

Finally, run MSSQL-to-MySQL. On "Connect to MS SQL server" page you need to select "SQL Server authentication" option, enter the name of Azure SQL Server, username and password (from registration of Azure SQL service on windowsazure.com website) into the appropriate fields.


Importing dump files

This article explains how to move dump file contents to MySQL server using the most popular tools.

1. MySQL console client (mysql)
Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call MySQL console client as follows:

mysql --host=... --user=... --password=... -vvf < dump_file_name

Otherwise:

mysql --host=... --user=... --password=... -vvf MySQL_database_name < dump_file_name

where 'MySQL_database_name' is a name of an existing database.

2. phpMyAdmin
Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain 'CREATE DATABASE' statement.

Step 1. Click on the appropriate database name in the left frame.

Step 2. Click 'SQL' tab on the top of the screen.

Step 3. Enter full path to the dump file in 'Location of the textfile' field or click 'Browse' button to locate it through the directory tree.

Step 4. Click 'Go' button.


Merging and Overwriting

If you try to import MS SQL database into an existing MySQL database, MSSQL-to-MySQL allows you to choose one of the following options: overwrite the entire database, overwrite existing tables only, merge into the database or specify another destination name.

  • Select 'Overwrite the entire database' option to replace the entire contents of the destination MySQL database with MS SQL data. Note that all data contained in MySQL database will be lost.
  • Select 'Overwrite existing tables only' option to replace the contents of MySQL tables having the same names as tables in source MS SQL database.
  • Select 'Skip existing tables only' option to not convert existing tables.
  • Select 'Merge' option to merge MS SQL data into the destination MySQL database. MSSQL-to-MySQL implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into MySQL database only those records whose key/index value does not exist in the destination MySQL table. Otherwise, MSSQL-to-MySQL appends the whole table contents to the corresponding MySQL table. If the structures of source and destination tables are different, the program transforms each record of the source table so that it conforms to the destination table structure.
  • Select 'Synchronize' option to update existing MySQL tables with MS SQL data. See Synchronization article for the further information.

If you decide to enter another destination name just click 'Cancel' button.


MySQL dump file settings

On this wizard screen users can specify how the resulting dump file should be constructed. There are following settings available:

  • MySQL version - this parameter determines the syntax of MySQL statements being used in output dump file. 'Old' versions of MySQL (before 3.23.6) put more restrictions on database objects names. Note that 'new' syntax is not accepted by 'old' versions of MySQL. Use 'old' syntax whenever you are not sure about the version of your MySQL server.
  • Line breaks - this parameter specifies how to separate text lines in the dump file. If the destination MySQL server is running on MS Windows platform, you should select 'MS Windows style' option. Otherwise select 'Unix style'.
  • Add lock for write around 'INSERT' statements - it makes importing the contents of the dump file into a database much faster. To use this feature in MySQL 4.0 and higher you need global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.
  • Insert 'CREATE DATABASE' statement - select this option to put 'CREATE DATABASE' statement in the resulting dump file. This setting doesn't take effect without the option below activated.
  • Insert 'CREATE TABLE' statements - select this option to put 'CREATE TABLE' statements in the resulting dump file. You should always select it in order to obtain a valid dump file. The only exception is appending the data to existing tables.
  • Insert comments - select this option to add brief comments. It makes the resulting dump file more readable.

See Importing dump files article to learn how to move dump file contents to MySQL server.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MS SQL and MySQL connection information (host, user name, etc) and all necessary settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.m2s) file with left mouse button. This will cause MSSQL-to-MySQL wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Select Databases

On this wizard screen you should enter the source MS SQL database name and MySQL database name or path to a dump file (depending on which conversion scenario you choose). Is is possible to enter MS SQL database name directly or drop down the combo box to see all available databases. Note, that you should have sufficient privileges to browse list of available databases. If you specify the name of existing MySQL database or dump file it will be overwritten.


Select Tables

On this wizard screen you can manage the list of MS SQL tables to convert. In the left part of the screen you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new MS SQL table for converting into MySQL format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MS SQL tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

Push "Add Query" button to add SELECT-query filtering data that will be migrated to MySQL server. See Using Queries article for the further information.

To edit selected table or query double click the corresponding item in "Selected tables" list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information. If selected item is a query, you will see dialog box to edit SQL-statement of the query.

NOTE: if you see complex table names including schema (like [dbo].[table1]) while you need it in simple form, return on the previous screen and select "View tables owned by the user" checkbox.


Select Views

On this wizard screen you can manage the list of Microsoft SQL views to convert into MySQL. In the left part of the screen you can see "Available views" list box. It contains all available views of the source database except those that you have selected already. On the right you can see "Selected views" list box. To add new MS SQL view for converting into MySQL you should select it in "Available queries" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to add all available MS SQL views. To remove an item from "Selected views" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected views" list box.

Some views may depend on another views that must be created first. Use the up and down buttons to control the order in which views are converted. Highlight an item in "Selected views" list and then you will be able to move it up and down using those buttons.


Synchronization

MSSQL-to-MySQL implements synchronization as combination of insert MS SQL records that are missing in MySQL table and update existing MySQL records with MS SQL data. For synchronization purpose source and destination tables must have identical structures and have primary key or unique index defined.

Assume both MS SQL and MySQL databases have table "People" defined as below:

    Table1(
        ID Integer NOT NULL,
        FName Varchar(50),
        LName Varchar(50),
        EMail Varchar(50),
        Primary Key (ID)
    );

This table has the following contents in MS SQL database:

2 | Andrew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com

In MySQL database:

1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@yahoo.com
After migrating MS SQL data to MySQL in "Synchronize" mode MS SQL table is kept untouched and MySQL table is modified as (pay attention to the cell marked red):
1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com


Troubleshooting

While working with MSSQL-to-MySQL wizard you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

  • Error message "The program does not support authentication protocol requested by server..."
  • Error message "Duplicate field values being used for an unique index were found..."
  • Error message "Can't connect to MySQL server on <host name or IP>"
  • Error message "User is not allowed to read the source database"
  • Some default values are missing in the resulting MySQL database
  • Error message "Access denied for user: <user name>"
  • Error message "Transferred value is too big. Increase MySQL max packet size and try again..."
  • I cannot see any tables after connecting to MS SQL database

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MSSQL-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with MSSQL-to-MySQL. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with MSSQL-to-MySQL.
Go to top

P: Error message "Duplicate field values being used for an unique index were found..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating MS SQL data into a new MySQL database, you can either edit source database in order to resolve the conflict manually or restart the program with "Skip converting indexes" checkbox selected. When appending MS SQL data to an existing MySQL database, you should remove the conflicting unique index in the destination MySQL database.
Go to top

P: Error message "Can't connect to MySQL server on <host name or IP>"
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.
Go to top

P: Error message "User is not allowed to read the source database"
R: MS SQL server user should have enough permissions to execute SELECT queries on the source database.
Go to top

P: Some default values are missing in the resulting MySQL database
R: MSSQL-to-MySQL ignores default values for text, ntext, or image MS SQL columns because MySQL does not allow default values for variable size fields.
Go to top

P: Error message "Access denied for user: <user name>"
R: You will not be able to connect to the target MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you sufficient permissions to create new databases on the target MySQL server. See VServer User Notes article for related information.
Go to top

P: Error message "Transferred value is too big. Increase MySQL max packet size and try again..."
R: The error mostly caused by BLOB values that are larger than max_allowed_packet. Sometime you may see "packet too large" as part of the error message, and that confirms that you need to increase max_allowed_packet value. You can increase MySQL max allowed packet size with one of the following options:

  1. restarting server daemon as
    mysqld --max_allowed_packet=16M
  2. running the following query though mysql client
    SET GLOBAL max_allowed_packet=16M
  3. setting max_allowed_packet in option file ("my.ini" or "my.cnf")
    [mysqld]
max_allowed_packet=16M
Go to top

P: I cannot see any tables after connecting to MS SQL database
R: In "SQL Server authentication" mode MSSQL-to-MySQL shows tables owned by the specified user only. You can change it using "Connect as database owner" checkbox on "Connect to MS SQL Server" wizard page. It will force the program to display all tables from "dbo" tablespace, but only if the specified user has enough privileges to read them. Otherwise, the option will not take effect.
Go to top

If you came across some other kind of problem while using MSSQL-to-MySQL, please contact our support department at service@convert-in.com


Types Mapping

Microsoft SQL and MySQL have similar set of data types. Some of them are compatible with each other, while the rest are not. When designing the schema of custom (non-standard) types mapping, it is necessary to remember the following compatibility matrix for popular data types of Microsoft SQL and MySQL. In tables below all valid type mappings are marked green (with restriction on data if any), every prohibited or dangerous type mapping is marked orange.

Text / Binary

 | CHAR | VARCHAR | TEXT | NCHAR | NVARCHAR | NTEXT | BINARY | VARBINARY | IMAGE
CHAR | dest. length >= source length | dest. length >= source length | Possible truncation of text | dest. length >= source length | dest. length >= source length | Possible truncation of text |  |  |
VARCHAR | dest. length >= source length | dest. length >= source length | Possible truncation of text | dest. length >= source length | dest. length >= source length | Possible truncation of text |  |  |
BINARY |  |  |  |  |  |  | dest. length >= source length | dest. length >= source length | Possible truncation of data
VARBINARY |  |  |  |  |  |  | dest. length >= source length | dest. length >= source length | Possible truncation of data
TINYBLOB |  |  |  |  |  |  | Source length <= 255 | Source length <= 255 | Possible truncation of data
TINYTEXT |  |  | Possible truncation of data |  |  | Possible truncation of data |  |  |
BLOB |  |  |  |  |  |  |  |  | Possible truncation of data
TEXT |  |  | Possible truncation of data |  |  | Possible truncation of data |  |  |
MEDIUMBLOB |  |  |  |  |  |  |  |  | Possible truncation of data
MEDIUMTEXT |  |  | Possible truncation of data |  |  | Possible truncation of data |  |  |
LONGBLOB |  |  |  |  |  |  |  |  |
LONGTEXT |  |  |  |  |  |  |  |  |

Numeric

 | BIT | TINYINT | SMALLINT | INT | BIGINT | DECIMAL / NUMERIC | MONEY | SMALLMONEY | FLOAT | REAL
BOOLEAN |  |  |  |  |  |  |  |  |  |
TINYINT (-128 ... 127) |  | If source is out of range -128...127 use UNSIGNED | Possible truncation of data | Possible truncation of data |  |  | Possible truncation of data | Possible truncation of data |  |
SMALLINT |  |  |  | Possible truncation of data |  |  | Possible truncation of data | Possible truncation of data |  |
INTEGER |  |  |  |  | Possible truncation of data |  | Possible truncation of data | Possible truncation of data |  |
BIGINT |  |  |  |  |  |  | Possible truncation of data | Possible truncation of data |  |
DECIMAL |  |  |  |  |  | Precision and scale should be the same |  |  | Possible truncation of data | Possible truncation of data
FLOAT |  |  |  |  |  | Possible loss of precision | Possible loss of precision | Possible loss of precision |  | Possible truncation of data
DOUBLE |  |  |  |  |  | Possible loss of precision | Possible loss of precision | Possible loss of precision |  |

Date / time

 | DATE | DATETIME | DATETIME2 | TIME | DATETIMEOFFSET | SMALLDATETIME
DATE |  | Possible truncation of data | Possible truncation of data |  | Possible truncation of data | Possible truncation of data
TIME |  |  |  |  |  |
DATETIME |  |  |  |  |  |
TIMESTAMP |  |  |  |  |  |


Using Queries

Queries give you the ability to extract partial data for migrating to MySQL server. The following examples are provided to illustrate how to use queries for particular purposes. Assume that we have table "Table1" as below:

   Table1(
    ID INT,
    FName VARCHAR(50),
    LName VARCHAR(50),
    Birthday DATE,
    Notes TEXT
   );

Example 1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000
Example 2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

Example 3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE Notes IS NOT NULL

Example 4. Merge two tables.

Assume, you have two tables defined as follows:

   ProductLines(
    productline VARCHAR(50),
        decsription VARCHAR(1000),
    photo       IMAGE
   );
   Products(
    code        VARCHAR(15),
    name        VARCHAR(70),
    productline VARCHAR(50),
    vendor      VARCHAR(50),
    description TEXT,
    quantity    SMALLINT,
    price       FLOAT
   );

If you want to get product code and product name from the "Products" table and description of product lines from the "ProductLines" table, it is possible to be done using the following query:

SELECT code,
       name,
       description
FROM Products T1
INNER JOIN ProductLines T2 ON T1.productline = T2.productline;

You can add queries on "Select Tables" wizard screen. See Select Tables article for related information.


VServer User Notes

If you are using Virtual Server hosting you probably have no sufficient privileges to create new databases on the target MySQL server. For this case we recommend the following approach:

  1. ask your Virtual Server administrator to create a blank database for you
  2. run MSSQL-to-MySQL and select this database as the conversion target
  3. click "Yes" when you will be asked if you'd like to overwrite existing MySQL database

When you complete these steps, the entire contents of the source MS SQL database will be imported into the specified MySQL database.


Contents

Add Query

Command Line

Connect to MS SQL server

Connect to MySQL server

Connecting to MySQL over SSH

Conversion Settings

Customize Conversion

Edit Table

Frequently Asked Questions

How to connect to SQL Azure

Importing dump files

Introduction

Merging and Overwriting

MySQL dump file settings

Profile

Quick Launch

Select Databases

Select Tables

Select Views

Synchronization

Troubleshooting

Types Mapping

Using Queries

VServer User Notes