1
--
定时同步服务器上的数据
2
--
例子:
3
--
测试环境,SQL Server2000,远程服务器名:sp,用户名为:sa,无密码,测试数据库:test
4
--
服务器上的表(查询分析器连接到服务器上创建)
5
create
table
[
user
]
(id
int
primary
key
,
number
varchar
(
4
),name
varchar
(
10
))
6
go
7
--
以下在局域网(本机操作)
8
--
本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
9
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[user]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
10
drop
table
[
user
]
11
GO
12
create
table
[
user
]
(id
int
identity
(
1
,
1
),
number
varchar
(
4
),name
varchar
(
10
),state
bit
)
13
go
14
--
创建触发器,维护state字段的值
15
create
trigger
t_state
on
[
user
]
16
after
update
17
as
18
update
[
user
]
set
state
=
1
19
from
[
user
]
a
join
inserted b
on
a.id
=
b.id
20
where
a.state
is
not
null
21
go
22![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
23
--
为了方便同步处理,创建链接服务器到要同步的服务器
24
--
这里的远程服务器名为:sp,用户名为:sa,无密码
25
if
exists
(
select
1
from
master..sysservers
where
srvname
=
'
srv_lnk
'
)
26
exec
sp_dropserver
'
srv_lnk
'
,
'
droplogins
'
27
go
28
exec
sp_addlinkedserver
'
srv_lnk
'
,
''
,
'
SQLOLEDB
'
,
'
xz
'
29
exec
sp_addlinkedsrvlogin
'
srv_lnk
'
,
'
false
'
,
null
,
'
sa
'
30
go
31![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
32
--
创建同步处理的存储过程
33
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[p_synchro]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
34
drop
procedure
[
dbo
]
.
[
p_synchro
]
35
GO
36
create
proc
p_synchro
37
as
38
--
set XACT_ABORT on
39
--
启动远程服务器的MSDTC服务
40
--
exec master..xp_cmdshell 'osql /S"sp" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output
41
42
--
启动本机的MSDTC服务
43
--
exec master..xp_cmdshell 'net start msdtc',no_output
44
45
--
进行分布事务处理,如果表用标识列做主键,用下面的方法
46
--
BEGIN DISTRIBUTED TRANSACTION
47
--
同步删除的数据
48
delete
from
srv_lnk.test.dbo.
[
user
]
49
where
id
not
in
(
select
id
from
[
user
]
)
50![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
51
--
同步新增的数据
52
insert
into
srv_lnk.test.dbo.
[
user
]
53
select
id,
number
,name
from
[
user
]
where
state
is
null
54
55
--
同步修改的数据
56
update
srv_lnk.test.dbo.
[
user
]
set
57
number
=
b.
number
,name
=
b.name
58
from
srv_lnk.test.dbo.
[
user
]
a
59
join
[
user
]
b
on
a.id
=
b.id
60
where
b.state
=
1
61
62
--
同步后更新本机的标志
63
update
[
user
]
set
state
=
0
where
isnull
(state,
1
)
=
1
64
--
COMMIT TRAN
65
go
66![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
67
--
创建作业,定时执行数据同步的存储过程
68
if
exists
(
SELECT
1
from
msdb..sysjobs
where
name
=
'
数据处理
'
)
69
EXECUTE
msdb.dbo.sp_delete_job
@job_name
=
'
数据处理
'
70
exec
msdb..sp_add_job
@job_name
=
'
数据处理
'
71![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
72
--
创建作业步骤
73
declare
@sql
varchar
(
800
),
@dbname
varchar
(
250
)
74
select
@sql
=
'
exec p_synchro
'
--
数据处理的命令
75
,
@dbname
=
db_name
()
--
执行数据处理的数据库名
76
77
exec
msdb..sp_add_jobstep
@job_name
=
'
数据处理
'
,
78
@step_name
=
'
数据同步
'
,
79
@subsystem
=
'
TSQL
'
,
80
@database_name
=
@dbname
,
81
@command
=
@sql
,
82
@retry_attempts
=
5
,
--
重试次数
83
@retry_interval
=
5
--
重试间隔
84
85
--
创建调度
86
EXEC
msdb..sp_add_jobschedule
@job_name
=
'
数据处理
'
,
87
@name
=
'
时间安排
'
,
88
@freq_type
=
4
,
--
每天
89
@freq_interval
=
1
,
--
每天执行一次
90
@active_start_time
=
00000
--
0点执行
91
go
92
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
2
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
3
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
4
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
5
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
6
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
7
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
8
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
9
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
10
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
11
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
12
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
13
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
14
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
15
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
16
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
17
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
18
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
19
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
20
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
21
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
22
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
23
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
24
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
25
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
26
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
27
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
28
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
29
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
30
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
31
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
32
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
33
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
34
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
35
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
36
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
37
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
38
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
39
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
40
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
41
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
42
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
43
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
44
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
45
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
46
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
47
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
48
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
49
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
50
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
51
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
52
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
53
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
54
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
55
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
56
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
57
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
58
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
59
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
60
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
61
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
62
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
63
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
64
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
65
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
66
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
67
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
68
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
69
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
70
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
71
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
72
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
73
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
74
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
75
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
76
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
77
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
78
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
79
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
80
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
81
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
82
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
83
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
84
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
85
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
86
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
87
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
88
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
89
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
90
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
91
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
92
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)