Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-11-2015 03:08 PM
Contributor
Posts: 57

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

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

View solution in original post


All Replies
Super User
Posts: 3,233

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

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.

Contributor
Posts: 57

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

What happens if you try:

libname mydblib oledb dsn=prompt;

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

Frequent Contributor
Posts: 89

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

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

Start ► All Programs ► SAS ► SAS 9.3. A menu displaysSmiley FrustratedAS 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.

Contributor
Posts: 57

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

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;

Frequent Contributor
Posts: 89

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

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?

Solution
‎02-11-2015 03:08 PM
Contributor
Posts: 57

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

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

Frequent Contributor
Posts: 89

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

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

test_connection.PNG

when I ran lib name statement I got error as

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

Contributor
Posts: 57

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

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.

Frequent Contributor
Posts: 89

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

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 Smiley Happy . 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";

Super User
Posts: 3,233

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1585 views
  • 1 like
  • 3 in conversation