ee_lcpaa
asked on
Truncation warninsg when migrating data from MySQL to MSSQL
Hi All,
I got the following warnings when migration a table from MySQL of GA version 5.5.2 to MSSQL 2016 standard edition.
- Validating (Warning)
Messages
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "domain_username" with a length of 12 to database column "domain_username" with a length of 8.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "english_name" with a length of 75 to database column "english_name" with a length of 50.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "chinese_name" with a length of 75 to database column "chinese_name" with a length of 50.
(SQL Server Import and Export Wizard)
Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
(SQL Server Import and Export Wizard)
Below shows the script used to create a table "USER" in MySQL
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`domain_username` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`english_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`chinese_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
In fact, the column width is 8 characters for a column called "domain_username". But I don't know why the "SQL server import and export wizard" thinks that the length is 12 (rather than 8).
I had setup a ODBC system DSN entry as shown in the attached file "File_1.png".
When running the "SQL server import and export wizard", I selected the input source ".Net Framework Data Provide for Odbc" as shown in the attached file "File_2.png". Then I selected "SQL Server Native Client 11.0" as destination (shown in file "File_3.png")
The column mappings details are shown in the attached file "File_4.png". The warnings are shown in the "File_5.png".
Although the data were migrated successfully from MySQL to MSSQL, I would like to check how to remove those warnings.
Please advise. Thanks.
File_1.png
File_2.png
File_3.png
File_4.png
File_5.png
I got the following warnings when migration a table from MySQL of GA version 5.5.2 to MSSQL 2016 standard edition.
- Validating (Warning)
Messages
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "domain_username" with a length of 12 to database column "domain_username" with a length of 8.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "english_name" with a length of 75 to database column "english_name" with a length of 50.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "chinese_name" with a length of 75 to database column "chinese_name" with a length of 50.
(SQL Server Import and Export Wizard)
Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
(SQL Server Import and Export Wizard)
Below shows the script used to create a table "USER" in MySQL
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`domain_username` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`english_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`chinese_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
In fact, the column width is 8 characters for a column called "domain_username". But I don't know why the "SQL server import and export wizard" thinks that the length is 12 (rather than 8).
I had setup a ODBC system DSN entry as shown in the attached file "File_1.png".
When running the "SQL server import and export wizard", I selected the input source ".Net Framework Data Provide for Odbc" as shown in the attached file "File_2.png". Then I selected "SQL Server Native Client 11.0" as destination (shown in file "File_3.png")
The column mappings details are shown in the attached file "File_4.png". The warnings are shown in the "File_5.png".
Although the data were migrated successfully from MySQL to MSSQL, I would like to check how to remove those warnings.
Please advise. Thanks.
File_1.png
File_2.png
File_3.png
File_4.png
File_5.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If your MySQL column contains some multibyte characters then you should declare MS SQL column as nvarchar. This is valid namely for Chinese_name column.
The last warning points to insufficient rights and you should solve it separately.
The last warning points to insufficient rights and you should solve it separately.
Being a warning you just need to perform a select after the migration to see if any value had been really truncated.