SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
DBailey
Lapis Lazuli | Level 10

We have a sas grid environment with predefined libraries (against sql server).  This works:

proc sql noprint;
create table work.test as select * from quest.BusinessUnit;
quit;

However, this fails:

 

proc sql noprint;
connect using quest as q;
create table work.test as
select * from connection to q (
	select * from businessunit
);
disconnect from q;
quit;

with this error:

 

39 connect using quest as q;
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

 

Any suggestions?

 

17 REPLIES 17
quickbluefish
Pyrite | Level 9

Do you have a Putty terminal or similar where you can access the SAS Grid server?

quickbluefish
Pyrite | Level 9

When I do this, I specify a DATASRC argument:


connect to SQLSVR as Q (datasrc=DBNAME);

*... where DBNAME is the name of your target database, as in [DBNAME].[schema].[tablename] ;
Tom
Super User Tom
Super User

@quickbluefish wrote:

When I do this, I specify a DATASRC argument:


connect to SQLSVR as Q (datasrc=DBNAME);

*... where DBNAME is the name of your target database, as in [DBNAME].[schema].[tablename] ;

CONNECT TO and CONNECT USING are two different statements with different meaning and syntax.

DBailey
Lapis Lazuli | Level 10

So in our environment, the username/passwords are stored in an authdomain and are used in the predefined library.  If I try and use just the datasource name, I get this:

 

proc sql noprint;
35 connect to sqlsvr as q (datasrc=QUEST);
ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver][SQL Server]Login failed for user ''.

DBailey
Lapis Lazuli | Level 10

I do.

quickbluefish
Pyrite | Level 9
In the terminal, type 'klist' and tell us what you see.
quickbluefish
Pyrite | Level 9
...well, don't tell us exactly what you see, but just make sure it doesn't say something to the effect of, "no credentials found". I find that this is the source of my problems sometimes when I get the error you describe. Happens when something goes wrong with authentication at login.
DBailey
Lapis Lazuli | Level 10

I see 

credentials cache 'XXXXX' not found

SASKiwi
PROC Star

I suggest you test CONNECT USING with a LIBNAME statement first. I've tried CONNECT USING with a metadata-defined database connection and I've not been able to get it working. I'm not sure what the problem is. It might be use of the DEFER = YES LIBNAME option for greater efficiency.

quickbluefish
Pyrite | Level 9

Thanks @SASKiwi - yeah, I'm not familiar with the 'connect using' syntax.

quickbluefish
Pyrite | Level 9

I'm not sure if it's the same syntax, but our corresponding libname is written like this:


libname Q sqlsvr datasrc=DBNAME schema=SCHEMA;

Not sure if specifying schema is necessary.  If so, it might be something like schema=Dflt

DBailey
Lapis Lazuli | Level 10

specifying a schema is required if you need something other than the default and if you want to browse the library contents.  I think the most often used default is dbo

 

and i'm going to try and get the auth domain so I can execute the libname statement directly.

DBailey
Lapis Lazuli | Level 10

we do use "connect using" for other predefined libraries...but they're oracle and not sqlserver.  just trying to troubleshoot a bit

Tom
Super User Tom
Super User

@DBailey wrote:

we do use "connect using" for other predefined libraries...but they're oracle and not sqlserver.  just trying to troubleshoot a bit


Did you try exercising the libref using some other command, like PROC CONTENTS to make sure it is working, before trying to run the CONNECT USING ?

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 17 replies
  • 533 views
  • 2 likes
  • 4 in conversation