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

我能够创建和执行DTS包从远程Oracle数据库拷贝表以本地SQL服务器,但要设置为链接服务器到Oracle数据库的连接.

DTS包当前使用Microsoft OLE DB Provider for Oracle,具有以下属性:

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

密码:UserPassword

用户ID:UserName

允许保存密码:true

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

我能够将链接服务器设置为远程Oracle数据库,最终成为一个多步骤的过程:

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

在SQL Server上创建系统DSN到Oracle数据库.

使用系统DSN在SQL Server上创建链接服务器.

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

一个.下载必要的Oracle Instant Client软件包:Basic,ODBC和SQL*Plus(可选)

湾 通常将包解压缩到SQL Server上的本地目录C:\Oracle.这应该会产生一个[目录] C:\Oracle\instantclient_10_2,这将是本答案其余部分中引用的[目录]的值.

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.将[directory]添加到系统PATH环境变量中.

即 创建一个名为TNS_Admin[directory] 的新系统环境变量

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

G.建议您重新启动SQL服务器,但可能没有必要.此外,您可能希望为此SQL Server服务器和SQL代理用户标识的目录授予安全权限.

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

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

湾 选择"系统DSN"选项卡,然后选择"添加"按钮.

C.在驱动程序列表中,选择Instantclient {version}中的Oracle.(例如,'即时客户端10_2中的Oracle')然后选择完成按钮.

d.指定以下内容:

Data Source Name:{System DSN Name}

Description:{留空/空}

TNS Service Name:应该OracleTnsName在tnsnames.ora列出的文件中定义,选择它作为值.

用户ID:{Oracle用户名}

即 选择测试连接按钮.系统将提示您提供{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语句为我解决了这个问题.