MS SQL - MySQL - Openquery Chinese characters imported as '?'

Question

I am importing data from a MySQL Database into a Microsoft SQL Server Database (SQL_Latin1_General_CP1_CI_AS). I use a linkedserver connection based on OBDC in combination with an Openquery statement.

I use the following MS SQL Statement:

 SELECT street_address FROM openquery(MYLINKEDSERVER,'SELECT street_address FROM customers')

Some of the addresses contain Chinese characters, when using openquery these are imported as question marks, example result:

??? ??? 74 ?????501?

I tried converting using latin1 - no luck;

 SELECT * FROM openquery(MYLINKEDSERVER,'SELECT convert(street_address using latin1) FROM customers')

Any ideas how to import the Chinese characters into MS SQL?

Answer 1

There are two ODBC drivers, one ANSI (myodbc5a.dll) and one Unicode (myodbc5w.dll). The ANSI driver will always return varchar data. You need to use the Unicode driver in order to return Chinese characters as Unicode. 

Check the relevant paragraph on the ODBC driver's installation page and this related question.

When converting from one codepage to another, unknown or unrepresentable characters are replaced with ? or the Unicode Replacement character �. Such characters in a string are a sure sign that a wrong conversion was attempted. 

Answer 2

So i managed to make it work.

1. convert to binary in MySQL
2. Import in SQL Server through openquery
3. cast to varchar(max) in SQL Server
4. convert varchar UTF8 to Nvarchar using function --> link

result query;

SELECT dbo.func_utf_string(cast(bin AS varchar(MAX))) from openquery(MYLINKEDSERVER,'SELECT cast(street as binary) as bin from customers')