04-17-2018 08:51 AM
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>
Target Site: Assign
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.
04-18-2018 02:26 AM
04-18-2018 04:53 AM
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.
04-21-2018 09:26 PM
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,