topic Re: SAS 9.4 and MS SQL 2016, varchar and nvarchar in one table in Administration and Deployment

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

DmitryKaptur — Thu, 25 Feb 2021 06:05:48 GMT

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.

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

JimmyJoeBob — Sat, 22 Apr 2023 15:08:10 GMT

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

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

Mayt — Tue, 24 Oct 2023 09:00:13 GMT

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?

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

JimmyJoeBob — Tue, 24 Oct 2023 15:01:04 GMT

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.

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

JimmyJoeBob — Wed, 25 Oct 2023 14:49:50 GMT

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

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

Mayt — Tue, 31 Oct 2023 09:20:27 GMT

Thank you so much ^^