Forum Stats

  • 3,740,856 Users
  • 2,248,335 Discussions
  • 7,861,472 Comments

Discussions

TNS:listener was not given the SERVICE_NAME IN connect_data

485900
485900 Member Posts: 124
edited Apr 8, 2012 12:02AM in General Database Discussions
I did a search on this error but it doesn't seem any of the talk helps solve me issue or maybe I just don't understand.

I am trying to connect to a Oracle 10g Standard Edition server from another machine through Visual Studio .NET (not that it matters where I connect from).
I get the following error "TNS:listener was not given the SERVICE_NAME" IN connect_data. What is the problem here? Here is the listener.ora file:

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVER)(PORT = 1521))
)
)

One thing to note here is that I cannot connect to the server via Name I can only connect using the IP address from my machine. Not sure if that matters.

Comments

  • 485900
    485900 Member Posts: 124
    Here is another piece of information:

    I am able to connect to the database from my machine using sqlplus using the following command:

    sqlplus MyUser/[email protected]/ORCL

    It just seems to be a problem connecting to it though the .NET Oracle data provider.
  • 482476
    482476 Member Posts: 59
    Check your tnsnames file to make sure that you have properly configured a service name. Service names are looked up in tnsnames, and then handed off to the listener when the connection is attempted. If the name is wrong in tnsnames, that could account for the error.

    We've found that tnsnames can be very finicky about how it is edited on Windows systems. Often we have had to open the Net Configuration Assistant and reconfigure the name with it in order to get the name recognized. This tool is nice because you can test the connection as a part of the configuration.
  • 93654
    93654 Member Posts: 420
    Hello,

    Things to perform before connecting a database.

    a) Was the listener.ora configured propertly for the Database and status of the litener.

    Also the listener.ora looks like doesnt have the Database SID entry.
    << Sample ORCL or whatever you are refering..>>

    (SID_DESC =
    (GLOBAL_DBNAME = ORCL)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (SID_NAME = ORCL)
    )

    b) If listener is running try to add entry for the database in tnsnames.ora and try to connect using sqlplus or test using tnsping <dbalias>

    c) As you mentioned that it is not considering hostname in that is because there is no host entry for the hostname in the /etc/host file if you are refering UNIX or c:\windows\system32\drivers\etc\hosts if you are refering Windows.

    -Sri
  • 485900
    485900 Member Posts: 124
    Thanks for your postings. I figured out what the problem was. The Oracle client software on that machine did not have a reference to the Server in its tsnames.ora file.
    For deployment it would have been a nightmare to require the client to configure their tsnames.ora file so to get around this I added the full description into the Data Source property of the connection string and that solved the problem.

    Thanks again.
  • 681714
    681714 Member Posts: 1
    can you give an example of what you mean by full description?
  • orafad
    orafad Member Posts: 8,035 Silver Trophy
    Old post lives again!


    I would guess that that post was referring to something like this:
    http://download.oracle.com/docs/cd/B19306_01/win.102/b14307/featConnecting.htm#sthref114

    About Naming and Connect descriptors:
    http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/concepts.htm#i1041378
    http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/naming.htm#sthref683
  • Thanks a lot it works
  • 929127
    929127 Member Posts: 1
    Thanks a lot it works
This discussion has been closed.