Characters turned into Chinese using sql insert from Viya

[Ullsokk]

I am trying to update a table in a sql database from viya, using proc sql insert. However, the characters have turned into Chinese on the way from VIYA to the sql database.

Is this a known issue? I would guess this has something to do with encoding.

The same code works from SAS 9.4.

[japelin]

Hi,

First, please clarify the environment.

1. The encoding of SAS and the encoding of the original database in the environment where you checked it works.

2. what is the execution environment when garbled on Viya, SAS or other?

3. How were the databases created? Is it just a SAS dataset, a sql database, did you create it, is it an existing database, etc.?

[Ullsokk]

1.The encoding in sas VIYA is UTF-8, as far as I am aware. The encoding in the MSSQL database is wlatin1, the same is true in our 9.4 environmen, where the code works.

2.The execution was from sas studio V 

3.The database is an existing MSSQL database 

[japelin]

Let me check something additional.

How are you connecting to the mssql database?
Is it sqlsvr with the access engine, or ODBC? Or is it some other way?
Also, what is the code for the part that is connecting to the database?

[Ullsokk]

The conection is through ODBC

libname mssql odbc dsn="dsn" schema=schema user="user" password="pw"
cursor_type=forward_only;

from the odbc.ini:

[dsn]

Driver = ODBC Driver 17 for SQL Server

Server = servername

Database=database

AutoTranslate=yes

IANAAppCodePage=106

DriverUnicodeType=1

[japelin]

Try adding the following string to the end of the libname statement.

client_encoding='utf-8'

[Ullsokk]

Tried it, in several locations in the libname statement. got the following error:

ERROR 22-7: Invalid option name CLIENT_ENCODING.

[Ullsokk]

tried the option for a postgres example: 

libname mydb postgres server=myserver port=5432 
    user=myuser password='mypwd' database=mydb1 client_encoding='utf-8';

that gave no error regarding client_encoding. Is client_encoding maybe not supported in odbc?