0

I am using an Mssql instance and trying to make an INSERT to a LINKED SERVER with openquery but having troubles with chinese characters.

My linked server connects to a MySQL instance utf8_general_ci and to build it I use a MySQL odbc 5.3 unicode driver and in its details I already specify utf8 in charset.

I will copy main parts of my code:

insert openquery (MySQL_OBDC_UNICODE,'select id,chinesedescription
from chinesecodes')

(select id,  descchinese from openquery (SQLSERVER_ODBC,'SELECT id,descchinese FROM tbarticles where idlanguage=''CN'''))

IF I execute only second select instruction in SQL SERVER Management Studio chinese descriptions are shown ok, but when I try to make the insert to MySQL linked server it shows me the following:

Provider OLE DB "MSDASQL" of linked SERVER "MYSQL_OBDC_UNICODE" could not INSERT INTO table "[MSDASQL]" due to the column "chinesedescription". Value not fulfill restrictions of column integrity.

Let me know if you need some specific detail for provide with me help.

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
DBAA
  • 1
  • 1

1 Answers1

0

After doing some research I came up with the solution to my linked servers collation issue:

1) I forced the collation in my source linked server to COLLATE Chinese_PRC_CI_AS.

(select id, descchinese from openquery (SQLSERVER_ODBC,'SELECT id,descchinese FROM tbarticles Chinese_PRC_CI_AS where idlanguage=''CN'''))

2) In MySQL UNICODE ODBC DRIVER I leave the charset property to blank because before I had UTF8 and I was encoding twice.

Hope it helps to others!!

DBAA
  • 1
  • 1