TNS: listener was not given SID. Access to Oracle 10g ODBC connection [message #473074] |
Wed, 25 August 2010 13:14 |
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 #473080 is a reply to message #473079] |
Wed, 25 August 2010 13:30 |
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 #473083 is a reply to message #473081] |
Wed, 25 August 2010 14:19 |
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.
|
|
|
|
|
|
|
|
|