topic Re: Strange table names when accessing MS SQL server via ODBC in New SAS User

Strange table names when accessing MS SQL server via ODBC

dimy4 — Tue, 11 Dec 2018 14:24:10 GMT

I am trying to establish a connection to MS SQL server databse from a SAS program using a standard ODBC driver

The code:

libname testdb odbc noprompt="server=serv1;DRIVER=SQL Server;Trusted Connection=yes" DATABASE=mydb schema=dbo PRESERVE_TAB_NAMES=YES;

This works without problems, however the table names inside a dataset are strangely encoded:

I don't know is it because of the dirve or am i doing something wrong?

If i try to print the table data like

proc print data=testdb.Step;
run;

This produces the error:
proc print data=testdb.Step;
ERROR: File TESTDB.Step.DATA does not exist.
25 run;

Re: Strange table names when accessing MS SQL server via ODBC

LinusH — Tue, 11 Dec 2018 14:54:37 GMT

Probably not the driver, maybe something else.

I can't see "Step" table in your print screen - verify by scrolling.

Also, check with your SQL Server what tables/views are actually stored in this schema, by using SQL Server Management Studio, or other client of choice. Potentially talk to your DBA to get your parameters/autorization right.

Re: Strange table names when accessing MS SQL server via ODBC

dimy4 — Tue, 11 Dec 2018 17:04:18 GMT

This is the exact problem - instead of the table names there are some strange #NNNNNN number names under testdb which you can see on the screenshot. It should be normal table names shown

There is a Step table on this database and also other tables. The exact amount of tables is the same as amount of #NNNNN names on a screenshot. So, I suppose one of them is actually "Step" table..

But why are they getting these strange names?

Re: Strange table names when accessing MS SQL server via ODBC

Ksharp — Wed, 12 Dec 2018 13:58:39 GMT

1) Try this libname

libname testdb odbc dsn=mydb schema=dbo user=xxx password=xxxx;

2)try

options validmemname=extend;