topic Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding in SAS Programming

SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

milts — Thu, 13 Jul 2017 14:05:58 GMT

I have a SQL Server table with chinese characters in it. I am able to view it properly through Base SAS (Unicode Suppport), but when I try to open it in Enterprise Guide, I hit the error "Unable to transcode data to/from UCS-2 encoding" upon clicking the dataset.


libname mydb odbc noprompt="driver=sql server;server=mysrv/db;uid=sa;pwd=pwd;database=mydb;" schema=dbo;

Greatly appreciate any inputs on this.

Thanks!

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

Ksharp — Fri, 14 Jul 2017 10:32:53 GMT

Change your SAS encoding into UTF-8 .

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

milts — Fri, 14 Jul 2017 14:59:36 GMT

The default is already in UTF-8.

I managed to resolve it by adding read permissions to the library and table itself in SMC and pre-assigning the library with metadata engine.

I find it strange though that sometimes the error appears when you first open the table but once it's in the process flow it can actually be viewed properly.

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

Ksharp — Sat, 15 Jul 2017 11:48:37 GMT

Make sure SAS has the same encoding with SQL .

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

milts — Mon, 17 Jul 2017 01:42:20 GMT

Is it the collation in SQL Server? The database is Latin1_General_CI_AI and I can view chinese characters when I query in SQL Management Studio.

I'm actually able to view them now in SAS EG also, but when I filter by dates I get thrown again to a similar error.

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

Ksharp — Mon, 17 Jul 2017 12:39:07 GMT

It is most likely unprinted character problem. E.X. you have half of a chinese word or a illegal character for EG.

If that was it, you need clean your data before using EG.

Also see if you could print it in HTML destination.

ods html;

proc print data=have(obs=100);run;