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

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.

我使用SSMA将MySQL从MySQL迁移到MSSQL，在几个表上，我得到了22018的错误。经过大量的折腾之后，我发现由于俄罗斯字符被存储在这些表中，所以产生了错误。

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

例如:Бухгалтерскийучет

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

表中还有其他非英语字符，如Profissao jua，它们正通过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).

该错误与collation有关，而MySQL表上的collation是latin1_swedish_ci，而持有“foreign”字符的列有utf8_unicode_ci排序，并且是varchar(255)。

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

MSSQL中的收件人表使用数据库默认排序(Latin1_General_CI_AS)，接收列是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.

在SSMA中，latin1的charset映射设置为CHAR/VARCHAR的默认值，我尝试过将这个设置为NCHAR/NVARCHAR，但没有成功。varchar的类型映射被设置为所有实例的nvarchar。

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?

唯一接近我找到答案的是这个帖子。除非我错过了答案的要点，否则我看不到有一个缺失的转换——或者在那里?

2 个解决方案

#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.

在花了几个小时的时间试图找出为什么它不起作用的原因之后，我找到了解决方案，当我开始放弃并使用一个连接的表来访问，然后通过一个直通查询到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!

我使用了错误的ODBC驱动程序。原来有两个MySQL ODBC驱动程序安装了ANSI和Unicode驱动程序。我用ANSI。当我把它换成Unicode时，一切都很好!

#2

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

根据微软SSMA的帮助，SSMA只兼容MySQL ODBC 5.1驱动程序(可信)。

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

我将MYSQL ODBC从5.3降级为5.1，它起作用了。

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

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