Writing to SQL Server encoding problem

[mshparber_fnx]

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

[Shmuel]

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 ?

[mshparber_fnx]

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!

[mshparber_fnx]

Anyone? @Shmuel ?

Please help.

Thank you

[Shmuel]

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

[SASKiwi]

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

[mshparber_fnx]

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