Forum Stats

  • 3,875,697 Users
  • 2,266,948 Discussions
  • 7,912,304 Comments

Discussions

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

572474
572474 Member Posts: 259
edited Jun 23, 2010 10:48AM in ODP.NET
Hello,

I hit problem this time.

Database server: Oracle 11g Release 1
Tools: Visual Studio 2008 Team System 2008
ODT.NET version 2.111.6.20

When I am trying to connect to Database using SCOTT from either the Server Explorer or Code I get the error:

"ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"

I can connect to the Database using SCOTT from Oracle SQL Devloper or JDeveloper without any problem.

Please help,
Ming Man
Tagged:
«1

Comments

  • 427070
    427070 Member Posts: 63
    Hi

    Check any old Oracle client has installed in your system. And check the path env variable and remove old Oracle client paths.

    Nilaksha.
  • 572474
    572474 Member Posts: 259
    Thanks,

    But I checked the path and I don't see any old client path. So it is still does not work.

    Ming Man
  • 502182
    502182 Member Posts: 474
    I assume that you are facing problem in connecting through a simple ODP.NET client also.

    Can you try out giving the full tns entry (of tnsnames.ora) for the data source in your connection string?

    For example, if currently your connection string is "user id=scott; password=tiger; data source=oracle" then replace 'oracle' with its equivalent value in tnsnames.ora.
  • 572474
    572474 Member Posts: 259
    Originally in my server I have:

    c:\app\chanmm\product\11.1.0\db_1\NETWORK\ADMIN
    ORCLDEMO =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcldemo)
    )
    )

    and I tried to change it because of the error to:

    ORCLDEMO =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (CONNECT_DATA = (SERVICE_NAME = orcldemo))
    )
    )

    I have no problem at all when I am using VS 2005 and even VS 2008 before I install ODP.NET 11g

    Mark are you around somewhere?

    Ming Man
  • 427070
    427070 Member Posts: 63
    Hi

    In our programs we do not use TNS Names, we give the TNS full description in Data Source parameter.

    eg:

    "user id=scott; password=tiger; data source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = demo)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)
    (SERVICE_NAME = orcldemo)))"


    Nilaksha.
  • 572474
    572474 Member Posts: 259
    I need TNS name to work so this definitely is not the solution for me.

    Thanks,
    Ming Man
  • 24208
    24208 Member Posts: 1,295
    Hi Ming Man,

    The first tns entry is correct:
    ORCLDEMO =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcldemo)
    )
    )
    Do you have a sqlnet.ora file in the c:\app\chanmm\product\11.1.0\db_1\NETWORK\ADMIN directory? If so, does it have HOSTNAME listed in the "names.directory_path" (if that is set)? If it does can you remove that from the list and try again?

    Thanks,

    Mark
  • 572474
    572474 Member Posts: 259
    Thanks Mark,

    for tnsnames.ora I change back to:

    ORCLDEMO =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcldemo)
    )
    )

    For sqlnet.ora original is:

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    so I tried to comment the NAMES.DIRECTORY_PATH as:

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    # NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    but no luck.

    Ming Man
  • 572474
    572474 Member Posts: 259
    Sorry Mark,

    My mistake. I comment

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    # NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    at my server side so it does not work but I comment at the ODP.NET client side and it works now.

    Thank you very much. You are really a guru.

    Ming Man
  • 24208
    24208 Member Posts: 1,295
    Hi Ming Man,

    Glad you got it working!

    - Mark
This discussion has been closed.