topic Re: SAS libname MS SQL server ODBC in SAS Procedures

SAS libname MS SQL server ODBC

thetoddflatline — Tue, 11 Aug 2015 06:06:29 GMT

I've googled for a few days and can't get this working. Hopefully this is the correct forum.

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.

Re: SAS libname MS SQL server ODBC

SASKiwi — Tue, 11 Aug 2015 19:44:21 GMT

Try something like this. You don't need to specifiy a DSN, you can define it all in the connection string. Note don't leave any more blanks in the NOPROMPT string than in my example otherwise it won't work.

libname SQLSRVR odbc noprompt = "server=MYSERVER\SQLEXPRESS;DRIVER=SQLServer Native Client 11.0;Trusted Connection=yes" DATABASE = AdventureWorks2012 schema = dbo;

Re: SAS libname MS SQL server ODBC

thetoddflatline — Wed, 12 Aug 2015 08:58:12 GMT

Thank you very much!

Re: SAS libname MS SQL server ODBC

Kurt_Bremser — Wed, 12 Aug 2015 10:26:40 GMT

Insert a blank between the string and stringdates=yes to avoid the note 49-169.

To test your libname, do

proc datasets library=mylib;

quit;

and take a look at the output listing.

Re: SAS libname MS SQL server ODBC

Lenvdb — Thu, 13 Aug 2015 10:06:20 GMT

I have had some success with the Native ODBC SQL Server Driver on SAS 9.3 (On Linux) .

We do not use a Libname Statement for extracting data though.

We use this method to connect to MSSQL 2014 Server databases:

%let connx=(dsn=<myODBC_DSN> user=<MYLOGIN> pwd=xxxxxxx);

Proc SQL;

connect to odbc&connx;

Create Table Test as

Select * from connection to odbc

(SELECT * FROM [SQLDatabase].[dbo].[MyTable]);

Disconnect from odbc;

Quit;

This method is dependent on a ODNC.ini DSN entry, so you will need to make sure that you have a proper DSN in your ODBC.ini and ODBCINST.ini files on your Linux Server.