topic Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application in SAS Enterprise Guide

SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

learner_sas — Wed, 11 Feb 2015 16:30:20 GMT

Hello Everybody,

I am trying to connect my SAS with Oracle. I have SAS license for PC and I have license for "SAS/ACCESS Interface to OLE DB" but not for "---SAS/ACCESS Interface to ODBC".

I can connect to my oracle database using file>>Open>>OLEDB>>Data Link Properties>>Microsoft OLE DB Provider for ODBC Drivers(provider)>>Data source name (dsnname),username(testone),password(pwd123).

Now I am trying to join using following lib name

libname mydblib oledb user=testone password=pwd123 datasource=dsnname provider='OraOLEDB.Oracle' ; and got following as error "ERROR: Error trying to establish connection: CreateDBInstance failed.: %1 is not a valid Win32 application."

I also tried

libname dbMYDB oledb init_string="Provider=OraOLEDB.Oracle;SERVER='test.server';

Data Source=dsnname;USERID=testone;PASSWORD=pwd123;PORT=1521";

But got same error. I am wondering where am I doing the mistake.

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

SASKiwi — Wed, 11 Feb 2015 18:41:14 GMT

Judging from your error you could have a 32-bit - 64-bit incompatibility. I suspect your installed SAS version is 32-bit but your OLEDB software is 64-bit. Look in the SAS Help menu to confirm your SAS bitness. If it is 32-bit then your OLEDB software must be 32-bit also.

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

statistician13 — Wed, 11 Feb 2015 19:16:04 GMT

What happens if you try:

libname mydblib oledb dsn=prompt;

and then manually enter the connection information when prompted? Does it work then?

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

learner_sas — Wed, 11 Feb 2015 19:47:18 GMT

, I think my SAS is 64 bit because if I go to

Start ► All Programs ► SAS ► SAS 9.3. A menu displays:SAS 9.3(English). Which is correct as of 32374 - Determining whether you have SAS® 9.2 32-bit or SAS 9.2 x64 installed on Windows x64.

. Do you know how to make sure your OLE DB driver is 64 bits?

#statistician13 it gives me following error if I run libname mydblib oledb dsn=prompt;

ERROR: Error trying to establish connection: Unable to prompt for connection information.

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

statistician13 — Wed, 11 Feb 2015 19:55:21 GMT

Hmmmm. It's unusual that you wouldn't receive the OLE prompt. Are you sure you have SAS/ACCESS to OLE DB? Do you see ACCESS to OLE listed if you run:

proc setinit;

run;

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

learner_sas — Wed, 11 Feb 2015 20:00:08 GMT

I think so below is my Proc Setinit;run;

Operating System: WX64_WKS.

Product expiration dates:

---Base SAS Software 29JUN2015

---SAS/STAT 29JUN2015

---SAS/GRAPH 29JUN2015

---SAS Enterprise Miner 29JUN2015

---SAS Enterprise Guide 29JUN2015

---SAS/ACCESS Interface to PC Files 29JUN2015

---SAS/ACCESS Interface to OLE DB 29JUN2015

---SAS Enterprise Miner for Desktop 29JUN2015

---SAS Workspace Server for Local Access 29JUN2015

---SAS Add-in for Microsoft Excel 29JUN2015

Do you think I need more?

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

statistician13 — Wed, 11 Feb 2015 20:08:18 GMT

No. You clearly have it. It seems like SAS is unable to find your OLE drivers. Let's try this:

1. Open up a new text file in notepad or wordpad or any other text editor. Don't type anything in it, but just save a the blank file as test.udl, making sure you use the .udl connection.
2. Double click the udl file. You should be prompted for your connection information. Go ahead and enter is all it and test the connection using the the "test connection" button to make sure it succeeds.
3. submit the following SAS code:

libname mydblib oledb UDL_FILE="D:\test.udl";

but you will change "D:\test.udl" to the location of the udl file you created in step 1

See if that works and report back if any of the above fails (or succeeds).

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

learner_sas — Wed, 11 Feb 2015 20:19:41 GMT

Done. I am able to test connection and it connection successful.

when I ran lib name statement I got error as

"ERROR: Error trying to establish connection: The file is not a valid compound file."

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

SASKiwi — Wed, 11 Feb 2015 20:47:31 GMT

Check in Windows Control Panel in Programs - this will probably tell you if OLEDB is 64-bit or not.

Also check out this note, which is a bit old but still relevant: http://support.sas.com/kb/8/637.html

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

statistician13 — Wed, 11 Feb 2015 21:02:55 GMT

OK. I'm wondering if you can connect to any other datasources through OLE. Can you cannot to something besides oracle using OLE? I wonder if you have a 32-bit version of the oracle OLE driver. I'd recommend downloading and installing the oracle 64-bit driver and see if that helps: 64-bit Oracle Data Access Components (ODAC) for Windows.

Re: SAS OLE DB connection using libname statement,error %1 is not a valid Win32 application

learner_sas — Fri, 20 Feb 2015 17:12:42 GMT

Hello @statistician13

I figured out how to do it. For some reason it did not work on my EG then I went to Base SAS and ran process exactly as you said it ran . and copied to EG and worked. Following should work of everybody if they have problem. Thank you everybody fo the help.

libname mylib oledb user=username password=password datasource=datasourcename provider="MSDASQL.1";