tnsnames.ora and DSN in stage

irshanuddin · Post by **irshanuddin** » Fri Nov 04, 2011 1:48 pm

I am upgrading from 8.1 to 8.5 and am in the process of setting up all the Database connections. This question relates to the Oracle piece of it.
We have Oracle 11g as our database and we have installed the client on the 8.5 server which is on Red Hat.
We changed platforms from Windows 2003 to Linux.
I copied my tnsnames.ora file from the Windows server to the Linux server.
There basically two minor issues that I am seeing after this setup is done.

a) My drop-down for a DSN in my Import Table Definition lists ALL the lines of my TNSnames.ora file.
For example, this is what my DSN drop-down looks like:
EDW.xxxxx.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-xxx-xxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = EDW2)
)
)
Rather than just say EDW...
Do I need to change something in my tnsnames.ora?

b)I'm having to fully qualify my connections when I run the jobs, so where previously on Windows I was able to just say "EDW for DSN, I'm now having to provide EDW.xxxxx.com for the connect identifier, otherwise it fails with a TNS:Cannot resolve connect identifier specified.

Is there a way around this?

Thanks!

lstsaur · Post by **lstsaur** » Fri Nov 04, 2011 2:22 pm

To define a DSN, you put all required information in both .odbc.ini and uvodbc.config (project level) files. Why tnsnames.ora file is used to define the DSN?

irshanuddin · Post by **irshanuddin** » Fri Nov 04, 2011 2:26 pm

It was my understanding that the tns names file can be used in case of Oracle conncetions, and it hasnt failed me. The connections work fine and jobs work great. It's just these minor annoyances that I am trying to fix.

lstsaur · Post by **lstsaur** » Fri Nov 04, 2011 5:14 pm

Ok, you can just remove the .xxxxx.com part from the EDW.xxxxx.COM(database alias) of the tnsnames.ora file. Also ensure to comment out the parameter NAMES.DEFAULT_DOMAIN = xxxxx.com if it is specified in the SQLNET.ora file. Then, you should be able just type the EDW as the connect identifier and get connected to the database.