ORA-12504:在CONNECT_DATA中未为TNS:listener提供SERVICE_NAME [英] ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

问题描述

我正在尝试使用Visual Studio 2010中的服务器资源管理器连接本地Oracle 11g数据库.我尝试按照说明设置所有内容，但是仍然出现ORA-12504错误.

I'm trying to connect my local Oracle 11g database using server explorer in Visual Studio 2010. I tried to setup everything as per instruction, but I'm still getting an ORA-12504 error.

这是错误:

这是我的tnsnames.ora内容:

Here is my tnsnames.ora contents:

# tnsnames.ora Network Configuration File: C:\app\rsahi\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.


VENUS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SPRPRG020)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VENUS)
    )
  )

LISTENER_VENUS =
  (ADDRESS = (PROTOCOL = TCP)(HOST = SPRPRG020)(PORT = 1521))

listener.ora文件内容，

listener.ora file contents,

# listener.ora Network Configuration File: C:\app\rsahi\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SPRPRG020)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\rsahi

推荐答案

当我尝试使用Oracle 11g创建LinkedServer时遇到了相同的问题.我的oracle实例名称是: PC-2.my.xgen ，而我的侦听器名称是" NB ".第一条语句的最后一个参数实际上是Oracle服务器实例和侦听器名称的串联.

I have experienced the same problem when I was trying to create LinkedServer with Oracle 11g. My oracle instance name was: PC-2.my.xgen and my listener name was "NB". The last parameter of first statement is actually the concatenation of Oracle server instance and listener name.

因此，我在SQL Server中写下了以下语句.

So I have write down the following statements in SQL server.

--add a linked server into SQL server
--last parameter contains OracleInstance / Listener Name of desired database
EXEC sp_addlinkedserver   'OracleLinkedServer4', 'Oracle',   'MSDAORA', 'PC-2.my.xgen/nb'

--add login information into linked server
EXEC sp_addlinkedsrvlogin 'OracleLinkedServer4', false,  Null, 'system',  '123456'

在Oracle数据库"NB"中，有一个名为CRD_CIL_NOTIFICATION_TYPE的表.因此，我写下了以下语句来获取记录.

In Oracle database "NB" I have a table named : CRD_CIL_NOTIFICATION_TYPE. So I have written down the following statement to get the records.

select * from OracleLinkedServer4..SYSTEM.CRD_CIL_NOTIFICATION_TYPE

这篇关于ORA-12504:在CONNECT_DATA中未为TNS:listener提供SERVICE_NAME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！