BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tisbam
Fluorite | Level 6

Hello,

 

I am trying to define an ODBC connection to a SQL Server and mapping a libref to it.

Connection seems to be working (tested with isql). Libname statement executes succesfully, however all tables names appear as single letter going through the alphabet... 

 

libname myodbc ODBC dsn="DBDSN" user="DBUSER" password="DBPASSWD" schema="dbo";

tisbam_0-1618938909392.png

 

Any ideas?

 

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
DanielKaiser
Pyrite | Level 9
This sounds exactly like my problem.

SAS-Support told me to add these parameteres, did you try this?

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

View solution in original post

8 REPLIES 8
SASKiwi
PROC Star

What SAS user interface are you using and what version? What SAS version are you using? Does the LIBREF (MYODBC) show in full? What ODBC driver to SQL Server are you using? Older version drivers can cause problems.

 

If possible can you test this on another PC and is the behaviour the same? 

tisbam
Fluorite | Level 6

Hi,

 

This is on SAS Viya 3.5 (Not sure if latest ones but last updated September).

SAS user interface are you using and what version : Enterprise Guide and SAS Studio V

Does the LIBREF (MYODBC) show in full? Not sure I understand what you mean.

What ODBC driver to SQL Server are you using? Microsoft ones (libmsodbcsql-17.5.so.2.1) and UnixODBC (updated version 2.3.7).

 

Thanks

SASKiwi
PROC Star

I'd suggest a Tech Support track would be best to progress this. Has this problem just started appearing or is it a first-time one?

 

Have you tried another SQL Server database and do you get the same issue?

 

When you expand the Server List in EG, you would be expanding the library name to get the list of tables. Just wanted to check if the library name was also truncated.

DanielKaiser
Pyrite | Level 9

Hi @tisbam,

maybe have a look at this thread: Connection-to-MSSQL-returns-tablename-with-spaces-between-each 

I had the same problem - the single letter usually occurs because between each letter is a symbol or space SAS can not work with.

I marked the solution - maybe this helps you too!

tisbam
Fluorite | Level 6

Hi,

There are indeed tables with underscores (_) in the name.
However when running:
options validmemname=extend ;

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

Tables appear with correct names... So not sure if this is related.

tisbam_0-1618991343451.png

 

tisbam
Fluorite | Level 6

I'd suggest a Tech Support track would be best to progress this.

  > Yes, will submit one, and will post solution here if any is found!

Has this problem just started appearing or is it a first-time one?

  > First time setting it up. I managed to set it up on a SAS 9.4 install on Windows via the Management Console...

Have you tried another SQL Server database and do you get the same issue?

  > Dont have another one set up for now.

When you expand the Server List in EG, you would be expanding the library name to get the list of tables. Just wanted to check if the library name was also truncated.

  > Library name is fine (but defined by me with >8 cap letters)

 

Thanks

DanielKaiser
Pyrite | Level 9
This sounds exactly like my problem.

SAS-Support told me to add these parameteres, did you try this?

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
tisbam
Fluorite | Level 6

Hello,

 

Yes! Thank you. In the meanwhile I also had an answer of the tech support.

 

In a bit more details:

 

Check that the env variable is EASYSOFT_UNICODE=YES with a new session in StudioV for instance :

%put %quote(%sysget(EASYSOFT_UNICODE));

 

If not, add the following line in these files

     export EASYSOFT_UNICODE=YES 

 

  • /opt/sas/viya/config/etc/cas/default/cas_usermods.settings
  • /opt/sas/spre/home/SASFoundation/bin/sasenv_local
  • /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh
  • /opt/sas/viya/config/etc/sysconfig/compsrv/default/sas-compsrv

 

Then test access to the libname in a new session.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

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.

Discussion stats
  • 8 replies
  • 2052 views
  • 0 likes
  • 3 in conversation