SQL Server Migration Assistant (SSMA): ERROR [22018] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.51-community]

Question

I am using SSMA for MySQL to migrate from MySQL to MSSQL and on several tables I am getting the 22018 error. After lots of mucking about I found that the error was being generated due to Russian characters being stored in these tables.

For example: Бухгалтерский учет

There are other non-English characters in the tables, such as Profissão Jurídica and they are coming through OK.

The error is to do with collation and the collation on the MySQL table is latin1_swedish_ci and the column that holds the "foreign" characters has utf8_unicode_ci collation on it and is a varchar(255).

The recipient table in MSSQL uses the database default collation (Latin1_General_CI_AS) and the recipient column is a nvarchar(255).

In SSMA the charset mapping for latin1 is set to the default of CHAR/VARCHAR and I have tried having this set to NCHAR/NVARCHAR with no success. The type mapping for varchar is set to nvarchar for all instances.

The only thing close to an answer that I have found is this posting. Unless I'm missing the point of the answer, I can't see that there is a missing conversion - or is there?

Answer 1

After having spent literally hours trying to find out why it didn't work I found the solution when I started to give up and use a linked table in Access to the then a pass-through query into MSSQL.

I was using the wrong ODBC driver. It turns out there are 2 MySQL ODBC drivers installed an ANSI and a Unicode driver. I was using ANSI. When I swapped it to Unicode all was well!

Answer 2

As per the Microsoft SSMA help guide SSMA is compatible with only MySQL ODBC 5.1 Driver (trusted).

I downgraded MYSQL ODBC from 5.3 to 5.1 and it worked.

MYSQL ODBC 5.1~ link https://dev.mysql.com/downloads/file/?id=415029