BookmarkSubscribeRSS Feed
jchiang
Calcite | Level 5

I'm noticing odd behavior with ODBC in Linux64 operating system where CONNECT USING and CONNECT TO differ on DSN-less (NOPROMPT) connections. Cannot find relevant documentation or if this is a bug. Does anyone know if there is a solution to allow for CONNECT USING to function like CONNECT TO? We hope to use CONNECT USING so that the LIBREF can be defined only once in code, rather than repeatedly calling a NOPROMPT.

 

I'm on SAS9.4M6 In the below statements I have masked connection details but preserved the length of the strings by putting in an "×" where a character was.

 

The following statement works and the ODBC Trace exits from SQLDriverConnect with SQL_SUCCESS, connection string length of 366.

 

OPTIONS
    SASTRACE='d,,,'
    SASTRACELOC=saslog
    NOSTSUFFIX;

libname sf odbc
noprompt="DRIVER={/××××××××××××××××××××××××××××/libSnowflake.so};
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=××××××××××××××××××××××××××××××snowflakecomputing.com;
UID=×××××××××××××××××××××××××;
PRIV_KEY_FILE=×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××;
PRIV_KEY_FILE_PWD=;
ROLE=×××××××××××××××××××××××××××××××××;
WAREHOUSE=×××××××××××××××××××××××;
DATABASE=×××××××××××××××××;"
readbuff=999;

proc sql;
    connect using sf;
    disconnect from sf;
quit;
%PUT &=SQLXRC.;
%PUT &=SQLXMSG.; libname sf clear; OPTIONS SASTRACE=OFF;

 

However. If I edit the connection string and add parameters, or even simply change the location of the private key to a single character longer, the libref assignment succeeds but the CONNECT USING fails. Also, if I keep the same 366 length NOPROMPT as above but edit READBUFF=1000 (one additional digit), I get the same error. Interestingly, adding SCHEMA= option to the libref does not produce this error.

 

libname sf odbc
noprompt="DRIVER={/××××××××××××××××××××××××××××/libSnowflake.so};
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=××××××××××××××××××××××××××××××snowflakecomputing.com;
UID=×××××××××××××××××××××××××;
PRIV_KEY_FILE=××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××;
PRIV_KEY_FILE_PWD=;
ROLE=×××××××××××××××××××××××××××××××××;
WAREHOUSE=×××××××××××××××××××××××;
DATABASE=×××××××××××××××××;"
readbuff=999;

This edit (1 character longer NOPROMPT) produces an error where SQLDriverConnect read a "0 length" NOPROMPT and PROC SQL returns:

%PUT &=SQLXRC.;
 SQLXRC=IM002
%PUT &=SQLXMSG.;
 SQLXMSG=[unixODBC][Driver Manager]Data source name not found, and no default driver specified

However, the LIBNAME statement it is USING executes without issue, with NOPROMPT having a 367 length reported in trace.

 

Oddly, the length is completely irrelevant for all scenarios tested when using CONNECT TO. All scenarios run successfully in CONNECT TO without issue, including with READBUFF up to 32767.

 

 

 

2 REPLIES 2
SASKiwi
PROC Star

I suggest you open a Tech Support track on this. I use CONNECT USING a lot and never had any problems but I'm not accessing Snowflake.

 

Not sure if this old SAS note is still relevant or not: https://support.sas.com/kb/50/855.html

 

jchiang
Calcite | Level 5

Thanks, I think I'll have to go down the technical support track...

Thanks for linking that issue, I don't think it applies anymore in 9.4M6 seeing that simply changing the READBUFF can trigger issue using CONNECT USING.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 639 views
  • 1 like
  • 2 in conversation