proc sql connect to oledb

[BillJones]

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

[Kurt_Bremser]

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"

[BillJones]

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

[DBailey]

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

[BillJones]

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;

[Kurt_Bremser]

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.

[BillJones]

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

[DBailey]

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;