CODEWITHSUNDEEP
sql-serversaspass-through
Lovnlust
Lovnlust

Reputation: 1537

SAS SQL Pass-Through Facility Specifics for ODBC

First time to use SQL Pass-Through and couldn't figure out how it works.

libname myl odbc datasrc=AAA;

I tried this 

proc contents data=myl.mytable;run;

It works exactly the same as 

proc contents data=AAA.mytable;run;

So the libname statement works?

But when I tried this no luck.

proc sql;
connect to ODBC(datasrc=AAA);
execute (delete from Oit.d1 where datepart(Date) >= '08Mar2014'd) by ODBC;
disconnect from ODBC;
quit;

Error shows 

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Did I miss some arguments?

Upvotes: 1

Views: 4207

Answers (1)

Robert Penridge
Robert Penridge

Reputation: 8513

You can check the following:

  • This error message is usually caused by a typo in the datasrc= statement, or failure to setup the DSN in Control Panel->ODBC Data Sources.
  • Be sure that the value you entered (AAA in your example) is exactly how the name appears in Control Panel->ODBC Data Sources.
  • Check that when you click test in the ODBC settings, it can connect successfully.
  • Finally, be sure that your ODBC connection is setup as a System DSN and not a User DSN if you will be running the SAS job from an account different to your windows login account. System DSNs are visible to all users on the computer, not just the user that created that ODBC connection.

This is how my ODBC passthrough statements normally look for a simple query:

/* mySQL example */
proc sql noprint;
  connect to odbc (datasrc=myDSN user=myusername password="mypassword");

  create table sqlo as 
  select *
  from connection to odbc
  (
  select * from database.table limit 1
  )
  ;
quit;

Upvotes: 1

Related Questions