topic Re: proc sql connect to oledb in SAS Procedures

proc sql connect to oledb

BillJones — Thu, 18 Dec 2014 09:37:34 GMT

I'm trying to connect directly to an oledb database through proc sql. I have a libname statement that works. However, when I try to use the same syntax in the sql, it doesn't work. Please see the log file below. Any suggestions would be greatly appreciated.

Thanks very much,

Bill

log file:

650 dm 'clear log';

651

652 options obs=max mprint=1;

679

680 libname SPINNDW oledb provider=sqloledb schema=dbo user=&mkt01_user.

680 ! password=&mkt01_pass. datasource=MKT01 properties=(XXXXXXXXXXXXXXXXX=XXXXXXXXXX)

680 ! access=readonly;

NOTE: Libref SPINNDW was successfully assigned as follows:

Engine: OLEDB

Physical Name: sqloledb

681 /*************************

682 Create rolled-up table for inforce premium & inforce count

683 *************************/

684 685

686 proc sql;

687 connect to oledb as spinndw (init_string="oledb provider=sqloledb schema=dbo

687 ! user=&mkt01_user. password=&mkt01_pass. datasource=MKT01 properties=('initial

687 ! catalog'=prodCSE_dw) access=readonly");

ERROR: Error trying to establish connection: Unable to Initialize: Invalid authorization

specification: Invalid connection string attribute

688 create table policy_inforce_amt as select * from connection to spinndw

689 (select

690 policy_inforce_amt as select

691 pol.reportperiod,

692 pol.policyref,

693 pol.annualstatementlinecd,

694 sum(pol.inforceamt) as ye_inforceamt

695 from

696 spinndw.policysummarystats pol

697 where

698 pol.reportperiod in ('201012','201112','201212','201312','201406')

699 and pol.feecd is null

700 group by

701 pol.reportperiod,

702 pol.policyref,

703 pol.annualstatementlinecd

704 order by

705 pol.policyref,

706 pol.reportperiod

707 );

ERROR: The SPINNDW engine cannot be found.

ERROR: A Connection to the spinndw DBMS is not currently supported, or is not installed at

your site.

708 disconnect from spinndw;

ERROR: Connection to the spinndw DBMS does not exist.

709 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Re: proc sql connect to oledb

Kurt_Bremser — Thu, 18 Dec 2014 10:13:55 GMT

I t looks like you have the wrong syntax for the init string.

Try with

init_string="Provider=SQLOLEDB;Password=&mkt01_pass.;User ID=&mkt01_user.;Initial Catalog=prodCSE_dw;Data Source=MKT01"

Re: proc sql connect to oledb

DBailey — Thu, 18 Dec 2014 12:44:42 GMT

If you're using the current version of SAS, you might could use the new connect as syntax.

libname SPINNDW oledb provider=sqloledb schema=dbo user=&mkt01_user. password=&mkt01_pass. datasource=MKT01 properties=(XXXXXXXXXXXXXXXXX=XXXXXXXXXX) access=readonly;

proc sql;

connect using SPINNDW as cSPIN;

disconnect from cSPIN;

quit;

Re: proc sql connect to oledb

BillJones — Thu, 18 Dec 2014 21:54:27 GMT

Kurt,

Thanks for the code. I tried your suggestion and it's running!

DBailey,

Thanks for your thoughts and alternative method. I am using 9.4. However, when I try use your syntax, I get a data link popup. Is there a way to get rid of the popup?

Thanks again,

Bill

Re: proc sql connect to oledb

DBailey — Thu, 18 Dec 2014 22:36:27 GMT

sorry...it's new to me as well.

Re: proc sql connect to oledb

BillJones — Thu, 18 Dec 2014 23:59:12 GMT

DBailey,

No problem! Thanks again for the code.

Here's another question for the group. Is there any disadvantage to using a libname statement and then referencing it in your proc sql?

For example,

libname SPINNDW oledb provider=sqloledb schema=dbo user=&mkt01_user. password=&mkt01_pass. datasource=MKT01 properties=('initial catalog'=prodCSE_dw) access=readonly;

proc sql;

create table policy_inforce_amt as select

pol.reportperiod,

pol.policyref,

pol.annualstatementlinecd,

sum(pol.inforceamt) as ye_inforceamt

from

spinndw.policysummarystats pol

where

pol.reportperiod in ('201012','201112','201212','201312','201408')

and pol.feecd is null

group by

pol.reportperiod,

pol.policyref,

pol.annualstatementlinecd

order by

pol.policyref,

pol.reportperiod

;

quit;

Re: proc sql connect to oledb

Kurt_Bremser — Fri, 19 Dec 2014 06:26:46 GMT

Often you can get a clue out of the internet by searching for the text of the error message.

I pasted

ERROR: Error trying to establish connection: Unable to Initialize: Invalid authorization

specification: Invalid connection string attribute

into google search and quite quickly came across a useful page.

Re: proc sql connect to oledb

BillJones — Fri, 19 Dec 2014 20:31:52 GMT

Kurt,

Thanks for the tip. I always search on google, sas.com, and stackoverflow.com before creating a post. However, in this case, it appears that I missed some useful information. I'll do a more diligent search next time.

-Bill