BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
learner_sas
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
statistician13
Quartz | Level 8

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

10 REPLIES 10
SASKiwi
PROC Star

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
Quartz | Level 8

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
Quartz | Level 8

, 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
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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

statistician13
Quartz | Level 8

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
Quartz | Level 8

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

SASKiwi
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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