JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no table

[rtynd4]

Not sure if this is the right group. but here goes:

I have created a JDBC library using the SAS Management Console. When trying to registering tables it return no tables and the error message is "No Tables were retrieved from your query". Trying to connect to Azure SQL Server database. 

Below is the details of the registering table. There is an exclamation next to the DRIVERCLASS.

If I log into the database using a different tool the default schema is dbo which has no tables. The library is assigned but it fetches no tables to register.

Any help would be appreciated.

NOTE: The autoexec file, /usr/sas/config8/Lev2/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization.
1 LIBNAME pathncpp SASIOJDB URL="serv-ccc-path-pp-synws.sql.azuresynapse.net:1433;
2 Authentication=ActiveDirectoryServicePrincipal;
3 databaseName=Mydatabase;
4 schema=Myschema;
5 user=userxxx;
6 password=xxxT;
7 "
8 CLASSPATH="/usr/sas/sashome8/AccessClients/9.4/DataDrivers/jdbc/synapse"
8 ! DRIVERCLASS="com.microsoft.sqlserver.jdbc.SQLServerDriver" AUTHDOMAIN="PathNC_PreProd_Auth" ;
NOTE: Credential obtained from SAS metadata server.
NOTE: Libref PATHNCPP was successfully assigned as follows:
Engine: JDBC
Physical Name:
jdbc:sqlserver://serv-ccc-path-pp-synws.sql.azuresynapse.net:1433;Authentication=ActiveDirectoryServicePrincipal;database
Name=Mydatabase;schema=Myschema;user=userxxx;password=xxx;

[SASKiwi]

Does the chosen database and schema genuinely have tables in it? If so how long are the tables names? If they are all > 32 characters then SAS can't handle this and will not return any names. Try creating a table <= 32 characters long and see if that works. Open the LIBREF pathncpp in SAS EG or SAS Studio to see if any tables are listed.

[rtynd4]

There are 3 out of 38 tables where the name is > 32. However, there are many columns in each table where the column length is > 32.

Thx for the help.

[LinusH]

Column length up 32 is no problem for SAS, but column name length has the same limitation as table name.
If you have tables that you know comply with SAS naming standard (also beware of special character, spaces, initial digit etc) and they're not visible to SAS - try to connect to the database using the same credentials from another client using the same connection configuration.

[rtynd4]

I am able to connect using DBVisualizer. 

[SASKiwi]

What does PROC DATASETS report?

proc datasets library = PATHNCPP;
  contents;
run;
quit;

[rtynd4]

I get this result using the libname stated above.

WARNING: No matching members in directory.
133 ! contents; run;
ERROR: There is not a default input data set (_LAST_ is _NULL_).

If I run this I get these results. But trying to get the library working so that I can register tables.

proc sql;
connect using pathncpp;
SELECT * from connection to pathncpp
(select
distinct TABLE_SCHEMA
from INFORMATION_SCHEMA.TABLES
) ;
SELECT * from connection to pathncpp
(select
top 5 TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'itk_curated'
) ;
quit ;

-------------------

TABLE_SCHEMA
--------------------------------------------------------------------------------------------------------------------------------
audit
config
gen_curated
gen_land
itk_curated
itk_federal
itk_land
itk_rpt
itk_stg
The SAS System 07:41 Tuesday, January 27, 2026 2

TABLE_SCHEMA
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
itk_curated
ASM_Assessment_Person_Involved__c

[Tom]

So your schema has one table and its name uses 33 bytes. 

So SAS will not SEE it when you try to access it as if was an actual SAS dataset.

That is why the LIBNAME statement said there were no tables found.

Your connection is working, so you should be able to proceed with testing if you can make a new table.

data pathncpp.test1;
  set sashelp.class;
run;

[rtynd4]

No, there are more than one table, I just showed a partial output. The query is for the top 5 tables.

There are 38 tables with 3 of them with names > 32.

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
itk_curated
ASM_Assessment_Person_Involved__c

itk_curated
CAS_Assist_Request__c

itk_curated
CAS_Case__c

itk_curated
CAS_Case_COI__c

itk_curated
CAS_Case_Related_Forms_and_Tools__c

[Tom]

Are you sure your libref is defined to point to that itk_curated schema?

Is it possible that your SQL Server collation setting is case sensitive?  That will make object names case sensitive in SQL Server.  Perhaps SAS is trying to query ITK_CURATED schema instead?

[rtynd4]

I currently have the schema=ITK_CURATED in the Url. I have tried it in lower case also.

[SASKiwi]

My best guess as to what's happening here is that for some reason your schema setting isn't working and that you are ending up in a default schema with no tables. I'd suggest opening a track with Tech Support to make further progress. Is JDBC the only SAS/ACCESS product you have? Is SAS/ACCESS to ODBC or SAS/ACCESS to SQL Server an option you can try? 

[rtynd4]

We tried ODBC but could not get that working. I have a case open with SAS on this. but slow response so far. Hopefully they will help solving this.

If I resolve this I will post it here.

Thanks for the help.

[SASKiwi]

We use ODBC with SQL Server a lot and find it works well. We don't use JDBC at all. In my experience the connection strings are a lot more straightforward. Feel free to try that again if you like. 

[rtynd4]

We tried connecting to the Azure SQL db with ODBC but could not get that to work.