topic sas Enterprise guide & Oracle in SAS Enterprise Guide

sas Enterprise guide & Oracle

Dmitriy1 — Tue, 17 Apr 2018 12:51:39 GMT

Hi!
In the SAS Manadment Console, when the server is configured, the Path field is filled in as follows
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = XXX.XXX.XXX.XXX) (PORT = 1521))) (CONNECT_DATA = (sid = orc1))).
When I try to view the library that accesses this server in SAS Enterprisse Guide, I get an error

Exception type: SAS.EG.SDS.SDSException
SAS Message: [Error] ORACLE connection error: ORA-12505: TNS: listener does not know of SID given in connect descriptor.

Raw Message: <? Xml version = '1.0'?> <Exceptions> <Exception> <SASMessage severity = 'Error'> ORACLE connection error: ORA-12505: SIN given in connect descriptor. / SASMessage> </ Exception> </ Exceptions>
Source: SAS.EG.SDS.Model
Target Site: Assign

Stack Trace:
   at SAS.EG.SDS.Model.Library.Assign ()
   at SAS.EG.SDS.Model.Library.PopulateDatasets (Collection & collection)
   at SAS.EG.SDS.Model.Broker.PopulateCollection (Collection collection)
   at SAS.EG.SDS.Model.Collection.DoPopulate ()
   at SAS.EG.SDS.Views.View.DoPopulate (DisplayOperation & op).

The SID field is taken from the tnsnames.ora file. oracle client works normally using the tnsnames.ora file.

Tell me what can be wrong.

Re: sas Enterprise guide & Oracle

LinusH — Wed, 18 Apr 2018 06:26:45 GMT

Do you have any Oracle connection that works?
If no, be sure that have performed all configuration steps for SAS/ACCESS to Oracle.
If yes, have an Oracle DBA look through your tnsnames file and libname options.

Re: sas Enterprise guide & Oracle

SASKiwi — Wed, 18 Apr 2018 08:53:22 GMT

I find it easier to test database connection strings in a LIBNAME statement first before setting up libraries in metadata. Have you tried that? From what I can see it looks like you are trying to bypass the TNSNAMES.ORA definition by defining everything in the connection string - is that correct? It might be easier to try a LIBNAME with the PATH= option to reference an existing TNSNAMES.ORA defined database.

Re: sas Enterprise guide & Oracle

AhmedAl_Attar — Sun, 22 Apr 2018 01:26:05 GMT

Hi @Dmitriy1 Try to Right Click on the Library/Libname in SAS Management Console and selecting "Display Libname Statement". You'll see the Generated Libname Statement does not match the syntax of your definition! Hence you are getting the Error!

While the SAS/ACCESS to ORACLE Documentation shows an example on how connects to Oracle without updating the TNSNAMES.ORA file

libname x oracle user=myusr1 pw=mypwd1 
  path="(DESCRIPTION= 
          (ADDRESS_LIST=
            (ADDRESS= (PROTOCOL=TCP)(HOST=pinkfloyd)(PORT=1521))
             )
              (CONNECT_DATA= 
       "  	     (SID=alien)
       "   )
       " )
       " ";

That doesn't mean, the SAS Management Console would translate the library definition the same way!

It's safer to modify the TNSNAMES.ora file accessible to your SAS Server(s) to include the Oracle connection settings.

Just my 2 cents,

Ahmed