topic Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t in Developers

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

rtynd4 — Mon, 26 Jan 2026 17:42:45 GMT

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;

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

SASKiwi — Mon, 26 Jan 2026 20:10:15 GMT

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.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Mon, 26 Jan 2026 23:06:59 GMT

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.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

LinusH — Tue, 27 Jan 2026 11:53:44 GMT

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.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Tue, 27 Jan 2026 15:42:41 GMT

I am able to connect using DBVisualizer.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

SASKiwi — Tue, 27 Jan 2026 18:59:07 GMT

What does PROC DATASETS report?

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

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Tue, 27 Jan 2026 20:12:38 GMT

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

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

Tom — Tue, 27 Jan 2026 20:30:16 GMT

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;

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Tue, 27 Jan 2026 20:31:37 GMT

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

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

Tom — Tue, 27 Jan 2026 20:55:08 GMT

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?

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Wed, 28 Jan 2026 12:53:38 GMT

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

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

SASKiwi — Wed, 28 Jan 2026 19:16:05 GMT

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?

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Wed, 28 Jan 2026 19:49:29 GMT

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.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

SASKiwi — Wed, 28 Jan 2026 20:48:19 GMT

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.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Wed, 28 Jan 2026 20:49:52 GMT

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

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Wed, 28 Jan 2026 20:50:28 GMT

I will try it again and let you know what issues we have.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

SASKiwi — Wed, 28 Jan 2026 21:03:44 GMT

The first thing to check are firewall rules for the port(s) the ODBC driver is using. Does your SAS server run on Linux or Windows? It's a lot easier to test this on Windows as you can do this all in the Windows ODBC Administrator tool.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Thu, 29 Jan 2026 12:38:13 GMT

The install is on Solaris.

I have tried to connect using

SAS/ACCESS Interface to Microsoft SQL Server

Is there a product SAS/ACCESS Interface to ODBC?

I created a server and library using SQL Server and get errors when trying to register tables.

In the SMC I created a server and library choosing ODBC but when I try to register tables it says there is not ODBC drivers.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

SASKiwi — Thu, 29 Jan 2026 19:11:24 GMT

Yes, there is a product called SAS/ACCESS Interface to ODBC. You can check if you have it installed and licensed by running this:

proc product_status;
run;
proc setinit;
run;

However SAS/ACCESS Interface to SQL Server should also work. Did the SQL Server LIBNAME assign without errors or not? Please post your SAS log.

Re: JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

rtynd4 — Mon, 02 Feb 2026 14:11:04 GMT

Here is the latest on this issue.

Working with SAS it was determined that there seems to be a bug in JDBC driver connection to SQL Server in that it returns an empty set of tables.

They gave me a workaround to use SAS code to register tables.

So, created the library with settings like this below. Also, it is best to have double quotes for connection below.

URL="jdbc:sqlserver://myserver:1433;Authentication=ActiveDirectoryServicePrincipal;databaseName=MyDb;schema=MySchema;user=myuserid;password=mypw;"

DriverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"

ClassPath="/usr/sas/sashome/AccessClients/9.4/DataDrivers/jdbc/..."

The above are the settings for the connection.

For the library selected the JDBC server created in the SMC and set the schema names and the connection.

After that used the code editor to execute this code:

proc metalib ;
omr (library="JDBC_LIBRARY_PREPROD");
select ("MyTable") ;
report ;
run;

SAS is going to provide information about generating a log file so that it can be sent to Microsoft.

I originally tried to use SQL Server connection but the driver does not support the AuthenticationMethod=13 so this did not work.