☰

Oracle 10g ODBC 驱动程序和连接器

tag-icon • tag-icon windows-server-2003 sql-server oracle10g odbc
Oracle 10g ODBC 驱动程序和连接器

您好，我使用两台机器：Windows XP 和 Windows Server 2003，在两台机器上我都安装了 Oracle 客户端 10g，当我尝试在 SQL Server 上使用 ODBC 作为链接服务器选项时，它只在我的 XP 上运行，但在服务器上我无法让它运行，即使使用相同的配置。

笔记：

Windows XP - SQL Server 2008 开发人员 Windows Server 2003 - SQL Server 2000 标准版

是 SQL 版本吗？

谢谢。

在两台机器上我都得到相同的 tnsnames.ora 文件：

# tnsnames.ora Network Configuration File: C:\oradata\client\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

MEDICAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERCONTABLE.RESOCENTRO.COM)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = medical)
    )
  )

但安装目录不同。

SSMS 的错误堆栈：

===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.GetChildren(IGetChildrenRequest request)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

===================================

El proveedor OLE DB 'OraOLEDB.Oracle' informa de un error. El proveedor no proporcionó información acerca del error. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
Server Name: 192.168.0.3
Error Number: 7399
Severity: 16
State: 1
Procedure: sp_tables_ex
Line Number: 13


------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

当我尝试将 SSMS 与 OPENQUERY 一起使用时，我得到以下信息：

select * from openrowset 

('OraOLEDB.Oracle','oradb';'system';'siscad', 

'select * from emp') 

Servidor: mensaje 7399, nivel 16, estado 1, línea 1
El proveedor OLE DB 'OraOLEDB.Oracle' informa de un error.  
[OLE/DB provider returned message: ORA-12154: TNS:no se ha podido resolver el identificador de conexión especificado]
Traza de error de OLE DB [OLE/DB Provider 'OraOLEDB.Oracle' IDBInitialize::Initialize returned 0x80004005:   ].

诽谤：

Server: Message 7399, level 16, State 1, line 1 OLE DB 'OraOLEDB.Oracle' provider reports an error.   
[OLE/DB provider returned message: ORA-12154: TNS:no could be resolved the connection identifier specified] 
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IDBInitialize:Initialize returned 0x80004005:].

答案1

不，这不太可能是因为 SQL 版本。我在设置到 Oracle 目标的链接服务器时最常遇到的错误是 Oracle 名称解析错误。检查以确保您的 tnsnames.ora 在 Windows 2003 服务器上的条目与 XP 计算机上的条目相同。tnsnames.ora 文件通常位于

\[oracle install root]\product\[version]\client_x\NETWORK\ADMIN

其中 version 是 Oracle 的版本，client_x 是您正在使用的客户端安装的实例。

答案2

解决方案 ：

编辑或添加新的 REG_DWORDAllowInProcess并将十六进制值设置为1

RUN> regedit > HKEY_LOCALMACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[Version]\Providers\OraOLEDB.Oracle

相关内容