topic Re: proc sql and connect using in SAS Programming

proc sql and connect using

DBailey — Tue, 14 Jan 2025 14:14:20 GMT

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?

Re: proc sql and connect using

quickbluefish — Tue, 14 Jan 2025 18:36:50 GMT

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

Re: proc sql and connect using

quickbluefish — Tue, 14 Jan 2025 18:40:33 GMT

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] ;

Re: proc sql and connect using

Tom — Tue, 14 Jan 2025 18:44:06 GMT

@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.

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 18:59:34 GMT

I do.

Re: proc sql and connect using

quickbluefish — Tue, 14 Jan 2025 19:00:12 GMT

In the terminal, type 'klist' and tell us what you see.

Re: proc sql and connect using

quickbluefish — Tue, 14 Jan 2025 19:04:27 GMT

...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.

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 19:06:06 GMT

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 ''.

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 19:15:18 GMT

I see

credentials cache 'XXXXX' not found

Re: proc sql and connect using

SASKiwi — Tue, 14 Jan 2025 19:19:31 GMT

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.

Re: proc sql and connect using

quickbluefish — Tue, 14 Jan 2025 19:22:23 GMT

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

Re: proc sql and connect using

quickbluefish — Tue, 14 Jan 2025 19:29:00 GMT

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

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 19:36:16 GMT

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

Re: proc sql and connect using

Tom — Tue, 14 Jan 2025 20:02:35 GMT

@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 ?

Re: proc sql and connect using

SASKiwi — Tue, 14 Jan 2025 20:02:56 GMT

@DBailey - My issue is with SQL Server and SAS/Access Interface to ODBC. CONNECT USING works fine with a prior LIBNAME statement, but not with a pre-defined library.

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 20:26:50 GMT

I just tried that:

proc sql;

select count(*) as recs

from quest.businessunit;

connect using quest as q;

create table work.test as select * from connection to q (

select * from businessunit

);

disconnect from q;

quit;

the count worked but the connect using did not.

ODBC_2: Prepared: on connection 1
select COUNT(*) as "recs" from "dbo"."businessunit" TXT_1 WITH (NOLOCK)

ODBC_3: Executed: on connection 1
Prepared statement ODBC_2

ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
37
38 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

I'll see if I can find the authdomain and try a "connect to" instead of "connect as"

Re: proc sql and connect using

DBailey — Tue, 14 Jan 2025 20:29:03 GMT

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.

Re: proc sql and connect using

Tom — Tue, 14 Jan 2025 20:31:30 GMT

In that case open a ticket with SAS support and see if they can explain why it does not work and whether there is a solution.