Connection to MSSQL returns tablename with spaces between each letter

[DanielKaiser]

Hi,

I connect to a MSSQL Server 2016 using ODBC Driver 13.1 for SQL Server on RHEL7.
Connection works but return the tablename with spaces between any letter.

In the odbc.ini are definied:
Driver
Server
Database
AutoTranslate=Yes
AnsiNPW=Yes
DriverUnicodeType=1

Register Table returns like this - using the Windows 10 odbcad32 returns the correct table name with the same driver. So seems to be a problem that is Linux or SAS-Server relatet. 

[Ksharp]

Use the following code to check table name ?

options validmemname=extend ;
libname x odbc ........... ; /* preserve_col_name=yes; */
proc sql;
select memname
from dictionary.tables
where libname='X';
quit;

[DanielKaiser]

Results in this pretty long error message and 

And Log says "No rows were selected". The library also does not show any tables

[Ksharp]

Can you show your code and full log ?

[DanielKaiser]

Sure

Spoiler

1                                                          Das SAS System                          Freitag, 29. Januar 2021 10:15:00

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Programm';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HtmlBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         options validmemname=extend;
29         LIBNAME ebox2 ODBC DS=H25_EBOX USER=IT25_TATMHIST_XXXX PASSWORD=XXXXXXXXXXXXXXXXXXXXXXX;
NOTE: Libref EBOX2 was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: H25_EBOX
30         proc sql;
31         select memname
32         from dictionary.tables where libname='ebox2';
NOTE: No rows were selected.
33         quit;
NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

34         
35         
36         %LET _CLIENTTASKLABEL=;
37         %LET _CLIENTPROCESSFLOWNAME=;
38         %LET _CLIENTPROJECTPATH=;
39         %LET _CLIENTPROJECTPATHHOST=;
40         %LET _CLIENTPROJECTNAME=;
41         %LET _SASPROGRAMFILE=;
42         %LET _SASPROGRAMFILEHOST=;
43         
44         ;*';*";*/;quit;run;
45         ODS _ALL_ CLOSE;
46         
47         
48         QUIT; RUN;
2                                                          Das SAS System                          Freitag, 29. Januar 2021 10:15:00

49         

options validmemname=extend;
LIBNAME ebox2 ODBC DS=H25_EBOX USER=IT25_TATMHIST_XXXXX  PASSWORD="XXXXXXXXXX";
proc sql;
select memname
from dictionary.tables where libname='ebox2';
quit;

This is the configuration in odbc.ini

[H25_EBOX]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
DriverUnicodeType=1
Server=vdb01234,1234
Database=IT25_TATMHIST
AutoTranslate=no
AnsiNPW=yes

[Kurt_Bremser]

Library names in SAS must be specified in uppercase. That's why you don't get any results.

[DanielKaiser]

Didn't know that - but just tried it, does not change anything. The error keeps to be the transcode problem between utf-16le and utf-8

[Ksharp]

Or could try create a table ?

proc sql;
create table want as
select memname
from dictionary.tables where libname='EBOX2';
quit;

[DanielKaiser]

It's a readonly database, so I guess, this will not work, right?

[Ksharp]

No. the table WANT created at your side , in WORK library .
You can check it by double click .

[DanielKaiser]

Hm. Created with 0 Rows 😞

[Ksharp]

Double click library "EBOX2" and check if there are tables in it .

[DanielKaiser]

No, no tables

[Ksharp]

Then you need specify a SCHEMA in libname statement.

libname x odbc ........... schema=xxxx ;

Or using the following to get schema and table name.

proc sql;
connect to odbc(dsn=xx user=xx passwrod=xx) ;
select *
from connection to odbc(ODBC:SQLTables);
quit;

[DanielKaiser]

Specifying the schema helped - sorry I dont do that much SAS programming.

So now, I have the dataset - you an see the MouseOver shows only "T" if I doubleclick it to edit the field I can only see "T" too.

And this is  how the Library looks like now. This is the same behaviour I have when I use register table in SMC - I get an error that "T" could not be registered.

Any ideas how we can force SAS to use the right encoding or remove the blanks before going on?