How to write utf-8 data to SQL server?

[Mayt]

Hi, guys!

I've connect SAS to SQL server and tried  to write data to the server. But the Thai characters don't appear as I expected. They appear as "?".

More information:

The sqlserver running on windows.

SAS running on unix.

And the unicode of SAS session is UTF8.

How I connected SAS to SQL server

libname sslib sqlsvr noprompt="DRIVER=SAS ACCESS to MS SQL Server;
UID=my UID ;PWD=my Password;
HOST=my Host ;PORT=my Port ; db= my Database" schema="dbo";

the data

If I create new table in SQL server from the data

proc sql;
	create table sslib.test_temp_output(encoding='utf8') as
	select *
	from work.temp_tbl(encoding='utf8');
run;

How a new table looks like:

I've tried proc append but the results stay the same.

data _null_;
  set temp_tbl(encoding='any');
  put string= $quote. +1 string $hex8.;
run;

proc append base=sslib.test_table data=temp_tbl(encoding='utf8') ;
run;

or

data _null_;
  set temp_tbl(encoding='any');
  put string= $quote. +1 string $hex8.;
run;

proc append base=sslib.test_table data=temp_tbl(encoding='any') ;
run;

[AhmedAl_Attar · Accepted solution]

Hi @Mayt 

Have a look at this paper Multilingual Computing_with SAS 9.4

It looks like you would need to declare your SqlServer Tables with nchar/nvchar columns in order to display utf-8 data values.

Hope this helps,

Ahmed

[Mayt]

Can we do it in sas studio?

[LinusH]

Technically speaking yes, by using explicit SQL pass through.
Here's a Microsoft documentation link:
https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-serv...

[Mayt]

Thank you so much