When I using ODBC, characters are broken.

Question

I have configured 64 bit ODBC.
Then I set DBLINK in MSSQL.
Then when I called the data using openquery SQL the characters were corrupted.

Is there any way to fix this?

For example,

SELECT * FROM openquery(dblink, 'select description from test_table');

Result: Special characters are broken.

ref) Select @@langauge : Korean

      Select * from sys.databases : SQL_Latin1_General_CP1_CI_AS

My server's version : Windows 2016 64bit (server's langauge : Korean)

SQL version : mssql 2014

Answer 1

Is a Korean font installed to display the data?

What kind of ODBC driver? Some comes in ANSI or UTF flavors like MySQL. In this case you may need a different driver.

Answer 2

Thank you for your reply.

ODBC is AspenTech SQLPlus. (Connection Test OK.)

I already installed Korean font.

When I checked the database, the "collation_name" are all SQL_Latin1_General_CP1_CI_AS for master, tempdb, model, and msdb.
However, my database is Korean_Wansung_CI_AS.
Can this be a problem?

this "select name, collation_name from sys.database" query result 

master : SQL_Latin1_General_CP1_CI_AS
tempdb : SQL_Latin1_General_CP1_CI_AS
model : SQL_Latin1_General_CP1_CI_AS
msdb : SQL_Latin1_General_CP1_CI_AS
MyTestDB : Korean_Wansung_CI_AS

Answer 3

Hi MJ,

As I searched,AspenTech.SQLplus.DataProvider doesn't support Unicode chars, you could check with AspenTech support.

It is better for you to still use SQL_Latin1_General_CP1_CI_AS for MyTestDB or change the ODBC.

Best regards,

Melissa

-------------------------------------------

MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 4

I create a test database using SQL_Latin1_General_CP1_CI_AS, but I still get an error in the sql statement in openquery.

I asked a colleague how to install MSSQL.
When installing MSSQL on a server set to Korean, the default Windows language was not used.
And he changed the language set to SQL_Latin1_General_CP1_CI_AS when installing MSSQL.
Could this issue corrupt the result value of openquery sql?
(The default language of the server: Korean)

characters are still broken.

for example

ODBC name : test_ip21

Linked Server name : dblink (Microsoft OLE DB Provider for SQL Server)

SQL statement : SELECT * FROM openquery(dblink, 'select description from test_table');

Answer 5

As I searched,AspenTech.SQLplus.DataProvider doesn't support Unicode chars, you could check with AspenTech support.

This means that you need to set the correct code page in the driver and for testing you should use Excel or Access. Cause SSMS has its own flaws around some charset  combinations.

And depending on whether you have already written some data over that linked server, you may have indeed corrupted your data.

Answer 6

characters are still broken.

for example

ODBC name : test_ip21

Linked Server name : dblink (Microsoft OLE DB Provider for SQL Server)

SQL statement : SELECT * FROM openquery(dblink, 'select description from test_table');

In your previous post your linked server was something completely different.

To be able to help you, we need to get accurate information. If the data source is not delivering the Korean characters properly, it does not matter what you do locally.

So what is there on the other end? AspenTech (which I'm completely unfamiliar), or another SQL Server instance? It the latter case, what is the table definition? Please give us the complete CREATE TABLE statement.

---

Erland Sommarskog, SQL Server MVP, [e-mail]