topic Re: Connection to MSSQL returns tablename with spaces between each letter in Administration and Deployment

Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 09:42:44 GMT

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.

Re: Connection to MSSQL returns tablename with spaces between each letter

Ksharp — Fri, 29 Jan 2021 11:15:59 GMT

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;

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 12:06:01 GMT

Results in this pretty long error message and

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

Re: Connection to MSSQL returns tablename with spaces between each letter

Ksharp — Fri, 29 Jan 2021 12:34:59 GMT

Can you show your code and full log ?

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 12:45:55 GMT

Sure

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

Re: Connection to MSSQL returns tablename with spaces between each letter

Kurt_Bremser — Fri, 29 Jan 2021 12:47:41 GMT

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

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 12:50:12 GMT

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

Re: Connection to MSSQL returns tablename with spaces between each letter

Kurt_Bremser — Fri, 29 Jan 2021 12:52:54 GMT

Look if this post is helpful:

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

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 12:58:58 GMT

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

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.

Re: Connection to MSSQL returns tablename with spaces between each letter

Ksharp — Fri, 29 Jan 2021 13:00:43 GMT

Or could try create a table ?

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

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 13:02:52 GMT

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

Re: Connection to MSSQL returns tablename with spaces between each letter

Ksharp — Fri, 29 Jan 2021 13:06:42 GMT

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

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Fri, 29 Jan 2021 13:31:01 GMT

Hm. Created with 0 Rows 😞

Re: Connection to MSSQL returns tablename with spaces between each letter

Ksharp — Sat, 30 Jan 2021 10:51:00 GMT

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

Re: Connection to MSSQL returns tablename with spaces between each letter

Tom — Sat, 30 Jan 2021 18:33:51 GMT

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?

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Sat, 30 Jan 2021 19:47:02 GMT

No, no tables

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Sat, 30 Jan 2021 19:48:21 GMT

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

Re: Connection to MSSQL returns tablename with spaces between each letter

SASKiwi — Sat, 30 Jan 2021 21:34:34 GMT

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?

Re: Connection to MSSQL returns tablename with spaces between each letter

Ksharp — Sun, 31 Jan 2021 11:08:49 GMT

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;

Re: Connection to MSSQL returns tablename with spaces between each letter

DanielKaiser — Mon, 01 Feb 2021 05:57:23 GMT

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?