topic Re: How to derive my proc sql connection string from libname connection string? in SAS Procedures

How to derive my proc sql connection string from libname connection string?

saspert — Thu, 10 Sep 2015 15:08:36 GMT

Hi guys,

My libname is here -

LIBNAME WWTest ODBC NOPROMPT="server=HMIDWPRD03;driver=sql server;Trusted Connection=yes;Database=WW;" SCHEMA=dbo USER=myid PASSWORD="{sas002}secretalphanumericcode123" ;

I tried several kinds of combinations for proc sql but it will not work -

23 Proc sql;
24 Connect to odbc ( user=myid password=myunecryptedpwd database="WW.dbo") ;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
specified

I tried datasrc=/ database= /single quote/double quote/ and other combinations but of no avail.

Thanks,

saspert

Re: How to derive my proc sql connection string from libname connection string?

DBailey — Thu, 10 Sep 2015 19:23:02 GMT

Have you tried:

connect using WWtest as cWWTest;

Re: How to derive my proc sql connection string from libname connection string?

saspert — Thu, 10 Sep 2015 20:41:49 GMT

This is a more OS -related issue as per my supervisor. So we can ignore this for now. Thanks!

Re: How to derive my proc sql connection string from libname connection string?

SASKiwi — Thu, 10 Sep 2015 22:09:36 GMT

Does your LIBNAME actually work? Supplying a username and password is not required if you are using Windows authentication as your string Trusted Connection=yes; indicates. With this option SQL Server authenticates against your Windows user credentials.

Re: How to derive my proc sql connection string from libname connection string?

saspert — Fri, 11 Sep 2015 15:05:15 GMT

LIBNAME WW2 ODBC NOPROMPT="server=ABCDEFGPRD;driver=sql server;Trusted Connection=yes;Database=WW;" ;

This works fine. The original library is WW. The new test library I created is WW2. Both are shown as assigned/created.

Re: How to derive my proc sql connection string from libname connection string?

SASKiwi — Sat, 12 Sep 2015 02:02:05 GMT

In that case your same NOPROMPT string should work fine in a CONNECTION statement.