To import SQLServer2000 data to MySQL5.5

Method 1 of importing SQLServer2000 data to MySQL5.5 install MySQL5.5 (which can be different from SQLServer on the same server) and create an empty database (generally UTF-8 encoded ), used to receive imported data. 2. Install the mysql-connector-odbc-5.3.2-win32 on the SQLServer server. Iii. SQL Server

Method 1: Import SQL Server 2000 data to MySQL 5.5. Install MySQL 5.5 (it can be different from SQL Server on the same Server ), create an empty database (UTF-8 encoding is generally used) to receive imported data. 2. Install the mysql-connector-odbc-5.3.2-win32 on the SQL Server. Iii. SQL Ser

How to import SQL Server 2000 data to MySQL 5.5

1. Install MySQL 5.5 (which may be different from SQL Server) and create an empty database (generally UTF-8 encoded) to receive imported data.

2. Install the mysql-connector-odbc-5.3.2-win32 on the SQL Server.

3. Run "Administrative Tools"> "Data Source (ODBC)" on the SQL Server, switch to the "system DSN" panel, and add "MySQL ODBC 5.3 Unicode Driver ", set "Data Source Name" to any Name, and "TCP/IP Server" and "Port" to the IP address and Port (generally 3306) of the MySQL database ), then, enter the user and password with Database permissions (generally the root user, the root remote access permission is required for MySQL not installed on the local machine), and select "Database" as the Database created in step 2.

4. run SQL Server's Enterprise Manager, right-click the database to be exported, and choose "all tasks"> "export data" from the context menu ". Click Next. First, check whether the Database Name of the data source is correct. (For this step, you can choose Windows authentication or SQL Server Authentication ). Click Next, select "target" as "MySQL ODBC 5.3 Unicode Driver", and then select "User/system DSN" as the Data Source created in step 3. Click Next and select "Copy table and view from database ". Click Next, select all from the source table and view, click next, and then execute immediately.

5. After the data is exported successfully, the table fields in SQL Server are not set successfully in MySQL as the primary key and self-increment fields. You also need to go back to MySQL and set it manually. You can use Navicat MySQL graphical tool or execute SQL:

Alter table 'users'
Modify column 'id' int (11) not null AUTO_INCREMENT FIRST,
Add primary key ('id ');

During data export, some tables may encounter errors when copying data. The errors and solutions are as follows:

1. A table field cannot be null

Before exporting data, set this field in SQL Server to "allowed to be NULL"

2. Data too long for column 'name' in a table'

This happens when the mysql-connector-odbc-5.1.2-win32 is installed and a record of SQL Server has Chinese garbled characters. Solve the problem after you switch to the mysql-connector-odbc-5.3.2-win32 and select the MySQL ODBC 5.3 Unicode Driver.

3. a table id doesn' t have a default value

Querying Google, there is a way to disable strict Mode for MySQL, see: http://blog.csdn.net/god8816/article/details/8593419

In fact, you only need to manually modify the id field of the table in MySQL as the primary key + Auto Increment, and then enter the Enterprise Manager to re-export the table.