topic How to write utf-8 data to SQL server? in SAS Programming

How to write utf-8 data to SQL server?

Mayt — Tue, 17 Oct 2023 10:34:43 GMT

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;

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;

Re: How to write utf-8 data to SQL server?

AhmedAl_Attar — Tue, 17 Oct 2023 15:29:27 GMT

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

Re: How to write utf-8 data to SQL server?

Mayt — Tue, 24 Oct 2023 08:18:58 GMT

Can we do it in sas studio?

Re: How to write utf-8 data to SQL server?

LinusH — Tue, 24 Oct 2023 10:29:52 GMT

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-server-ver16

Re: How to write utf-8 data to SQL server?

Mayt — Tue, 31 Oct 2023 09:21:47 GMT

Thank you so much