Sql Server Linked Oracle Server

来源：互联网发布：矩阵的k次幂等于多少编辑：程序博客网时间：2024/04/23 18:45

客户有一个监视系统，日志文件写到Oracle数据库里。但是该日志会被定期清除，希望把所有日志记录都存到Oracle服务器中。

一、创建链接服务器

打开企业管理器，在【安全性】-->【链接服务器】上右键【新建链接服务器】。在【常规】选项卡上，选择数据源提供程序"Oracle Provider For OLE DB"。数据源中填写Oracle服务名。在【安全性】选项卡中填写Oracle的登录名和密码。

sp_addlinkedserver 'ORADB', 'ORACLE', 'OraOLEDB.Oracle', 'ORCL'
exec sp_addlinkedsrvlogin 'ORADB','false',null,'JK_DANGAN','caini'

二、链接测试

在查询分析中执行，SELECT * FROM ORADB..JK_DANGAN.RIGHTS。其中ORADB是我设置的链接服务器名。JK_DANGAN是Oracle用户。【附1】

提示错误信息：

服务器: 消息 7320，级别 16，状态 2，行 1

未能对 OLE DB 提供程序 'OraOLEDB.Oracle' 执行查询。

执行 sp_tables_ex 'ORADB' ，已经能获取链接服务器的所有表名。说明链接没有错误。可为什么还是不能执行呢？GOOGLE一下，终于找到解决方法：

在注册表中找到：

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSSQLServer/Providers/OraOLEDB.Oracle

将AllowInProcess的值修改为1。如果没有该项，那么自己创建一个就好了。注销之后再测试就链接可以了。

奇怪的是，使用 SELECT * FROM OPENQUERY(ORADB,'SELECT * FROM JK_DANGAN.RIGHTS')【附2】则不存在修改注册表的问题。在网上看到，说是采用OPENQUERY很稳定，速度也不错。

三、将数据写入Oracle

首先想到的是，在Sql Server的数据源表上创建触发器，这样在Sql Server插入一条，也就在Oracle中插入一条。

写好触发器，然后在源表中插入一条测试数据，发现报：

服务器:消息 7391, 级别 16, 状态 1, 行 2
The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction.
[OLE/DB provider returned message:New transaction cannot enlist in the specified transaction coordinator.]

原因是在Windows Server 2003 上，Microsoft 分布式事务协调器服务必须运行在 NT AUTHORITY/NetworkService 。【附3】

由于客户的SQL Server服务所在服务器是集群的一台服务器，不能修改MSDTC的运行设置。最终选择了在 SQL Server服务上创建任务，定期进行数据读取写入到Oracle。

附1：

在 Oracle 链接服务器中引用表时，请使用以下规则：

如果在 Oracle 中创建的表名和列名没有引用的标识符，请全部使用大写字母名称。
如果在 Oracle 中创建的表名和列名有引用的标识符，请全部使用与 Oracle 中创建名称时相同的字母大小写格式。
INSERT 语句应为表中的所有列提供值，即使表中的某些列为 NULL 或有默认值。

附2：

依据 OLE DB 提供程序的能力，还可以将 OPENQUERY 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。

也就是说，可以将OPENQUERY当作一个表看待，甚至可以跟别的表联合查询。

INSERT INTO OPENQUERY(ORADB,'SELECT * FROM JK_DANGAN.RIGHTS')
SELECT * FROM RIGHTS
WHERE NOT EXISTS
(SELECT * FROM OPENQUERY(ORADB,'SELECT * FROM JK_DANGAN.RIGHTS') T WHERE T.ID = RIGHTS.ID)

附3：

参看文章：http://support.microsoft.com/kb/903944/zh-cn