DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010

    Oracle to SQL Server Connection


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


    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.

  2. #2
    Join Date
    Jul 2002
    Lake Worth, FL

    Cool Best practice

    Quote Originally Posted by Rikesh_p View Post
    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Aug 2010
    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.

  4. #4
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    Quote Originally Posted by Rikesh_p View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Aug 2010

    Connectivity from Sql server side easier

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.