locked
Connecting to Oracle Data Source RRS feed

  • Question

  • Hello all,

    I'm having some issues with pulling data from Oracle 10g.

    My server is a Enterprise License on a 64x platform.

    I installed the Oracle 9i client tools and I'm getting the famous ORA-12154 TNS: Could not resolve service name.

    This is an interesting issue.

    I can connect to my data source using SQL Plus, an Oracle client tool.

    If I run a tnsping to QV1.WORLD, I get:

    V:\>tnsping QV1.WORLD

    TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 10-FEB-20
    10 11:07:49

    Copyright (c) 1997 Oracle Corporation. All rights reserved.

    Used parameter files:
    D:\DevSuiteHome_1\network\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP
    .WORLD) (PROTOCOL = TCP) (HOST = sapdbq01) (PORT = 1527))) (CONNECT_DATA = (SID
    = QV1) (GLOBAL_NAME = QV1.WORLD)))
    OK (50 msec)

    V:\>

    My TNSNAMES.ORA file looks like this:

    # tnsnames.ora Network Configuration File: D:\DevSuiteHome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = FTLQSSRS01.DOMAIN.net)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = PLSExtProc)
    )
    )

    QV1.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (COMMUNITY = SAP.WORLD)
    (PROTOCOL = TCP)
    (HOST = sapdbq01)
    (PORT = 1527)
    )
    )
    (CONNECT_DATA =
    (SID = QV1)
    (GLOBAL_NAME = QV1.WORLD)
    )
    )

    My SQLNET.ORA file looks like this:

    # SQLNET.ORA Network Configuration File: D:\DevSuiteHome_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= ( TNSNAMES)
    AUTOMATIC_IPC = ON
    TRACE_LEVEL_CLIENT = 0
    SQLNET.EXPIRE_TIME = 0
    NAMES.DEFAULT_DOMAIN = WORLD
    NAME.DEFAULT_ZONE = WORLD

    The TNSPING above leads me to think that my TSNNAMES.ORA file is good, correct?

    Do I need a LISTENER.ORA file?

    Does my SQLNET.ORA file looks good?

    Could this be an issue with Visual Studio, since I can connect to Oracle using Oracle's Client tools?

    Now, when I test my connection from Visual Studio 2008 on the server I'm trying to connect from, I get the 12154 error.

    Do you guys have any other suggestions on this?

    Thanks in advance for help me on this issue.

    -Igor Santos

    Wednesday, February 10, 2010 4:45 PM

Answers

All replies

  • in your tnsnames.ora, try adding a SERVICE_NAME parameter making it like so.  I'm no oracle expert but I get 12154 errors without it in VS while I'll I can still do tnsping.  From what I heard you can find the value of this in your oracle server's init.ora file but from what I seen this is usually the tns alias, if you want to give that a shot. 

    QV1.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (COMMUNITY = SAP.WORLD)
    (PROTOCOL = TCP)
    (HOST = sapdbq01)
    (PORT = 1527)
    )
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ----------)
    (SID = QV1)
    (GLOBAL_NAME = QV1.WORLD)
    )
    )
    Wednesday, February 10, 2010 5:23 PM
  • Nehemiah, 
    Thanks for the suggestion, I added the SERVICE_NAME and still doesn't work.

    We're able to get this to work on our 32x environment and not on the 64x environment, so I'm thinking is an issue between the platforms.

    Let me know if you have any workaround for this issue.

    Thanks,
    -Igor Santos
    Thursday, February 11, 2010 5:36 PM
  • This is driving me nuts...

    I'm able to run the report when I RDP into the server in VS, I see the reports and results without issues.

    Can't see it when I deploy it to the server and try to see it through IE.

    Any suggestions?

    This is the error message I get when I try to view my report on the browser, I do not get this in VS while RDP'ing into the server.

    • An error occurred during client rendering.
      • An error has occurred during report processing.
        • Cannot create a connection to data source 'OracleDataSource'.
          • Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.
            • An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) 
    Tuesday, February 16, 2010 7:44 PM
  • Solution for this:

    Install both 11g clients x32 and x64, created the same sqlnet.ora and tnsnames.ora on both ORACLE_HOME/NETWORK/ADMIN forlders and it worked.

    Solution found on this other thread:
    http://www.attunity.com/forums/microsoft-ssis-oracle-connector/issues-pulling-data-oracle-package-fails-1472.html#post2286

    • Marked as answer by sqlsantos Wednesday, February 17, 2010 8:24 PM
    Wednesday, February 17, 2010 8:24 PM