sql - 如何在SQL 2000/2005上将链接服务器设置为Oracle数据库？

Question

我能够创建并执行一个DTS程序包，该程序包将表从远程Oracle数据库复制到本地SQL服务器，但希望将与Oracle数据库的连接设置为链接服务器。

DTS包当前使用具有以下属性的Microsoft OLE DB提供程序用于Oracle:

数据源:SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=acc)));uid=*UserName*;pwd=*UserPassword*;

密码:UserPassword

用户ID:用户名

允许保存密码:true

如何使用上面定义的数据源将链接服务器设置为Oracle数据库？

Answer 1

我能够将链接服务器设置到远程Oracle数据库，这最终是一个多步骤过程:

在SQL Server上安装Oracle ODBC驱动程序。

在SQL Server上为Oracle数据库创建系统DSN。

使用System DSN在SQL Server上创建链接服务器。

步骤1:在服务器上安装Oracle ODBC驱动程序

一种。下载必需的 Oracle Instant Client软件包:Basic，ODBC和SQL * Plus(可选)

b。将软件包解压缩到SQL服务器上的本地目录，通常为 C:\Oracle。这应该导致像 C:\Oracle\instantclient_10_2这样的[directory]，这将是此答案其余部分中引用的[directory]的值。

C。在包含以下内容的即时客户端[目录]中创建一个名为 tnsnames.ora 的文本文件:

OracleTnsName = 
(
  DESCRIPTION=
  (
    ADDRESS = (PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521)
  )
  (
    CONNECT_DATA = (SERVICE_NAME=acc)
  )
)

注意:实际的 HOST， PORT和 SERVICE_NAME会根据您要建立连接的Oracle服务器而有所不同。通常可以使用Oracle网络客户端工具在侦听器下找到此信息。
OracleTnsName可以是您想要分配给Oracle数据源的任何名称，并将在设置系统DSN时使用。如果需要，您还可以使用以上语法在同一个 tnsnames.ora文件中定义多个TNS名称。

d。将[目录]添加到系统 PATH 环境变量。

e。创建一个名为 的新系统环境变量TNS_Admin ，其值为[directory]

F。执行 [directory]\odbc_install.exe 实用程序以安装Oracle ODBC驱动程序。

G。建议您重新启动SQL Server，但可能没有必要。另外，您可能希望向该目录授予SQL Server和SQL代理用户身份的安全权限。

步骤2:创建使用Oracle ODBC驱动程序的系统DNS

一种。打开ODBC数据源管理器工具。 [管理工具->数据源(ODBC)]

b。选择系统DSN选项卡，然后选择添加按钮。

C。在驱动程序列表中，选择Instantclient {version}中的Oracle。 (例如，“instantclient 10_2中的Oracle”)，然后选择“完成”按钮。

d。指定以下内容:

Data Source Name:{系统DSN名称}

Description:{保留空白/空}

TNS Service Name:应该列出您在 OracleTnsName 文件中定义的tnsnames.ora，将其选择为值。

用户ID:{Oracle用户名}

e。选择测试连接按钮。应该提示您提供{Oracle用户密码}。如果一切顺利，测试将成功。

步骤3:在SQL中创建到Oracle数据库的链接服务器

在SQL Server中打开查询窗口并执行以下操作:

EXEC sp_addlinkedserver 
     @server        = '{Linked Server Name}'
    ,@srvproduct    = '{System DSN Name}'
    ,@provider      = 'MSDASQL'
    ,@datasrc       = '{System DSN Name}'

EXEC sp_addlinkedsrvlogin 
     @rmtsrvname    = '{Linked Server Name}'
    ,@useself       = 'False'
    ,@locallogin    = NULL
    ,@rmtuser       = '{Oracle User Name}'
    ,@rmtpassword   = '{Oracle User Password}'

注意: {Linked Server Name}可以是您在引用Oracle服务器时想要使用的任何东西，但是 {System DNS Name} 必须与您先前创建的系统DSN的名称匹配。
{Oracle User Name}应该与系统DSN使用的用户ID相同，并且 {Oracle User Password}应该与成功测试ODBC连接所使用的用户ID相同。有关排除Oracle链接服务器问题的信息，请参见 KB 280106。

查询Oracle链接服务器

您可以使用 OPENQUERY在Oracle链接服务器上执行直通查询，但是请注意，对于非常大的记录集，如果在直通查询中指定 ORA-01652子句，则可能会收到 ORDER BY错误消息。将 ORDER BY子句从传递查询移动到外部select语句为我解决了此问题。