通过数据库链接将Oracle连接到SQL Server [英] Connecting Oracle to SQL Server via database link

问题描述

我尝试从Oracle 10G（在UNIX上）连接到SQL Server数据库（在Windows上）。我看了手册，我会承认，我找到的文档很难遵循。似乎有几个选项要使用，但没有一个文档描述每个选项如何工作。

I am trying to connect from Oracle 10G(on UNIX) to a SQL Server database (On windows). I have looked at the manual and i will admit that i am finding the documentation quite difficult to follow. There seems to be several options to use but none of the documentation describe how each option works.

例如，我已经给出了我需要连接到的数据库（即SQL Server数据库）的以下信息：

As an example, i have been given the following information on the database i need to connect to (i.e. the SQL Server database)

• 用户名


• 密码


• 数据库名称（假设数据库名称为data_extract） li>
  

• Username
• Password
• Database Name [lets assume the database name is data_extract]

要连接上面，我做了以下更改

To connect the above i made the following changes

  HS_FDS_CONNECT_INFO = data_extract
  HS_FDS_TRACE_LEVEL = 0

$ ORACLE_HOME / network / admin / tnsnames.ora

$ORACLE_HOME/network/admin/tnsnames.ora

  sqlserver.db =
          (DESCRIPTION =
                  (ADDRESS = (protocol=tcp)(host=10.10.10.10)(port=49400))
                          (connect_data = (sid=data_extract))
                          (hs=ok)
          )

$ ORACLE_HOME / network / admin /listener.ora

$ORACLE_HOME/network/admin/listener.ora

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = merlin)(PORT = 1525))
      )
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
        (PROGRAM = extproc)
      )
      (SID_DESC =
        (GLOBAL_DBNAME = db1.mydb.co.uk)
        (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
        (SID_NAME = billdb)
      )
      (SID_DESC =
          (SID_NAME = data_extract)
          (ORACLE_HOME = /u/app/oracle/product/10.2.0/db)
          (program = hsodbc)
      )
    )

注意：在listener.ora中，我只添加了最后一个SID_DESC条目。

Note: In listener.ora, i only added the last SID_DESC entry.

然后我继续创建数据库链接，如下所示

I then went on and created the database link as shown below

  create database link sqlservdb using 'sqlserver.db';

当我尝试访问表时，我得到以下错误

When i try to access a table i get the following error

  sqlplus> select * from TESTTABLE@sqlservdb;
  select * from TESTTABLE@sqlservdb
                             *
  ERROR at line 1:
  ORA-28545: error diagnosed by Net8 when connecting to an agent
  Unable to retrieve text of NETWORK/NCR message 65535
  ORA-02063: preceding 2 lines from ORASQLSERVER

我不确定的事情

• 我在哪里指定用于访问sql服务器的用户名和密码，从oracle db到sql server db


• 在互联网上阅读后，我可以看到有些人引用了DSN数据源。我被告知，我拥有的信息是我需要的。


• 在inithsodbc.ora和tnsnames.ora中指定的sid实际上是sql server数据库。它是否正确？

以上的任何帮助将不胜感激。感谢

Any help with the above will be appreciated. Thanks

推荐答案

我使用Data Direct驱动程序，必须在Oracle UNIX机器上配置。您必须在UNIX中创建ODBC连接，以便能够查看SQL Server。 （至少我一直是这样做的）

I used Data Direct drivers which had to be configured on the Oracle UNIX machine. You must create the ODBC connection in UNIX in order to be able to see SQL Server. (At least that has always been how I have done it)

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