ODBC Sql Server - Column names are single letters

[tisbam]

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";

Any ideas?

Thank you

[SASKiwi]

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]

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]

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]

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]

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]

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 · Accepted solution]

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]

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.