Home » RDBMS Server » Networking and Gateways » TNS: listener was not given SID. Access to Oracle 10g ODBC connection (Oracle 10g ODBC driver, Windows XP Proff, Access 2003)
TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473074] Wed, 25 August 2010 13:14 Go to next message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
ODBC--call failed.
TNS:listener was not given the SID in CONNECT_DATA (#12504)

TNSname sets the SID. SQL*Plus is able to connect from the client Windows XP Proff using the same name. Access, on the same client, uses the same Oracle driver to connect successfully to other Oracle databases. These other databases show equal tnsname (different name and SID...)

ODBC Data Source Administrator configures the database and the test connection is successful.

Cannot set up a link from Access to the Oracle database. At some point, I have created a new Access database and it did let me connect, view the list of tables, and set up links to several tables, but the next day I could not open any of the tables and the complain was: ODBC--connection to 'MYDB' failed.

It seems that SQL*Plus passes correctly the SID from the TNSname but ODBC, when invoked from Access, does not. Yet, ODBC does it right for other databases.

Can anyone shed some light? HELP!



Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473075 is a reply to message #473074] Wed, 25 August 2010 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59982
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
 *Cause:  The SID was missing from the CONNECT_DATA.
 *Action: Check that the connect descriptor corresponding to the service
 name in TNSNAMES.ORA has an SID component in the CONNECT_DATA.

Regards
Michel
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473077 is a reply to message #473074] Wed, 25 August 2010 13:21 Go to previous messageGo to next message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
The TNSnames.ora shows and it does not work:
POC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 175.25.115.105)
(PORT = 1533))
(CONNECT_DATA =
(SID = FGIGT))
)

EQS =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 175.26.75.35)
(PORT = 1521))
(CONNECT_DATA =
(SID = BGIGT))
)
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473079 is a reply to message #473077] Wed, 25 August 2010 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59982
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you are not using the one you think you use.

Regards
Michel
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473080 is a reply to message #473079] Wed, 25 August 2010 13:30 Go to previous messageGo to next message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
Changing the SID in the one I think I use causes SQL*Plus to loose the ability to connect on one hand and, connecting to the other database specified in the same TNSnames.ora (which is the only one on the system) works. Adding SERVICE_NAME causes a different error in ODBC. I conclude that it is the same file I think it is??? Could it go somewhere else? The two Oracle system variables point to the right directories.
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473081 is a reply to message #473080] Wed, 25 August 2010 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 23140
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

blah, blah, error, then do blah, blah & get different error, then this works but fails doing something different.
Tell me how to fix this, please.

It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473083 is a reply to message #473081] Wed, 25 August 2010 14:19 Go to previous messageGo to next message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
1. I installed Oracle 10.2.0.1 ODBC driver with Oracle Client, setup a TNSNAMES.ORA file with the following descriptor in c:\oracle\hs\admin:
POC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 175.25.115.105)
(PORT = 1533))
(CONNECT_DATA =
(SID = FGIGT))
)
added a User DSN in ODBC Administrator named POC using the new Oracle driver and tested the connection successfully, opened an Access database and linked a table from the named database. Failed with the error: TNS:listener was not given the SID in CONNECT_DATA(#12504)

2. Created a new Access database and linked tables from the database in question successfully. The next day I tried to view data in a linked table and it failed: ODBC--connection to 'POC' failed.
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473085 is a reply to message #473083] Wed, 25 August 2010 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 23140
Registered: January 2009
Senior Member
12504, 00000, "TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"
// *Cause:  The listener was not configured with a default service and
//  SERVICE_NAME was missing from the CONNECT_DATA received by the listener.
// *Action: Possible solutions are:
//  - Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service
//    name. Reload the listener parameter file using reload [<listener_name>].
//    This should be done by the LISTENER administrator.
//  - If using a service name, Check that the connect descriptor corresponding
//    to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component
//    in the CONNECT_DATA.
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473086 is a reply to message #473085] Wed, 25 August 2010 14:50 Go to previous messageGo to next message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
I have no immediate access to the LISTENER administrator. Is it a specific
problem to ODBC or general to the TNSNAMES.ORA and its users: ODBC and SQL*Plus?
I am able to connect through SQL*Plus using the same descriptor with the SID.
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473095 is a reply to message #473086] Wed, 25 August 2010 17:15 Go to previous messageGo to next message
BlackSwan
Messages: 23140
Registered: January 2009
Senior Member
my red car goes.
my blue car won't go.
tell me how to make my car go.
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473156 is a reply to message #473095] Thu, 26 August 2010 06:28 Go to previous messageGo to next message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
Failed with the error: TNS:listener was not given the SID in CONNECT_DATA(#12504)
not "TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473165 is a reply to message #473156] Thu, 26 August 2010 08:22 Go to previous messageGo to next message
BlackSwan
Messages: 23140
Registered: January 2009
Senior Member
Yes, you have an error.

open URL below
http://www.orafaq.com/forum/t/88153/0/

Scroll down to section labeled as below

How to format your post?

Click on "How to format your post?"

then READ & DO as it says!
Re: TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473166 is a reply to message #473165] Thu, 26 August 2010 08:38 Go to previous message
shimonsbd
Messages: 7
Registered: August 2010
Location: Ontario
Junior Member
POC =
  (DESCRIPTION =
      (ADDRESS = 
         (PROTOCOL = TCP)
         (HOST = 175.25.115.105)
         (PORT = 1533))
      (CONNECT_DATA =
         (SID = FGIGT))
  )

EQS =
  (DESCRIPTION =
      (ADDRESS = 
         (PROTOCOL = TCP)
         (HOST = 175.26.75.35)
         (PORT = 1521))
      (CONNECT_DATA =
         (SID = BGIGT))
  )
Previous Topic: remote access
Next Topic: Listener Crashes
Goto Forum:
  


Current Time: Thu Dec 18 11:24:34 CST 2014

Total time taken to generate the page: 0.13666 seconds