topic Connect to SQL Server: Special Characters not correct in CAS but correct in Compute in SAS Viya

Connect to SQL Server: Special Characters not correct in CAS but correct in Compute

a_SAS_sin — Tue, 04 Mar 2025 15:06:49 GMT

I am on Viya 2024.09 on AKS.

I am connecting to a SQL Server with Collation="SQL_Latin1_General_CP1_CI_AS".

I am using the following code to connect to extract the database using Compute:

/*COMPUTE - Libname statement*/
libname sqlComp odbc
  complete="driver=SAS ACCESS to MS SQL Server;
    port=1433;
    server=&_SQLSR_SRV.;
    database=&_SQL_DB.;
    stringdates=no;
    read_lock_type=nolock;" 
  schema=&_SQL_SCHEMA. 
  authdomain="&_authDomain."
;

proc sql;
  select TableVersionLabel as Compute_Label
  from sqlComp.TableVersion 
  where TableVID =2328;
quit;

which returns the following:

To connect CAS to SQL Server and extract data I sue the following:

/* CAS - libname  statement*/
caslib odbccasl 
     datasource=( 
      srctype = "odbc" 
      authenticationDomain = "&_authDomain."
      catalog = "*"
      schema = "&_SQL_SCHEMA."
      conopts="driver=SAS ACCESS to MS SQL Server;
          port=1433;
          server=&_SQLSR_SRV.;
          database=&_SQL_DB.;
          stringdates=no;
          read_lock_type=nolock;" 
    ) ;

/*Loads the data in casuser using fedsql*/
proc fedsql sessref=mySession _method ;
   select a.TableVersionLabel as CAS_Label
   from odbccasl."TableVersion" a  
    Where TableVID =2328;;
quit ;

Which returns the following:

As you can see the "long-dash" character is not shown correctly.
How can I make sure the values in CAS are shown correctly.

FYI, the odbc driver is installed in a mounted share that is used by both the compute and CAS.

Thank you,

Re: Connect to SQL Server: Special Characters not correct in CAS but correct in Compute

splslb — Thu, 06 Mar 2025 10:51:57 GMT

I work in SAS Technical Support (TS). As discussed in the TS case:

Firstly I would suggest using the dedicated module SAS/Access to MS SQL Server. In order to use this module you'd need to change your SAS code to use:

srctype=’sqlserver’ in caslib and also sqlsvr engine in libname (instead of odbc)

and then:

- set IANAAppCodePage=106 to reflect utf-8 in Viya,

- column type names in SQL Server are expected to begin with n like nchar, nvarchar.

Re: Connect to SQL Server: Special Characters not correct in CAS but correct in Compute

a_SAS_sin — Thu, 06 Mar 2025 11:32:59 GMT

Adding the option `IANAAppCodePage=106` fixed the issue.
Thank you @splslb