topic Re: SAS doesn't allow me to pull out data from Oracle table in Administration and Deployment

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

Linlin — Thu, 08 Nov 2012 15:24:47 GMT

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

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

LinusH — Fri, 09 Nov 2012 09:56:40 GMT

Have you tried what the log sugests?

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

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

LinusH — Fri, 09 Nov 2012 10:01:12 GMT

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.

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

Linlin — Fri, 09 Nov 2012 14:41:38 GMT

Hi Linus,

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

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

TomKari — Sat, 10 Nov 2012 03:11:58 GMT

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

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

Linlin — Sat, 10 Nov 2012 20:54:42 GMT

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!

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

art297 — Sat, 10 Nov 2012 22:35:27 GMT

: 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

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

Linlin — Sun, 11 Nov 2012 01:54:38 GMT

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

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

Linlin — Wed, 14 Nov 2012 16:46:49 GMT

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.

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

Reeza — Wed, 14 Nov 2012 17:05:16 GMT

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

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

TomKari — Wed, 14 Nov 2012 18:26:20 GMT

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

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

Linlin — Wed, 14 Nov 2012 21:18:00 GMT

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

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

Rambill — Tue, 20 Nov 2012 16:11:05 GMT

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.

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

Sunshine_sas — Wed, 12 Dec 2012 12:48:58 GMT

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

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

Linlin — Wed, 12 Dec 2012 13:21:15 GMT

Hi Sunshine,

Thank you very much for your help! - Linlin