SAS libname syntax to connect to SQL Server via ODBC

Question

I've googled for a few days and can't get this working.

I'm using SQL 2014 and the adventureworks database.

I've got SAS 9.3.

I've tried different ODBC settings, as in setting a default database, using Windows login, or SQL login. Different drivers. Still nothing, used complete and noprompt:

libname mylib odbc complete='TrustedConnection=True;DSN=test2;SERVER=MYSERVER\SQLEXPRESS;DATABASE=AdventureWorks2012;'stringdates=yes;
proc print data=mylib.Person;

Error message:

NOTE: Libref MYLIB was successfully assigned as follows: Engine: ODBC Physical Name: test2

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended.

NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE PRINT used (Total process time): real time 5.48 seconds cpu time 0.34 seconds

700 proc print data=mylib.Person; ERROR: File MYLIB.Person.DATA does not exist

I've tried with Person.Person and also in the connection string using schema=Person.

However if I use the following it works. I put the default database in the ODBC:

proc sql;
   connect to odbc as mycon
   (required = "dsn=awlt32;uid=sa;password=mypassword");
   select *
   from connection to mycon
      (select *
      from Person.Person);

disconnect from mycon;
quit;

I know this is a super simple question but I can't seem to get it working, no matter what example code I find. Thanks in advance.

Sumo

NOTE 49-169 should disappear if you put a blank before stringdates=yes – Inefficacious

Any idea with the:700 proc print data=mylib.Person; ERROR: File MYLIB.Person.DATA does not exist – Sumo

If you run proc contents data=mylib._all_; do you see any tables? I don't know adventureworks. Could it be you need to add a schema name to the libname statement? – Alterative

I've tried with the schema name in the connection string and using mylib.Person.Person., AdventureWorks in a MS test DB. – Sumo

It looks like your quotation mark ends too early? Stringdate should be in quotes and semicolon after the connection string. Also try adding schema=dbo and note that table names are case sensitive. – Niggardly

Thanks Reeza, I haven't tried it with schema=dbo. Will try that later today. – Sumo

schema=Person looks like it's the right thing to use here if table person is in the Person schema. – Lais

Got it working. schema=Person is correct, but I had never tried it in the libname statement but outside of the " " connection string. opps. All fixed. Thanks all. – Sumo

Answer 1

1. Add the database connection to your ODBC driver; you will need the name of this connection in your libname,
2. Use the libname below and update the datasrc, sql user and password

LIBNAME mylib ODBC DATASRC=name from step1 SCHEMA=dbo USER=sql user PASSWORD="xxx" ;

proc datasets lib=mylib ;
quit;

Nodab