sqlserver2000连接oracle9i的方法2009-12-21 17:59一：应用环境
OS:win servers2003 with sp2
DB:sqlserver2K with sp2/oracle9i
二：连接步骤
1.要求pc机上安装oralce9i客户端软件和sqlserver2000软件。
2.客户端的配置tnsnames.ora文件，配置所要连接的数据库服务器.
3.配置windows的ODBC数据源：
3.1)开始菜单—》设置—》管理工具—》数据源（ODBC）—》进入配置用户DSN
3.2)添加—》选择Microsoft ODBC for oracle—》
自定义数据源名称（最好跟tns中连接串同名！）—》
服务器名称（必填！填写tns文件中的连接串名称）—》完成。

4.配置sqlserver2000中的连接服务器：
4.1)企业管理器—》安全性—》连接服务器—》右键新建连接服务器—》定义连接名称；
(1) 选其他数据源； 指定程序名称为：Microsoft OLE DB Provider for Oracle;
(2) 产品名称可不填；
(3) 数据源指定刚才ODBC中定义好的数据源名称；
(4) 提供程序字符串 按
以下格式填写：User ID=username;PassWord=userpasswd
(或者按如下格式：UID=username;PWD=userpasswd)，
这里的用户名和密码对应所要连接的oracle数据库中的用户名和密码 —》
(5) 安全性标签页里：设置用此安全上下文进行，并录入oracle的数据库用户名和密码—》
服务器选项标签页可默认—》确定。

5.准备工作全部完成啦！
在sqlserver企业管理器—》安全性—》连接服务器打开刚建好的连接服务器—》
点击表，即可在右边窗口看到该oracle数据库用户拥有的的所有表名
查询数据方法如下：
select * from openquery(连接服务器名，'SELECT * FROM 用户名.表名')
查询语句所有的单词必须大写

/*********************************
-- 檢查SysServers表中是否存在新建的連接服務器名(例:Ln_Oracle)
select * from master..sysservers where srvname='ln_oracle'

--刪除新建的連接服務器名(例:Ln_Oracle)
Exec sp_droplinkedsrvlogin 'ln_oracle',Null
Exec sp_dropserver 'ln_oracle'
go
--新建連接服務器名(例:Ln_Oracle)
EXEC sp_addlinkedserver
@server ='ln_oracle',
@srvproduct='Oracle',
@provider='MSDAORA',
@datasrc='ERP.WORLD'
--新建連接服務器名(例:Ln_Oracle)登陸信息
EXEC sp_addlinkedsrvlogin
'ln_oracle', --連接服務器名
'false',
NULL,
'discadmin', --遠程用戶名
's01ardm' --遠程用戶密碼

--測試查詢ORacle數據庫中客戶表的信息:
select * from OpenQuery( ln_oracle,
' SELECT * FROM APPS.RA_CUSTOMERS where customer_name like''亞合%'' ');

**********************/

特别说明：如果你按上述步骤新建成功后，点选表却抛出如下服务器错误
消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MSDAORA' 报错。
[OLE/DB provider returned message: 未找到 Oracle 客户端和网络组件。
这些组件是由 Oracle 公司提供的，是 Oracle 8i 版 (或更高) 客户软件安装的一部分。
在安装这些组件之前，将无法使用此提供程序。]
OLE DB 错误跟踪［OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:

］
：请如下步骤修改注册表
1.在电脑的任意位置新建一个txt文档，拷贝如下内容注册表项：
[HKEY_LOCAL_MACHINESOFTWARE
MicrosoftMSDTCMTxOCI]
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"
2.修改txt文档的后缀名为.reg,双击运行。
3.重启系统即可

如果是win xp with sp3+win sqlserver2K，可能不要改上述设定，本人测试本机时，
没有修改上述注册表项也测试成功，你如果测试不ok，请查阅相关的网页
参考博客：http://www.cnblogs.com/autumn/articles/splinkedserver.html
[@more@]　前段時間我一直想建立鏈接伺服器但總是不成功，後來在網絡上找到正確建立的方法：

1.直接使用SQL 語句，

use pubs

return

go

if not exists(select 1 from master..sysservers where srvname='ServerName')

exec sp_addlinkedserver 'ServerName', ' ', 'SQLOLEDB ', '192.168.1.20/ServerName'

go

--此處要增加一個go語句，否則會提示出錯：在 sys.servers 中找不到伺服器 'ServerName'。請確認所指定的　　　　　--伺服器名稱是否正確。如果需要，請執行預存程序 sp_addlinkedserver，將伺服器加入 sys.servers。另外再添加RPC訪問及輸出功能，即可以調用鏈接數據庫的存儲過程。

exec sp_serveroption N'ServerName', N'rpc out', N'true'
exec sp_serveroption N'ServerName', N'rpc', N'true'
exec sp_addlinkedsrvlogin 'ServerName', 'false ',null, 'username ', 'password'
select TOP 10 * from ServerName.DataBaseName.dbo.TableName
exec sp_dropserver 'ServerName', 'droplogins '

2.手動建立鏈接伺服器，即通過sql server management studio來建立，

1>伺服器物件||連接的伺服器－－新增連接伺服器；

　2>在新增連接伺服器對話框左側選擇“一般”選項；右側，“連接的伺服器”中輸入相應的伺服器名稱；“產品名稱”中輸入一個標識名字；“提供者”選擇“SQL Native Client”；“資料來源”輸入伺服器的ＩＰ地址或伺服器名稱；

　3>在新增連接伺服器對話框左側選擇“安全性”選項；右側，選擇最下方的“使用此安全性內容建立”單選項，並輸入“遠端錄入”用戶名和“指定密碼”。

　最後單擊新增連接伺服器對話框的確定按鈕以完成操作。

3.连接远程/局域网数据(openrowset/openquery/opendatasource)

1>openrowset

--查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表

--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1

2>openquery用法需要创建一个连接

--首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A

3> opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from 本地表

****************************

--建立连接服务器
EXEC sp_addlinkedserver '远程服务器IP','SQL Server'
--标注存储
EXEC sp_addlinkedserver
@server = 'server', --链接服务器的本地名称。也允许使用实例名称，例如MYSERVERSQL1
@srvproduct = 'product_name' --OLE DB数据源的产品名。对于SQL Server实例来说，product_name是'SQL Server'
, @provider = 'provider_name' --这 是OLE DB访问接口的唯一可编程标识。当没有指定它时，访问接口名称是 SQL Server数据源。SQL Server显式的provider_name是 SQLNCLI（Microsoft SQL Native Client OLE DB Provider）。Oracler的是 MSDAORA，Oracle 8或更高版本的是OraOLEDB.Oracle。MS Access和MS Excel的是 Microsoft.Jet.OLEDB.4.0。IBM DB2的是DB2OLEDB，以及ODBC数据源的是MSDASQL
, @datasrc = 'data_source' --这 是特定OLE DB访问接口解释的数据源。对于SQL Server，这是 SQL Server（servername或servernameinstancename）的网络名称。对于Oracle，这是SQL*Net别名。对于 MS Access和MSExcel，这是文件的完整路径和名称。对于ODBC数据源，这是系统DSN名称
, @location = 'location' --由特定OLE DB访问接口解释的位置
, @provstr = 'provider_string' --OLE DB 访问接口特定的连接字符串。对于ODBC连接，这是ODBC连接字符串。对于MS Excel，这是Excel 5.0
, @catalog = 'catalog' --catalog的定义变化基于OLE DB访问接口的实现。对于SQL Server，这是可选的数据库名称，对于DB2，这个目录是数据库的名称


--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin '远程服务器IP','false','sa','架构名','访问密码'
--标注存储
EXEC sp_addlinkedsrvlogin
@rmtsrvname = '远程服务器IP', --要添加登录名映射的本地链接服务器
@useself = false, --当使用true值时，使用本地SQL或Windows登录名连接到远程服务器名。如果设为false，存储过程 sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword参数将应用到新的映射中
@locallogin = NULL, --这是映射到远程登录名的SQL Server登录或Windows用户的名称。如果这个参数置为NULL，映射将应用SQL Server实例中的所有本地登录名
@rmtuser = '架构名', --用来连接到链接服务器的用户／登录名的名称
@rmtpassword = '访问密码' --用来连接到链接服务器的用户／登录名的密码

--查询数据
select top 10 * from [222].pwms.pwms.bas_tb_sku

--查看链接服务器信息
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1

--配置链接服务器属性
exec sp_serveroption '别名','name','远程服务器IP'
--标注存储
EXEC sp_serveroption
@server = 'server' --配置属性的链接服务器的名称
,@optname = 'option_name' --要配置的选项
,@optvalue = 'option_value' --选项的新值
--附参数
参 数 描 述
collation compatible 如果你确认SQL Server实例与远程SQL Server拥有相同的排序规则就启用这个设置。由于SQL Server不再需要对数据源之间的字符列执行比较操作，把它们假定为相同的排序规则，这样做可以提升性能
collation name 如果启用了use remote collation并且是非SQL Server的数据源，则collation name指定远程服务器排序规则的名称。这个排序规则名称必须是SQL Server所支持的
connect timeout 指定在超时发生之前到链接服务器的连接会尝试多少秒。如果数值为“0”，sp_ configure的remote query timeout的服务器值用来当作默认值
data access 如果启用，就允许分布式查询访问
lazy schema validation 如果设为true，则架构不会在查询开始时去检测远程表。尽管这样会减少远程查询的负载，但是如果架构发生了变化并且你没有进行架构检测，比如说查询中引用的对象不能与查询命令进行通信，就会生成错误
query timeout 指定查询等待的超时值（秒数）。如果这个值为0，则query wait选项使用sp_configure值
rpc 启用从服务器进行远程过程调用
rpc out 启用远程过程调用到服务器
use remote collation 指定是使用远程服务器排序规则（true）还是本地服务器排序规则（false）

--删除链接服务器属性
exec sp_dropserver '4'
--标注存储
EXEC sp_dropserver
@server = 'server' --从SQL Server实例中删除的链接服务器的名称
,@droplogins = 'droplogins' --如果指定droplogins，则在删除链接服务器之前要删除登录名映射


--查看链接登录名
select s.name linkedServerName,s.data_source linkedserver_source,s.is_linked,s.modify_date,
ll.remote_name,ll.local_principal_id,ll.uses_self_credential,
p.name localLoginName
from sys.Linked_logins ll
inner join sys.servers s on s.server_id=ll.server_id
left join sys.server_principals p on p.principal_id=ll.local_principal_id
where s.is_linked = 1

--删除链接服务器登录名映射
exec sp_droplinkedsrvlogin '远程服务器IP',NULL
--标注存储
EXEC sp_droplinkedsrvlogin
@rmtsrvname = '远程服务器IP' --登录名映射的链接服务器名称
@locallogin = NULL --这是从链接服务器删除的SQL Server登录或Windows用户映射的名称

SQL code

--简单点为远程机器设置远程链接服务器。
--假设远程机器IP为127.0.0.1
--建立连接服务器
EXEC sp_addlinkedserver '127.0.0.1','SQL Server'

--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin '10.0.0.222','false','sa','架构名','密码'

--查询数据
select top 10 * from [127.0.0.1].数据库名.架构名.表名

--查看链接服务器信息
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1

--配置链接服务器属性设置别名
exec sp_serveroption 'remotecmp','name','127.0.0.1'

--查询数据
select top 10 * from remotecmp.数据库名.架构名.表名

來源:http://www.newsmth.net/pc/pccon.php?id=2206&nid=390152

**************************