SAS 9.4 and MS SQL 2016, varchar and nvarchar in one table

[DmitryKaptur]

Hi, guys.

I need some help.

On MS SQL server table have to different type column nvarchar and varchar, workspace server work on UTF-8. When load the table in SAS I can read nvarchar column and can't read varchar(����).

MS SQL server has collation Cyrillic_General_CI_AS.

What I have:

AIX  OS 

odbc.ini
[***]
Driver=Easysoft SQL Server
Server=***
Port=1433
Database=***
QuotedId=Yes
AnsiNPW=Yes
ConvWToUtf=1
ConvToUtf=1
NTLMv2=1

/sas/SASConfig/Lev1/SASAppUTF/WorkspaceServer/sasv9_usermods.cfg
-ENCODING 'utf-8'
 
/sas/SASConfig/Lev1/SASAppUTF/WorkspaceServer/WorkspaceServer_usermods.sh
USERMODS_OPTIONS=
export EASYSOFT_UNICODE=YES
export SAS_COMMAND=$SAS_ROOT/$SASROOT/bin/sas_u8
LANG=ru_RU.UTF-8

I try to add odbc.ini configuration 

Server_CSet=WINDOWS-1251

or
Client_CSet=WINDOWS-1251
But it's doesn't work.

[JimmyJoeBob]

I had a similar problem, although we're running English collation.

Adding these two lines to my SiteMods\sasv9_usermods.cfg fixed it for us

For the -encoding value, experiment with the Cyrillic forms in https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/nlsref/n1r7pnb91iybs9n1hgvsj7q09srd.htm

/* required to support DBCS data */
-dbcstype=pcms
-encoding=latin1

[Mayt]

Sorry, I need to declare my SqlServer Tables with nchar/nvchar columns in order to display utf-8 data values. Could you let me know how you declare it?  

[JimmyJoeBob]

If you need to build SQL tables using SAS code, but you need more granular data type control, it's better to use SQL passthrough. Here's an example:

proc sql;
    CONNECT TO ODBC AS SqlSvr (dsn=sql.f.q.d.n\instance);
    EXECUTE (
        CREATE TABLE DB.SCM.TblName (
            Col1    char(4)
            , Col2    varchar(12)
            , Col3    nvarchar(24)
            )
        ;
        ) AT SqlSvr
    ;
    DISCONNECT FROM SqlSvr;
quit;

You'll need to change the connection information in the CONNECT TO statement and the table name and definition to suit your needs, but this will be the basic framework you need to apply.

[JimmyJoeBob]

Sorry - typo in the code:
'AT SqlSvr'
..should be
'BY SqlSvr'

[Mayt]

Thank you so much ^^