proc sql and connect using

[DBailey]

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?

[quickbluefish]

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

[quickbluefish]

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]

---

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

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]

I do.

[quickbluefish]

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

[quickbluefish]

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

I see 

credentials cache 'XXXXX' not found

[SASKiwi]

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]

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

[quickbluefish]

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]

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]

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

[Tom]

---

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