Architecting, installing and maintaining your SAS environment

SAS doesn't allow me to pull out data from Oracle table

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

SAS doesn't allow me to pull out data from Oracle table

Hi All,

I had newer 9.3 SAS installed last week. now I can't  pull out data from Oracle table. No problem for my sas code because I have run the same code thousands of times and I have all the required products licensed.  When I try to run the code a message came up: sas.exe has stopped working. When I try to run through EG, I got below message. Has anyone had the same problem? I have been working with SAS to resolve the problem since Monday. Today is the fourth day that I can't work.Smiley Sad:smileycry:. Thank you!

ERROR:  An
exception has been encountered.

Please contact technical support and provide them with
the 

       following
traceback information:

     The SAS task name is [OBJECT_EXECUTIVE]

  Exception occurred at (3F350BF5)

  Task Traceback

  Address 
Frame     (DBGHELP API Version 4.0
rev 5)

  3F350BF5
0351F35C  sasioora:tkvercn1+0xFBB5

  62A2FB57
0351F398  sasxdbi:tkvercn1+0xEB17

  675611A0
0351F5EC  sasyhl:tkvercn1+0x160

  6778D6C2  0351F758  sasyh:tkvercn1+0x2C682

  6778C976
0351F8F8  sasyh:tkvercn1+0x2B936
028CB91C  0351FAA8  sasxshel:tkvercn1+0x1A8DC 028B26EA  0351FB34
sasxshel:tkvercn1+0x16AA

  028B15E2
0351FB74  sasxshel:tkvercn1+0x5A2
028CF04D  0351FC2C  sasxshel:tkvercn1+0x1E00D

  028F5C10
0351FDF0
sasxshel:tkvercn1+0x44BD0 028F990D
0351FE74
sasxshel:tkvercn1+0x488CD 028F3D0F
0351FEAC
sasxshel:tkvercn1+0x42CCF

  55861351
0351FF28  sasxobj:tkvercn1+0x311

  01C53254
0351FF74  sashost:Main+0xD424

  01D47038
0351FF88  sashost:Main+0x101208
7546ED6C  0351FF94  kernel32:BaseThreadInitThunk+0x12 76FF377B  0351FFD4
ntdll:RtlInitializeExceptionChain+0xEF

  2 The SAS System                  14:50 Tuesday, November 6,
2012

76FF374E
0351FFEC  ntdll:RtlInitializeExceptionChain+0xC2


Accepted Solutions
Solution
‎12-12-2012 07:48 AM
SAS Employee
Posts: 29

Re: SAS doesn't allow me to pull out data from Oracle table

Hi there,

This what happened......

The original order ###### did NOT have the Oracle product in the Depot (this can happen if depots are subsetted and do not include the product in question). I first checked the soi.html file to make sure SAS/Access to Oracle was part of the products. It was.....the soi.html file is located in <SAS Software Depot\install_doc\###### (where ###### is the order number)\ and then open the soi.html file.

I then looked at the SAS Installation Data file that was being used - to make sure that I saw these items:

   SAS/ACCESS Interface to OLE DB
   *PRODNUM194 = ORACLEBNDL SAS/ACCESS ORACLE;
   and what the expire date was for 'PRODNUM194' which was fine

Next I went to the SAS Software Depot\Products directory and looked for the 12byte for SAS\Access to Oracle - ORACLEBNDL and it was not there

   For example, it would look something like this:
   C:\SAS Software Depot\products\oraclebndl__93210__win__xx__sp0__1

I then did the same thing to the other depot, and it contained the oraclebndl in the products directory, the SAS Installation Data file was correct and so was the Software Order email.

In conclusion the SAS/ACCESS to Oracle product was not part of the depot they were installing from.

Thank you and have a wonderful day,

Sunshine

View solution in original post


All Replies
Super User
Posts: 5,441

Re: SAS doesn't allow me to pull out data from Oracle table

Have you tried what the log sugests?

'Please contact technical support and provide them with...' Smiley Wink

Data never sleeps
Super Contributor
Posts: 1,636

Re: SAS doesn't allow me to pull out data from Oracle table

Hi Linus,

Thank you for your reply! Yes, I have contacted SAS and scheduled a webex today.  - Linlin

Super User
Posts: 5,441

Re: SAS doesn't allow me to pull out data from Oracle table

What OS/level do you use?

Oracle version?

What SAS/ACCESS product do you use (Oracle, ODBC...).

I assume you have checked system requirements for your combination of SW.

Data never sleeps
PROC Star
Posts: 1,167

Re: SAS doesn't allow me to pull out data from Oracle table

WOW! You REALLY blew up SAS! They're going to cancel your license!!

Seriously, assuming it's SAS/Access for Oracle that's choking, could you maybe use ODBC as a workaround until SAS gets it fixed? Good luck; I've never seen one like this in SAS/Access. Be sure to let us know what happened.

Best,

  Tom

Super Contributor
Posts: 1,636

Re: SAS doesn't allow me to pull out data from Oracle table

Hi Tom,

Thank you for your message. Yesterday afternoon two ladies from SAS looked through my computer from 2:30pm to 5:00pm. Her final words were "everything seems normal, I have no ideas what is going on here." . I emailed them all the files they have asked. The plan was that they would go through all the files I sent on Monday to identify the problem.  We will continue to work on Tuesday because Monday is a holiday for me. I will let you know once we identify the problem.

Thanks!

PROC Star
Posts: 7,492

Re: SAS doesn't allow me to pull out data from Oracle table

: Do you have a length statement in your code?  I ask because I just noticed the following note on the SAS site:

http://support.sas.com/kb/45/987.html

Super Contributor
Posts: 1,636

Re: SAS doesn't allow me to pull out data from Oracle table

Hi Art,

The problem is with Oracle. We tried libname statement, it did not work;Then we tried pass through it didn't work either.

Thanks - Linlin

Super Contributor
Posts: 1,636

Re: SAS doesn't allow me to pull out data from Oracle table

Hi Tom,

My problem has not been resolved yet. I got my original disk back, at least I am able to work now. Below are emails between SAS and our IT guy.

from SAS

The error seems to be isolated to the sasioora.dll file, which is used by SAS/ACCESS to Oracle.

Do you have this same setup on another machine already that is working? If yes, please try the following:

1. On your machine, Close SAS.
2. On your machine rename C:\Program Files\SASHome\SASFoundation\9.3\access\sasexe\sasioora.dll to sasioora.old.
If you are prompted with are you sure, select Yes.

3. Go to the working machine and copy the C:\Program Files\SASHome\SASFoundation\9.3\access\sasexe\sasioora.dll
file to your existing machine in the same directory C:\Program Files\SASHome\SASFoundation\9.3\access\sasexe\sasioora.dll
4. Start your SAS and test your code. If its fixed great! If not please proceed to the next set of instructions.

If you do NOT have a already working machine, Start another installation, run setup.exe just as if a new install was being done,
but treat it as a product add-on.....as if you are adding SAS/Access to Oracle to the existing installation. To do these please perform the following steps:

1. Close SAS
2. Highlight setup.exe and select Run as Administrator
3. Select Install SAS Software
4. Select Install SAS Foundation and related software.

   * Please note, when you run the setup again, it might go into Update Mode, you will want to allow it to do this.
   It might fix the SAS/ACCESS to Oracle problem during the update.

If the install does not go into update mode, select Install SAS Foundation and related software,
check SAS Foundation only and then when the SAS Foundation product list comes up you will check SAS/ACCESS to oracle.

5. Then complete the installation.
6. Then try to rep the problem again.

from our IT guy:

All,
We have this version installed on two machines, neither one works. 
We tried to follow your instructions on the second machine (second machine is 64-bit, but only has one version of Oracle client). 
Did not get a list to check SAS/ACCESS to Oracle.  Instead it did a full install. 
Now I am getting a different error (see attached file).

At this point we are talking about getting another copy of SAS and trying the install from the beginning. 
When we get it working on the second machine we will then install on Yulin's. 
This could take several days or maybe a week.

Super User
Posts: 19,870

Re: SAS doesn't allow me to pull out data from Oracle table

Did not get a list to check SAS/ACCESS to Oracle.  Instead it did a full install.

That's an issue.

Are they installing from a disk or SAS Depot?

Try installing from a SAS Depot and ensure Access is installed (it is a separate click) that ISN'T default as far as I've seen (may depend on your disks).

Secondly, reset your DSN Connection, ie delete and recreate it. Creating a depot takes a while (few hours) but the install over the network drive takes less than an hour after that I've found

PROC Star
Posts: 1,167

Re: SAS doesn't allow me to pull out data from Oracle table

Hi, Linlin

That's awful! I feel so sorry for you; I've had similar situations over the years, and I know how frustrating it is. Hang in there, it will get solved, and you'll be back in business.

Dumb question; can you use SQL*Plus on the troublesome machine to connect with Oracle and retrieve data>

Good luck!

  Tom

Super Contributor
Posts: 1,636

Re: SAS doesn't allow me to pull out data from Oracle table

Hi Reeze and Tom,

Thank you for your messages! I have forward Reeze's message to our IT guy. I think they already tried the SQL*Plus on Friday.

Linlin

Occasional Contributor
Posts: 7

Re: SAS doesn't allow me to pull out data from Oracle table

Not sure Linlin.  If you have tried this before. If i am there in your position, I will try to troubleshoot in the following order.

After ensuring SAS Access to Oracle is installed by confirming through Deployment Summary file.

1. Check if you are able to access Oracle Database directly from the server using Oracle Client installed on the server. SAS actually uses this oracle client to connect to the database.

2. Ensure on the 64 Bit server, 64 bit oracle client is installed and the SAS/Access interface to Oracle product is also of 64 BIt version.

3. On Linux, we setup some standard environment variables pointing to Oracle Client path in sasenv_local. You may need to something similar to that in Windows environement.

Solution
‎12-12-2012 07:48 AM
SAS Employee
Posts: 29

Re: SAS doesn't allow me to pull out data from Oracle table

Hi there,

This what happened......

The original order ###### did NOT have the Oracle product in the Depot (this can happen if depots are subsetted and do not include the product in question). I first checked the soi.html file to make sure SAS/Access to Oracle was part of the products. It was.....the soi.html file is located in <SAS Software Depot\install_doc\###### (where ###### is the order number)\ and then open the soi.html file.

I then looked at the SAS Installation Data file that was being used - to make sure that I saw these items:

   SAS/ACCESS Interface to OLE DB
   *PRODNUM194 = ORACLEBNDL SAS/ACCESS ORACLE;
   and what the expire date was for 'PRODNUM194' which was fine

Next I went to the SAS Software Depot\Products directory and looked for the 12byte for SAS\Access to Oracle - ORACLEBNDL and it was not there

   For example, it would look something like this:
   C:\SAS Software Depot\products\oraclebndl__93210__win__xx__sp0__1

I then did the same thing to the other depot, and it contained the oraclebndl in the products directory, the SAS Installation Data file was correct and so was the Software Order email.

In conclusion the SAS/ACCESS to Oracle product was not part of the depot they were installing from.

Thank you and have a wonderful day,

Sunshine

Super Contributor
Posts: 1,636

Re: SAS doesn't allow me to pull out data from Oracle table

Posted in reply to Sunshine_sas

Hi Sunshine,

Thank you very much for your help!Smiley Happy - Linlin

🔒 This topic is solved and locked.

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

Discussion stats
  • 14 replies
  • 1133 views
  • 0 likes
  • 7 in conversation