将Oracle与SQL Server链接 [英] Linking Oracle with SQL Server

问题描述

我正在尝试将SQL Server 2005链接到Oracle 10g数据库.我已经在SQL Server上安装了Oracle客户端，并验证了我可以同时使用tnsping和sqlplus连接到Oracle数据库.当我尝试在SQL Server中运行查询时，得到以下信息:

I'm trying to link SQL Server 2005 to an Oracle 10g database. I've installed the Oracle client on the SQL server and validated that I can connect to the Oracle database using both tnsping and sqlplus. When I try to run a query in SQL Server I get the following:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD".

OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD".

有什么想法吗?我已经尝试了以下两个查询，但都没有运气:

Any ideas? I've tried both of the following queries with no luck:

select * from openquery(ORA_CSSA2APD, 'select count(rowid) from eservice_op.agent')

select count(rowid) from ORA_CSSA2APD..eservice_op.agent

推荐答案

我怀疑环境设置.也就是说，您的会话正在拾取TNSNAMES.ORA文件，但基础SQL Server的会话却没有.我会检查ORACLE_HOME和TNS_ADMIN是否已设置并指向.

I suspect an environment setting. That is, your session is picking up the TNSNAMES.ORA file but the session underlying SQL Server is not. I'd check were ORACLE_HOME and, possibly, TNS_ADMIN are being set and pointing to.

您是否可以通过SQL Server连接对数据库使用轻松连接语法.

Are you able to use the easy connect syntax for the database with the SQL Server connection .

IE将ORA_CSSA2APD替换为主机名:1521/service_name

IE replace ORA_CSSA2APD with hostname:1521/service_name

这篇关于将Oracle与SQL Server链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！