topic Re: Writing to SQL Server encoding problem in SAS Enterprise Guide

Writing to SQL Server encoding problem

mshparber_fnx — Sun, 20 Sep 2020 06:10:02 GMT

I need your help please.

When I write a query results to a SQL Server via library - I get question marks instead of hebrew letters.

I use sqloledb provider, maybe I need to modify my libname statement.

I tried encoding="hebrew" and encoding="utf-8" - no luck.

When I select from other tables that contain hebrew - I see characters OK, it is just when WRITING the data.

Please help

Thank you

Michael

Re: Writing to SQL Server encoding problem

Shmuel — Sun, 20 Sep 2020 08:19:58 GMT

Clarification needed:

1) What OS is the SQL server?

2) What is the SAS environment? (MF / Windows / Unix or linux) and

Is it a local sas or running on a server (SAS Studio as: SAS EG / SAS UE) ?

3) By what Code have you written to the SQL server ?
Please post it using the "running man" icon.

4) Check the sas configuration file used when running the code -

what is the locale and encoding options ?

Re: Writing to SQL Server encoding problem

mshparber_fnx — Sun, 20 Sep 2020 10:04:35 GMT

1) SQL Server is on Windows Server OS (not sure which)

2) I am using a local EG client connected to a SAS Server (probably Unix, not sure)

3) I am using libraries. For SQL Server I use this code:

libname sqllib oledb
datasource = SQLSERVERNAME provider=sqloledb schema='dbo' properties=('initial catalog'=MYDBNAME)
user=user password=password
READBUFF=30000 INSERTBUFF=30000
encoding="utf-8";

4) When I look at log - I do see ENCODING=UTF8

I think it has smth. to do with nvarchart data types.

In SQL Server itself (without SAS) if I insert a value with hebrew characters with regular quotations - then I get question marks.

But if i put N'' before quotations - then it is OK.

So, how do I make SAS write to SQL Server with N''?

Thank you!

Re: Writing to SQL Server encoding problem

mshparber_fnx — Tue, 22 Sep 2020 10:14:44 GMT

Anyone? @Shmuel ?

Please help.

Thank you

Re: Writing to SQL Server encoding problem

Shmuel — Tue, 22 Sep 2020 10:33:42 GMT

@mshparber_fnx , I have asked the questions because I think the answer is the hint for how to fix the issue.

I'm sorry but I have never worked with SQL Server.

Is there anyone that you know work with this database, that can help you.

Is there any tool that you can look at a visible Hebrew text in the SQL server

and check what Hebrew - hex value - it is used.

If you post the some couples like 'א' = "##'x, 'ת'="##"x value, I may suggest

a code to translate your local Hebrew to the target Hebrew.

Re: Writing to SQL Server encoding problem

SASKiwi — Wed, 23 Sep 2020 05:00:03 GMT

@mshparber_fnx - What is the encoding of your SAS session? Do Hebrew letters display OK in SAS when viewing a SAS dataset - one you are trying to load into SQL Server?

Re: Writing to SQL Server encoding problem

mshparber_fnx — Wed, 23 Sep 2020 10:11:51 GMT

Thanks to @Hagay - the solution is to specify in libname the following code:

It just not working for me, but maybe it's SAS version issue.

CHAR_AS_NCHAR=YES