topic Re: ODBC Sql Server - Column names are single letters in Administration and Deployment

ODBC Sql Server - Column names are single letters

tisbam — Tue, 20 Apr 2021 17:16:03 GMT

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

Re: ODBC Sql Server - Column names are single letters

SASKiwi — Tue, 20 Apr 2021 19:59:01 GMT

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?

Re: ODBC Sql Server - Column names are single letters

tisbam — Tue, 20 Apr 2021 20:23:43 GMT

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

Re: ODBC Sql Server - Column names are single letters

SASKiwi — Tue, 20 Apr 2021 21:07:00 GMT

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.

Re: ODBC Sql Server - Column names are single letters

DanielKaiser — Wed, 21 Apr 2021 06:13:00 GMT

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!

Re: ODBC Sql Server - Column names are single letters

tisbam — Wed, 21 Apr 2021 07:46:41 GMT

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

Re: ODBC Sql Server - Column names are single letters

tisbam — Wed, 21 Apr 2021 07:49:10 GMT

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.

Re: ODBC Sql Server - Column names are single letters

DanielKaiser — Wed, 21 Apr 2021 09:24:28 GMT

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

Re: ODBC Sql Server - Column names are single letters

tisbam — Tue, 27 Apr 2021 08:50:35 GMT

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.