topic Re: Characters turned into Chinese using sql insert from Viya in SAS Viya

Characters turned into Chinese using sql insert from Viya

Ullsokk — Wed, 05 May 2021 12:10:01 GMT

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.

Re: Characters turned into Chinese using sql insert from Viya

japelin — Thu, 06 May 2021 00:37:26 GMT

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

Re: Characters turned into Chinese using sql insert from Viya

Ullsokk — Thu, 06 May 2021 07:02:57 GMT

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

Re: Characters turned into Chinese using sql insert from Viya

japelin — Thu, 06 May 2021 07:37:16 GMT

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?

Re: Characters turned into Chinese using sql insert from Viya

Ullsokk — Thu, 06 May 2021 07:45:07 GMT

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

Re: Characters turned into Chinese using sql insert from Viya

japelin — Thu, 06 May 2021 07:56:36 GMT

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

client_encoding='utf-8'

Re: Characters turned into Chinese using sql insert from Viya

Ullsokk — Thu, 06 May 2021 08:02:38 GMT

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

ERROR 22-7: Invalid option name CLIENT_ENCODING.

Re: Characters turned into Chinese using sql insert from Viya

Ullsokk — Thu, 06 May 2021 08:07:02 GMT

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?