定时同步服务器上的数据的例子:
测试环境SQL Server远程服务器名:xz用户名为:sa无密码测试数据库:test
服务器上的表(查询分析器连接到服务器上创建)
create table [user](id int primary keynumber varchar()name varchar())
go
以下在局域网(本机操作)
本机的表state说明:null 表示新增记录 表示修改过的记录 表示无变化的记录
if exists (select * from dbosysobjects where id = object_id(N[user]) and OBJECTPROPERTY(id NIsUserTable) = )
drop table [user]
GO
create table [user](id int identity()number varchar()name varchar()state bit)
go
创建触发器维护state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=
from [user] a join inserted b on aid=bid
where astate is not null
go
为了方便同步处理创建链接服务器到要同步的服务器
这里的远程服务器名为:xz用户名为:sa无密码
if exists(select from mastersysservers where srvname=srv_lnk)
exec sp_dropserver srv_lnkdroplogins
go
exec sp_addlinkedserver srv_lnkSQLOLEDBxz
exec sp_addlinkedsrvlogin srv_lnkfalsenullsa
go
创建同步处理的存储过程
if exists (select * from dbosysobjects where id = object_id(N[dbo][p_synchro]) and OBJECTPROPERTY(id NIsProcedure) = )
drop procedure [dbo][p_synchro]
GO
create proc p_synchro
as
set XACT_ABORT on
启动远程服务器的MSDTC服务
exec masterxp_cmdshell isql /Sxz /Usa /P /qexec masterxp_cmdshell net start msdtcno_outputno_output
启动本机的MSDTC服务
exec masterxp_cmdshell net start msdtcno_output
进行分布事务处理如果表用标识列做主键用下面的方法
BEGIN DISTRIBUTED TRANSACTION
同步删除的数据
delete from srv_lnktestdbo[user]
where id not in(select id from [user])
同步新增的数据
insert into srv_lnktestdbo[user]
select idnumbername from [user] where state is null
同步修改的数据
update srv_lnktestdbo[user] set
number=bnumbername=bname
from srv_lnktestdbo[user] a
join [user] b on aid=bid
where bstate=
同步后更新本机的标志
update [user] set state= where isnull(state)=
COMMIT TRAN
go
创建作业定时执行数据同步的存储过程
if exists(SELECT from msdbsysjobs where name=数据处理)
EXECUTE msdbdbosp_delete_job @job_name=数据处理
exec msdbsp_add_job @job_name=数据处理
创建作业步骤
declare @sql varchar()@dbname varchar()
select @sql=exec p_synchro 数据处理的命令
@dbname=db_name() 执行数据处理的数据库名
exec msdbsp_add_jobstep @job_name=数据处理
@step_name = 数据同步
@subsystem = TSQL
@database_name=@dbname
@command = @sql
@retry_attempts = 重试次数
@retry_interval = 重试间隔
创建调度
EXEC msdbsp_add_jobschedule @job_name = 数据处理
@name = 时间安排
@freq_type = 每天
@freq_interval = 每天执行一次
@active_start_time = 点执行
