none
Error : ORA-12170: TNS:Connect timeout occurred

    Question

  • Hi, i am trying to use OLEDB source for Oracle and i am putting the correct server name, username and password but i am keep getting error:

    ORA-12170: TNS:Connect timeout occurred

    Tuesday, July 17, 2012 10:31 PM

Answers

  • 1. First you need to make sure you have created the linked server on local machine for the Oracle server you are connecting with correct username and password.

    2. Copy the code for that server from the tns file you have on Dev server and past it in tns file you have on Local machine. If you dont have tns file on local machine then just copy the whole file and put it on local machine.

    3. what are you using at host name? is it Ipaddress or  myserver.mycomapny.comIf possible use myserver.mycomapny.com

    Let us know if you are still facing the issue.

    • Marked as answer by JoJo_1 Friday, July 20, 2012 4:17 AM
    Thursday, July 19, 2012 3:52 AM

All replies

  • From https://forums.oracle.com/forums/thread.jspa?threadID=361885&tstart=0&start=15

    I was tring to connect to Oracle from SSIS (SQL Server Integration Services) and had this error ERROR ORA-12170: TNS:Connect timeout occurred.

    This is what fixed my problem. I located my tansnames.ora file and made the change as highlighted below:

    MyDatabaseAlias =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mycompany.com)(PORT = 1531))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MyServiceName.mycompany.com)
    )
    )

    In nutshell, I fully qualified the SERVICE_NAME from 

    (SERVICE_NAME = MyServiceName) to (SERVICE_NAME = MyServiceName.mycompany.com)


    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Tuesday, July 17, 2012 11:57 PM
  • Thanks for your reply, i have many service_names in tns file so didnt know which one to use. But thats not a problem now i can connect but i can connect only in 2005. i cant connect in 2008.

    2008 is on local machine and 2005 is on Dev Server.

    Wednesday, July 18, 2012 6:44 PM
  • 1. First you need to make sure you have created the linked server on local machine for the Oracle server you are connecting with correct username and password.

    2. Copy the code for that server from the tns file you have on Dev server and past it in tns file you have on Local machine. If you dont have tns file on local machine then just copy the whole file and put it on local machine.

    3. what are you using at host name? is it Ipaddress or  myserver.mycomapny.comIf possible use myserver.mycomapny.com

    Let us know if you are still facing the issue.

    • Marked as answer by JoJo_1 Friday, July 20, 2012 4:17 AM
    Thursday, July 19, 2012 3:52 AM
  • Thanks SqlRockss, I didnt have tns file updated on local machine.
    Friday, July 20, 2012 4:18 AM