ODBC Connection from SAS 9.4 M6 on Windows Server Cannot see tables.

[thesasuser]

Hello
I have been able to configure Connection to Databricks on Windows server.
Using SAS EG I am able to create a libname statement and library is created. But I cannot see any tables. My code is

libname dbrk odbc dsn="dbrk" user="clinet-id" password="password" schema="catalog.schema";
proc sql;
select * from dictionary.tables where libname="DBRK";
quit;

Works perfectly library is created. But do not see any tables. The log is as follows

         libname dbrk odbc dsn="dbrk" user="client-id" password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
27       !  schema="catalog.schema";
NOTE: Libref DBRK was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: dbrk
28         proc sql;
29         select * from dictionary.tables where libname="DBRK";
NOTE: No rows were selected.
30         quit;

[MrSantos]

Hello,

Does user="client-id" have enough permissions on schema="catalog.schema" in Databricks?

[thesasuser]

Yes.

[Nigel_Pain]

It's worth trying a special catalog query as you're using ODBC. Something like:

PROC SQL;
connect to odbc as dbrk (dsn="dbrk" user="clinet-id" password="password" );
select * from connection to dbrk (
ODBC::SQLTables(,,,)
);
disconnect from dbrk;
quit;

This should list all tables in all schemas to which you have access.

More information: SAS Help Center: SQL Pass-Through Facility Specifics for ODBC

[thesasuser]

Thanks @Nigel_Pain .
I appreciate your help. The result is the same. The log is here

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         PROC SQL;
27         connect to odbc as dbrk (dsn="dbrk" user="client-id"
27       ! password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX );
28         select * from connection to dbrk (
29         ODBC::SQLTables(,,,)
30         );
NOTE: No rows were selected.
31         disconnect from dbrk;
32         quit;

[Tom]

Are databricks schema names case sensitive?

Is the DSN defined to point to the right database? Are you sure you didn't connect to some other databricks instance that does not have the schema and/or tables of interest?

[thesasuser]

Thanks @Tom . @MrSantos 
I just ran a test using plain java code and was able to see the tables with the same credentials as I used with SAS.
I changed the case of catalog name and yes it worked as before. So, it appears they appear to be case insensitive.

[SASKiwi]

How long are your table names? If they are greater than 32 characters you will most likely need to use SQL Passthru to access them.

[thesasuser]

Thanks.
They are less than 32 characters.

[SASKiwi]

I suggest you try to query the Databrick's dictionary table via SQL Passthru:

libname dbrk odbc dsn="dbrk" user="clinet-id" password="password" schema="catalog.schema";

proc sql;
 connect using dbrk;
  create table Want  as 
  select * from connection to dbrk
  (SELECT *
    FROM information_schema.tables
    WHERE table_schema = 'information_schema'
   )
  ;
quit;

If there are any tables in the specified schema they should be listed. 

[Ksharp]

I think you should check SCHEMA is correct or not .
Talk to your Admin to find which SCHEMA of tables it is.
https://communities.sas.com/t5/Administration-and-Deployment/Tables-not-shown-up/m-p/939847#M28853

Also you could try upcase SCHEMA ,like:
schema=YOUR_SCHEMA

[Ksharp]

https://www.connectionstrings.com/

[JuanS_OCS]

Hi there,

In these cases I like to understand what’s going on behind the scenes, so I can pinpoint exactly the issue, instead of going through trial and error.

For that I always refer to the logs on the client side (ODBC client) and the database server logs (audit).

You clearly have an issue with permissions, as you have been told. There is no mistake there. Whether it’s a wrong schema or username or something else, we need to figure it out, thanks to those logs.

The server logs should already tell you a lot. Have you asked your DBAs what do they see when you connect with SAS?

From the client side, you either use STRACE or enable the client logs (ODBC ini for ODBC connections).

My personal thought, but this is without knowing more of your setup and design, is that either your connection to the schema is different from SAS to Java, or you are connecting with your OS user on top of the user in the lib name statement.

Would you mind sharing as well your exact code used in Java?

[ballardw]

I seem to recall that some of the ODBC connections in Windows are pretty restrictive and even though a connection is made that the SAS Dictionary tables aren't available.

Have tried something a Proc Print on a known table in that connection?

Another setting might involve the SAS system option VALIDMEMNAME=EXTEND before the ODBC connection is made to allow use of table names not normally acceptable in SAS, i.e. containing spaces or characters other than _, digits or letters.

[thesasuser]

Thanks @ballardw 
I am able to able to see the data from the table, but tables themselves are not visible in the library.
Using VALIDMEMNAME=EXTEND and VALIDVARNAME=ANY did not help.
One more observation I have. For the schema one has to use catalog_name.schema_name.  Using only the schema name seems to not work.
I was able to run proc contents on the table, but data=libref._ALL_ did not show up any tables. Similarly proc datasets libref=library did not show any tables.