Connection to MSSQL returns tablename with spaces between each letter

[Ksharp]

Your table name looks right , maybe original table name is 'T F EBOX'n.
Try to Add an option ?


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

[DanielKaiser]

I have validated the table name in the SQL Studio. It is "T_F_EBOX" without spaces.
Using the Microsoft ODBC Driver returns the tablenames vertical.

I have written them into "want" again - showing only the first letter

 

Using the DataDirect Driver returns

ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver]2713SQL Server ODBC driver2726SQL Server ODBC driver. : 
       [DataDirect][ODBC 20101 driver]2728

Cant find any information to this error "2728" so right now DataDirect does not work.

[Ksharp]

"Using the Microsoft ODBC Driver returns the tablenames vertical."

That is real wield . shouldn't be vertical. if there are carriage character('0D0A'n) in table name ?

check option 'preserve_table_name' ,I forgot its real name.

libname odbc dsn=     preserve_table_name=yes ;

[DanielKaiser]

Preserve_tab_name did not help - it is yes per default, if i set it to no, the table does not appear anymore.

I exported the SAS Dataset with the Tablename into a hex-reader.
Looks interesting, SAS really seems to fill in Hex-Code "20" what is in UTF-16le " "

00000000h: FF FE 6D 00 65 00 6D 00 6E 00 61 00 6D 00 65 00 ; ÿþm.e.m.n.a.m.e.
00000010h: 0D 00 0A 00 54 00 20 00 5F 00 20 00 46 00 20 00 ; ....T. ._. .F. .
00000020h: 5F 00 20 00 45 00 20 00 42 00 20 00 4F 00 20 00 ; _. .E. .B. .O. .
00000030h: 58 00 20 00 0D 00 0A 00 0D 00 0A 00 ; X. .........

[Ksharp]

00000000h: FF FE 6D 00 65 00 6D 00 6E 00 61 00 6D 00 65 00 ; ÿþm.e.m.n.a.m.e.
00000010h: 0D 00 0A 00 54 00 20 00 5F 00 20 00 46 00 20 00 ; ....T. ._. .F. .
00000020h: 5F 00 20 00 45 00 20 00 42 00 20 00 4F 00 20 00 ; _. .E. .B. .O. .
00000030h: 58 00 20 00 0D 00 0A 00 0D 00 0A 00 ; X. .........

Interesting , there are NULL (00) and SPACE (20) in table name, How could that happen?

[DanielKaiser]

Hehe yeah... I thought the same.
There are no spaces in the MSSQL Admin Tool. No spaces when accessing via Windows 10 using ODBC. Only from Linux.

[Ksharp]

It seems the problem was from Linux's ODBC driver .

Could you let DB Admin to create a view to refer to that table .
and import the view into SAS ?

P.S. view name don't contains space or other special char .

[Kurt_Bremser]

Look if this post is helpful:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Encoding-Issue-UTF8-to-UTF-16-le/m-p/383475/high... 

[DanielKaiser]

It is utf-8 encoded. 
Problem occurs against local server and remote

Spoiler

Group=LANGUAGECONTROL
DATESTYLE=DMY     Specifies the sequence of month, day, and year when ANYDTDTE, ANYDTDTM, or ANYDTTME informat data is ambiguous.
DFLANG=GERMAN     Specifies the language for international date informats and formats.
DSCAS             Runs the DATA step on the CAS server.
EXTENDOBSCOUNTER=YES
                   Specifies whether to extend the maximum number of observations in a new SAS data file.
LOCALEDATA=SASLOCALE
                   Specifies the location of the locale database.
LOGLANGCHG        Enables changing the language of the SAS log when the LOCALE= option is changed.
NOLOGLANGENG      Write SAS log messages based on the values of the LOGLANGCHG, LSWLANG=, and LOCALE= options when SAS started.
LSWLANG=LOCALE    Specifies the language for SAS log and ODS messages when the LOCALE= option is set after SAS starts.
MAPEBCDICTOASCII= Specifies the transcoding table that is used to convert characters from ASCII to EBCDIC and EBCDIC to ASCII.
NONLDECSEPARATOR  Disables formatting of numeric output using the decimal separator for the locale.
ODSLANGCHG        Enables the language of the SAS message text in ODS output to change when the LOCALE option is set after start 
                   up.
PAPERSIZE=A4      Specifies the paper size to use for printing.
RSASIOTRANSERROR  Displays a transcoding error when illegal values are read from a remote application.
TIMEZONE="GMT+01:00"
                   Specifies a time zone.
TRANTAB=          Specifies the translation table catalog entries.
URLENCODING=SESSION
                   Specifies whether the argument to the URLENCODE function and to the URLDECODE function is interpreted using the 
                   SAS session encoding or UTF-8 encoding.
DBCS              Enables double-byte character sets for encoding values that support East Asian languages.
2                                                          Das SAS System                          Freitag, 29. Januar 2021 10:15:00

DBCSLANG=UNKNOWN  Specifies a double-byte character set language.
DBCSTYPE=UTF8     Specifies the encoding method that is used for a double-byte character set.
FSDBTYPE=DEFAULT  Specifies a full-screen double-byte character set (DBCS) encoding method.
FSIMM=            Specifies input method modules (IMMs) for full-screen double-byte character sets (DBCS).
FSIMMOPT=         Specifies options for input method modules (IMMs) that are used with a full-screen double-byte character set 
                   (DBCS).
ENCODING=UTF-8    Specifies the default character-set encoding for the SAS session.
LOCALE=DE_DE      Specifies a set of attributes in a SAS session that reflect the language, local conventions, and culture for a 
                   geographical region.
NONLSCOMPATMODE   Encodes data using the SAS session encoding.

[Tom]

Sounds like your ODBC driver or your SQL Server instance is configured to use double byte encoding. So UTF16.  And nothing is transcoding the names so the two bytes needed to store a 16bit code are appearing to your SAS code like two separate characters.  You might be able to fix this with ODBC settings?

[DanielKaiser]

Any Idea which setting might help? There is no real documentation for the settings of the Microsoft driver.

[SASKiwi]

So all table names have this problem? What is the exact name of the ODBC driver? Do you have any other ODBC drivers you can test?

I see you are also testing via EG and a local SAS server installed on the PC? If that is correct are you using a MS SQL Server Native Client driver? Again what is the name and version?

[DanielKaiser]

@SASKiwi 
I had the same problem in the past when we tried to use SAS CPM on linux.
So yes - we have this problem with all MSSQL tables that are connected via ODBC on Linux.

When I use the local SAS Server with the odbc-connection defined in the ODBC Admin, I dont have the problem with the incorrect tablename but still have to problem with the encoding-problem utf-8 and utf-16le.

The Driver Name on Linux is "libmsodbcsql-13.1.so.9.2" - should be the "Microsoft ODBC Driver 13.1 for SQL Server".

I also have a DataDirect Driver, but in this case - even with SCHEMA definied, I got no table returned.

[SASKiwi]

@DanielKaiser  - Have you raised this with Tech Support yet? Probably the best option if you haven't done so already.

[DanielKaiser · Accepted solution]

@Ksharp @SASKiwi @Kurt_Bremser thanks for your help so far - you did a lot of the prework to solve this issue together with the SAS Support.

I had to add three parameters (not sure which one exactly solved it) to the odbc.ini or the sasenv_local
These two parameters where added to the DataDirect Driver Datasource Connection information in the odbc.ini

client charset  = UTF-8
EnableQuotedIdentifiers=1
And to the sasenv_local I added this parameter

export EASYSOFT_UNICODE=YES
After this everything worked as expected... now sure where and how I should have found these information in any SAS-Documentation or DataDirect information