Oracle to SQL Server Connection

[Rikesh_p]

Hi,

A little while ago I followed the article below to successfully create an Oracle to SQL Server connection.

http://www.dbasupport.com/oracle/ora...ection01.shtml

I know have to create a second connection (on the same server). I followed the steps again, but am not able to select records.
The error I get is:
ora-12154: TNS: could not resolve connect identifier

I have checked my Listener.ora and tnsnames.ora files and they are correct. I can start the new listener and tnsping to the new connection.

I also have 2 init......ora files (initSQLSERV.ora & initNEW.ora)
Can there be 2 init.....ora files?

I also have 2 database links.

My DSN are also set up as recommeneded by the article. And get a successful connection.

Any help on having multiple Oracle to SQL Server connections would be helpful.
Many thanks in advance.

[LKBrwn_DBA]

Blah, blah, blah...
I also have 2 init......ora files (initSQLSERV.ora & initNEW.ora)
Can there be 2 init.....ora files?

Only if you start the database with the CORRECT one, for example: STARTUP PFILE=init????.ora

It is best practice to start the database using either the default init{SID}.ora or spfile{SID}.ora files.

[Rikesh_p]

Thanks for your response.

I believe that the Oracle database uses the normal init.ora file, however the initSQLSERV and initNEW files I have created are not related as they are situated in an entire different location.

Otherwise the issue has now been resolved. There was a error in one of my database links. It was not connecting to the correct user that was stated in the System DSN.

[PAVB]

ora-12154: TNS: could not resolve connect identifier

It looks to me this is a TNSNAMES.ora issue.

Be sure you have a single TNSNAMES.ora file - rename all others - and be sure you have a valid tnsnames entry for target Oracle database.

To test IP connectivity to target host do this...

ping machine_name used in tnsnames.ora
If it doesn't work try actual IP Address then use IP Address in tnsnames entry.

To test tns connectivity to target instance do this...

1- Set ORACLE_HOME
2- Set ORACLE_SID
3- tnsping ALIAS name used in tnsnames.ora file

Hope this helps.

[jpsaccount]

I noticed that using "Linked Server" which connects from the Microsoft end is an easier to setup and use link. However, in this case the link is from the Sql server end and you will be able to query oracle tables there and not vice versa.

I am an Oracle fan, but think about it, why make all this fuss of manual changes in listener/tnsnames and activation of HS (Heterogenous Services) features instead of doing it at the click of a button. My guess is that they should be including this in the latest OEM version in lines similar to the Linked Server in Microsoft.