- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you have a Putty terminal or similar where you can access the SAS Grid server?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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] ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ''.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I do.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I see
credentials cache 'XXXXX' not found
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @SASKiwi - yeah, I'm not familiar with the 'connect using' syntax.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
we do use "connect using" for other predefined libraries...but they're oracle and not sqlserver. just trying to troubleshoot a bit
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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 ?