CODEWITHSUNDEEP
oracle-databaseodbc
Jamie Dixon
Jamie Dixon

Reputation: 4302

Connection to an Oracle Database

I recently picked up a project where I need to build some SSIS packages that extract data from a remote Oracle database and load the data into a Sql Server database. The Oracle database is an old 32-bit Oracle 8i database. The Sql Server is 64-bit Sql Server 2008R2.

I can connect to the Oracle database from an old Win XP workstation no problem. When I try and connect from the Sql Server, I can't set a handshake.

I installed The Oracle Client 11g on the Sql Server box, copied the tnsnames file from the Install\Network\Admin\Sample to Install\Network\Admin and added the entry that worked on the XP workstation. When I try and create an ODBC connection using the Oracle ODBC Drviver, I get:

*Unable to connect Sql State=08004 ORA-12154: TNS:could not resolve the connect identifier specified.*1

My guess is that the driver can't find the TNS file. Is there anything else I have to configure? I checked the exception code via duckduckgo and I could not find anything.

Thanks in advance.

Upvotes: 0

Views: 23559

Answers (5)

Junior Mayhe
Junior Mayhe

Reputation: 16411

Of course you must check your TNSNAMES.ora at

C:\your_oracle_folder\app\oracle\product\11.2.0\server\network\ADMIN\TNSNAMES.ora

You should see at the top what is your system name. In my case Oracle 11g Express is "XE". Then configure your ODBC 32 or 64 bits depending on your platform:

enter image description here

Upvotes: 0

Mikey
Mikey

Reputation: 1

I had a similar problem where I was getting garbage in the TNS Services name list and testing the connection took forever. I found this was caused be a typo in the system environment variable setting for the TNS_ADMIN variable, where I had a space after the directory eg c:\oracle\instantclient_ (where _ is a space) Removed and corrected the list garbage and connected straight away.

Hope this helps.

Upvotes: 0

Preeti Joshi
Preeti Joshi

Reputation: 989

This might help someone stuck for "identifier not resolved issue": If you have already checked the environment variables : PATH, ORACLE_HOME, TNS_ADMIN, please give a close look to tnsnames.ora entry for the given dsn. I had not given proper indentation and it was failing because of that!

Here is the entry with accurate indentation:

<dsn> =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <server>)(PORT = <port>))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <servicename>) 
    )
  )

Upvotes: 0

Jamie Dixon
Jamie Dixon

Reputation: 4302

OK, I found it I had to go into the Environment variables and define the ORACLE_HOME path. Once I did that, it connected.

This post got me on the right track.

Thanks

Upvotes: 1

Ponting
Ponting

Reputation: 2246

You have to use both of either 64 bit or 32 bit as your machine configuration. Make sure your server configuration is correct.

Upvotes: 0

Related Questions