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 @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
Results in this pretty long error message and
And Log says "No rows were selected". The library also does not show any tables
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
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?
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.
Find more tutorials on the SAS Users YouTube channel.