如何在SQL 2000/2005上将链接服务器设置为Oracle数据库? [英] How do you setup a linked server to an Oracle database on SQL 2000/2005?

查看：85
本文介绍了如何在SQL 2000/2005上将链接服务器设置为Oracle数据库?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

  • 数据源: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数据库，这最终是一个多步骤过程:

  1. 在SQL Server上安装Oracle ODBC驱动程序.
  2. 在SQL Server上为Oracle数据库创建系统DSN.
  3. 使用System DSN在SQL Server上创建链接服务器.

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

a.下载必要的 Oracle Instant Client 软件包:基本，ODBC和SQL * Plus(可选)

b.将软件包解压缩到SQL Server上的本地目录(通常为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服务器和SQL代理用户身份.

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

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

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

c.在驱动程序列表中，选择 instantclient {version}中的Oracle . (例如，"instantclient 10_2中的Oracle")，然后选择完成"按钮.

d.指定以下内容:

  • Data Source Name:{系统DSN名称}
  • Description:{保留空白/空}
  • TNS Service Name:应该列出您在 tnsnames.ora 文件中定义的OracleTnsName，将其选择为值.
  • 用户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链接服务器，但是请注意，对于非常大的记录集，如果在传递查询中指定ORDER BY子句，则可能会收到ORA-01652错误消息.将ORDER BY子句从传递查询移动到外部select语句对我来说解决了这个问题.

I am able to create and execute a DTS package that copies tables from a remote Oracle database to a local SQL server, but want to setup the connection to the Oracle database as a linked server.

The DTS package currently uses the Microsoft OLE DB Provider for Oracle with the following properties:

  • Data Source: SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=acc)));uid=*UserName*;pwd=*UserPassword*;
  • Password: UserPassword
  • User ID: UserName
  • Allow saving password: true

How do I go about setting a linked server to an Oracle database using the data source defined above?

解决方案

I was able to setup a linked server to a remote Oracle database, which ended up being a multi-step process:

  1. Install Oracle ODBC drivers on SQL Server.
  2. Create System DSN to Oracle database on SQL Server.
  3. Create linked server on SQL server using System DSN.

Step 1: Install Oracle ODBC drivers on server

a. Download the necessary Oracle Instant Client packages: Basic, ODBC, and SQL*Plus (optional)

b. Unzip the packages to a local directory on the SQL server, typically C:\Oracle. This should result in a [directory] like C:\Oracle\instantclient_10_2, which will be the value of [directory] referenced in the rest of this answer.

c. Create a text file named tnsnames.ora within the instant client [directory] that contains the following:

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

Note: Actual HOST, PORT, and SERVICE_NAME will vary based on Oracle server you are establishing a connection to. This information can often be found using the Oracle network client tools under the listeners.

The OracleTnsName can be any name you want to assign to the Oracle data source, and will be used when setting up the system DSN. You can also use the syntax above to define multiple TNS names in the same tnsnames.ora file if desired.

d. Add the [directory] to the system PATH environment variable.

e. Create a new system environment variable named TNS_Admin that has a value of [directory]

f. Execute the [directory]\odbc_install.exe utility to install the Oracle ODBC drivers.

g. It is recommended that you reboot the SQL server, but may not be necessary. Also, you may want to grant security permissions to this directory for the SQL server and SQL agent user identities.

Step 2: Create a System DNS that uses the Oracle ODBC driver

a. Open the ODBC Data Source Administrator tool. [ Administrative Tools --> Data Sources (ODBC) ]

b. Select the System DSN tab and then select the Add button.

c. In the drivers list, select Oracle in instantclient {version}. (e.g. 'Oracle in instantclient 10_2') and then select Finish button.

d. Specify the following:

  • Data Source Name: {System DSN Name}
  • Description: {leave blank/empty}
  • TNS Service Name: should have the OracleTnsName you defined in the tnsnames.ora file listed, select it as the value.
  • User ID: {Oracle user name}

e. Select Test Connection button. You should be prompted to provide the {Oracle user password}. If all goes well the test will succeed.

Step 3: Create linked server in SQL to the Oracle database

Open a query window in SQL server and execute the following:

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}'

Note: The {Linked Server Name} can be anything you want to use when referencing the Oracle server, but the {System DNS Name} must match the name of the system DSN you created previously.

The {Oracle User Name} should be the same as the User ID used by the system DSN, and the {Oracle User Password} should be the same as you used to successfully test the ODBC connection. See KB 280106 for information on troubleshooting Oracle linked server issues.

Querying the Oracle linked server

You may use OPENQUERY to execute pass-through queries on the Oracle linked server, but be aware that for very large recordsets you may receive a ORA-01652 error message if you specify a ORDER BY clause in the pass-through query. Moving the ORDER BY clause from the pass-through query to the outer select statement solved this issue for me.

这篇关于如何在SQL 2000/2005上将链接服务器设置为Oracle数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆