通过触发器实现数据库同步问题(sqlserver2000与sqlserver2005)

现有两台数据库服務器,一台安裝的是sqlserver2000(服务器名为server1),另外一台安裝的是sqlserver2005(服务器名为server2).
server1中有一个数据库database1,其中存在一张表table1;
server2中也有一个数据库database2,其中存在着一张表table2;
已知table1和table2的结构相同,现在想让table1和table2这两张表实现同步,即当对table1表进行增删改的操作,table2中资料也应该实时地更新,始终要保持table2和table1的同步;
我的做法是在server1上建立一个链接服务器,直接指向到server2,然后在table1上建立了一个触发器(包含insert,update,delete),通过触发器实时地更新table2,这时候问题就出来了,当我在table1上新增一条记录的时候,系统提示错误信息,错误信息为:"[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message:New transaction cannot enlist in the specified tansaction coordinator] ,[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was ubable to begin a distributed transaction"
我把sqlserver2005的数据库服务器换成另外的一台安装sqlserver2000的数据库服务器,然后再在server1上新建一个链接服务器,修改table1中的触发器中使用到的链接服务器名称,问题竟然解决了
我想是不是通过触发器实现同步,只能是两台sqlserver2000服务器之间可以实现,而对于一台是sql2000,另外一台是sql2005的情况,是实现不了的?
希望各位达人帮忙分析一下出错的原因,多谢了.

9 个解决方案

#1

sorry,我把sqlserver2005的数据库服务器换成sqlserver2000的数据库服务器也还是不行,还是会出现相同的错误,链接服务器应该是没有问题的,因为我在查询分析器中,使用创建的链接服务器访问另外一台服务器都是可以的

#2

/*  
  作者:邹建  
  */  
   
  /*--同步两个数据库的示例  
   
  有数据  
  srv1.库名..author有字段:id,name,phone,  
  srv2.库名..author有字段:id,name,telphone,adress  
   
  要求：  
  srv1.库名..author增加记录则srv1.库名..author记录增加  
  srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新  
  --*/  
   
  --大致的处理步骤  
  --1.在   srv1   上创建连接服务器,以便在   srv1   中操作   srv2,实现同步  
  exec   sp_addlinkedserver     'srv2','','SQLOLEDB','srv2的sql实例名或ip'  
  exec   sp_addlinkedsrvlogin   'srv2','false',null,'用户名','密码'  
  go  
   
  --2.在   srv1   和   srv2   这两台电脑中,启动   msdtc(分布式事务处理服务),并且设置为自动启动  
  我的电脑--控制面板--管理工具--服务--右键   Distributed   Transaction   Coordinator--属性--启动--并将启动类型设置为自动启动  
  go  
   
   
  --3.实现同步处理  
   
  --a.在srv1..author中创建触发器,实现数据即时同步  
  --新增同步  
  create   trigger   tr_insert_author   on   author  
  for   insert  
  as  
  set   xact_abort   on  
  insert   srv2.库名.dbo.author(id,name,telphone)  
  select   id,name,telphone   from   inserted  
  go  
   
  --修改同步  
  create   trigger   tr_update_author   on   author  
  for   update  
  as  
  set   xact_abort   on  
  update   b   set   name=i.name,telphone=i.telphone  
  from   srv2.库名.dbo.author   b,inserted   i  
  where   b.id=i.id  
  go  
   
  --删除同步  
  create   trigger   tr_delete_author   on   author  
  for   delete  
  as  
  set   xact_abort   on  
  delete   b    
  from   srv2.库名.dbo.author   b,deleted   d  
  where   b.id=d.id  
  go  
   
   
   
  --3.实现同步处理的方法2,定时同步  
   
  --在srv1中创建如下的同步处理存储过程  
  create   proc   p_process    
  as  
  --更新修改过的数据  
  update   b   set   name=i.name,telphone=i.telphone  
  from   srv2.库名.dbo.author   b,author   i  
  where   b.id=i.id   and  
  (b.name<>i.name   or   b.telphone<>i.telphone)  
   
  --插入新增的数据  
  insert   srv2.库名.dbo.author(id,name,telphone)  
  select   id,name,telphone   from   author   i  
  where   not   exists(  
  select   *   from   srv2.库名.dbo.author   where   id=i.id)  
   
  --删除已经删除的数据(如果需要的话)  
  delete   b    
  from   srv2.库名.dbo.author   b  
  where   not   exists(  
  select   *   from   author   where   id=b.id)  
  go  
   
   
   
  --然后创建一个作业定时调用上面的同步处理存储过程就行了  
   
  企业管理器  
  --管理  
  --SQL   Server代理  
  --右键作业  
  --新建作业  
  --"常规"项中输入作业名称  
  --"步骤"项  
  --新建  
  --"步骤名"中输入步骤名  
  --"类型"中选择"Transact-SQL   脚本(TSQL)"  
  --"数据库"选择执行命令的数据库  
  --"命令"中输入要执行的语句:   exec   p_process    
  --确定  
  --"调度"项  
  --新建调度  
  --"名称"中输入调度名称  
  --"调度类型"中选择你的作业执行安排  
  --如果选择"反复出现"  
  --点"更改"来设置你的时间安排  
   
   
  然后将SQL   Agent服务启动,并设置为自动启动,否则你的作业不会被执行  
   
  设置方法:  
  我的电脑--控制面板--管理工具--服务--右键   SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.  
   
   
   
  上面写了两种同步方法,实际使用时,只需要其中任意一种就行了  
   
  如果数据库在同一实例中,则只需要同步处理的部分,并且将处理语句中涉及到的服务器名去掉,即只要:  
   
  库名.dbo.表名   

#3

如果只是简单的数据同步,可以用触发器来实现.下面是例子:  
   
  --测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test  
   
  --创建测试表,不能用标识列做主键,因为不能进行正常更新  
  --在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器  
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[test]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [test]  
   
  create   table   test(id   int   not   null   constraint   PK_test   primary   key  
  ,name   varchar(10))  
  go  
   
  --创建同步的触发器  
  create   trigger   t_test   on   test  
  for   insert,update,delete  
  as  
  set     XACT_ABORT   on  
  --启动远程服务器的MSDTC服务  
  exec   master..xp_cmdshell   'isql   /S"xz"   /U"sa"   /P""   /q"exec   master..xp_cmdshell   ''net   start   msdtc'',no_output"',no_output  
   
  --启动本机的MSDTC服务  
  exec   master..xp_cmdshell   'net   start   msdtc',no_output  
   
  --进行分布事务处理,如果表用标识列做主键,用下面的方法  
  BEGIN   DISTRIBUTED   TRANSACTION  
  delete   from   openrowset('sqloledb','xz';'sa';'',test.dbo.test)  
  where   id   in(select   id   from   deleted)  
  insert   into   openrowset('sqloledb','xz';'sa';'',test.dbo.test)  
  select   *   from   inserted  
  commit   tran  
  go  
   
  --插入数据测试  
  insert   into   test  
  select   1,'aa'  
  union   all   select   2,'bb'  
  union   all   select   3,'c'  
  union   all   select   4,'dd'  
  union   all   select   5,'ab'  
  union   all   select   6,'bc'  
  union   all   select   7,'ddd'  
   
  --删除数据测试  
  delete   from   test   where   id   in(1,4,6)  
   
  --更新数据测试  
  update   test   set   name=name+'_123'   where   id   in(3,5)  
   
  --显示测试的结果  
  select   *   from   test   a   full   join  
  openrowset('sqloledb','xz';'sa';'',test.dbo.test)   b   on   a.id=b.id
以上供参考，资料来自网络

#4

SQL2000和SQL2005数据库尽管类似，但表结构之间还是有差异的，是无法实现同步事务的。所以为什么两个相同的服务器可以实现同步，不同的就无法实现了。

#5

触发器+链接服务器。

--*********************************************************************************

--                 链接服务器，不同服务器数据库之间的数据操作

--*********************************************************************************
1、--创建链接服务器 
exec sp_addlinkedserver   '链接服务器名', ' ', 'SQLOLEDB', '远程服务器名或ip地址 ' 
exec sp_addlinkedsrvlogin  '链接服务器名', 'false ',null, '用户名', '密码' 


2、启动两台服务器的MSDTC服务 
MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

3、打开双方的135端口
MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。
使用“telnet IP 135”命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放


4、--如要创建触发器   
create   trigger   t_test   on   test   
for   insert,update,delete   
as 
--加上下面两句，否则会提示新事务不能登记到指定事务处理器
set    xact_abort   on  
begin  distributed   tran 
	delete   from   openrowset('sqloledb','xz';'sa';'',test.dbo.test)   
	where   id   in(select   id   from   deleted)   
	insert   into   openrowset('sqloledb','xz';'sa';'',test.dbo.test)   
	select   *   from   inserted   
commit tran

--查询示例 
select * from 链接服务器名.数据库名.dbo.表名 

--导入示例 
select * into 表 from 链接服务器名.数据库名.dbo.表名 

--以后不再使用时删除链接服务器 
exec sp_dropserver  '链接服务器名 ', 'droplogins ' 

#6

sdhdy:你好,我想请问你对于两台不同的sql数据库服务器(一台sqlserver2000,另一台sqlserver2005)，你上面提出来的解决办法也适用吗？

#7

xiaojing110：你好，根据你的回答，是不是说sql2000与sql2005数据库就不能实现同步了？

#8

我也遇到了同样的问题。在同一个服务器上是没有问题的，不管是SQL Server2005还是SQL Server2000本身。但是异地服务器就不行了，不知道楼主解决了没有？

我按照网上说的开启了相关的服务和端口（就如5楼所言的种种），得到的依然是“多步 OLE DB 操作产生错误。如果可能，请检查每个 OLE DB 状态值。没有工作被完成。” 

但是我要强调的是，如果从SQL Server2005中执行相关的 批量操作，则异地触发器能得到相应的更新。如果大师们有新的解决办法，请给我回帖：http://topic.csdn.net/u/20090824/22/4c528e38-3e5e-4529-ac12-839a8c9d012b.html 

#9

像 SyncNavigator  这样的软件都可以实现阿.自己写多麻烦.