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

[learner_sas]

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.

[SASKiwi]

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.

[statistician13]

What happens if you try:

libname mydblib oledb dsn=prompt;

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

[learner_sas]

, 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.

[statistician13]

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;

[learner_sas]

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?

[statistician13 · Accepted solution]

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).

[learner_sas]

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."

[statistician13]

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.

[learner_sas]

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";

[SASKiwi]

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