IDC资讯中心
–即时同步两个表的实例:
–测试环境:sql2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test
–创建测试表,不能用标识列做主键,因为不能进行正常更新
–在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器
if exists (select * from dbo.sysobjects where id = object_id(n[test]) and objectproperty(id, nisusertable) = 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
