topic Re: ODBC Connection from SAS 9.4 M6 on Windows Server Cannot see tables. in Administration and Deployment

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

thesasuser — Wed, 19 Feb 2025 15:58:25 GMT

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;

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

MrSantos — Wed, 19 Feb 2025 16:59:27 GMT

Hello,

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

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

thesasuser — Wed, 19 Feb 2025 17:16:41 GMT

Yes.

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

Nigel_Pain — Wed, 19 Feb 2025 17:17:59 GMT

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

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

thesasuser — Wed, 19 Feb 2025 17:23:09 GMT

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;

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

Tom — Wed, 19 Feb 2025 17:40:38 GMT

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?

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

thesasuser — Wed, 19 Feb 2025 18:11:56 GMT

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.

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

SASKiwi — Wed, 19 Feb 2025 20:18:20 GMT

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.

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

thesasuser — Wed, 19 Feb 2025 21:57:48 GMT

Thanks.
They are less than 32 characters.

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

SASKiwi — Wed, 19 Feb 2025 22:19:03 GMT

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.

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

Ksharp — Thu, 20 Feb 2025 02:09:58 GMT

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

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

Ksharp — Thu, 20 Feb 2025 02:12:11 GMT

https://www.connectionstrings.com/

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

JuanS_OCS — Thu, 20 Feb 2025 03:42:37 GMT

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?

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

ballardw — Thu, 20 Feb 2025 17:30:11 GMT

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.

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

thesasuser — Fri, 21 Feb 2025 19:30:56 GMT

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.

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

Tom — Fri, 21 Feb 2025 20:31:50 GMT

Did you check the various options for the LIBNAME statement to see if any can help you?

https://documentation.sas.com/doc/sk/pgmsascdc/v_060/acreldb/p11x7o76auf85bn1ev4m0oty0bno.htm

In particular try the DRIVER_TRACE and related options so you can see what SQL SAS is generating when it tries get the list of files.

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

thesasuser — Mon, 24 Feb 2025 15:45:08 GMT

Hello @Tom
Thanks. I have tried using driver_trace option. But I have the error as follows

WARNING: Libref DBRK is not assigned. 
27 option VALIDMEMNAME=EXTEND VALIDVARNAME=ANY;
28 libname dbrk odbc dsn="dbrk" user="client_id" password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 
28 ! schema="catalog.schema" driver_trace=ALL; 
                              ____________ 
22 ERROR: Libref DBRK is not assigned. 
ERROR: Error in the LIBNAME statement. 
ERROR 22-7: Invalid option name DRIVER_TRACE.

Thanks once again

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

Tom — Mon, 24 Feb 2025 15:56:08 GMT

Unfortunately it does not look like that option works for ODBC.

https://documentation.sas.com/doc/da/pgmsascdc/v_060/acreldb/p11x7o76auf85bn1ev4m0oty0bno.htm

Engines:	BIGQUERY, HADOOP, JDBC, SPARK
Data source:	Google BigQuery, Hadoop, JDBC, Spark

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

Nigel_Pain — Mon, 24 Feb 2025 16:46:36 GMT

It's worth trying the SASTRACE system option to see what is actually being sent and received via ODBC:

SAS Help Center: SASTRACE= SAS System Option

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

thesasuser — Fri, 28 Feb 2025 16:49:09 GMT

Thanks @Nigel_Pain @Tom @ballardw @JuanS_OCS @Ksharp
I appreciate the help all of you have provided. All the suggestions were of great help.
I worked with SAS tech Support and under their guidance, the issue was finally resolved.
Thanks SAS Tech Support for your help.
I am summarizing the steps so that they help others in need.
Step 1: Configure the odbc manager at the OS level.
The authentication information, server details go here. Th interface is quite intuitive and one can fill up the details with the help of Datawarehouse/databricks team.
In my case they were hostname, database name, the http options, OAuth details

Step2 The libname statement that worked is as follows:

libname [YR_LIBREF] ODBC preserve_tab_names=yes schema=[SCHEMA_NAME] noprompt="DSN=[DSN_NAME];catalog=[CATALOG_NAME];Schema=[SCHEMA_NAME];";

I have used [PARAMETER]for values that are needed to be input.