Sql Server Linked Oracle Server

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

 

一、创建链接服务器

 

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

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

 

二、链接测试

 

在查询分析中执行，SELECT * FROMORADB..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 * FROMJK_DANGAN.RIGHTS')【附2】则不存在修改注册表的问题。在网上看到，说是采用OPENQUERY很稳定，速度也不错。

 

三、将数据写入Oracle

 

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

 

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

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

 

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

 

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

 

附1：

 

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

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

 

附2：

 

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

 

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

 

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

 

附3：

 

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