--用触发器即时同步两个表的实例:--测试环境: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--以下操作在本机进行
--在本机上建表及做同步处理的工作
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 identity(1,1) 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
--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
--服务器上的表(查询分析器连接到服务器上创建)
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]create table [user](id int primary key,number varchar(4),name varchar(10))
go--以下在局域网(本机操作)
--state字段为辅助更新而设置的附加字段,字段值说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO
create table [user](id int identity(1,1) primary key,number varchar(4),name varchar(10),state bit)
go
--创建触发器,维护state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=1
from [user] a join inserted b on a.id=b.id
where a.state is not null
go--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa'
go--创建同步处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_synchro]
GO
create proc p_synchro
as
/*--因为MSDTC服务(分布式事务处理需要)工作不稳定,所以下面部分为选用内容
--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 srv_lnk.test.dbo.[user]
where id not in(select id from [user]) --同步新增的数据
insert into srv_lnk.test.dbo.[user]
select id,number,name from [user] where state is null
--同步修改的数据
update srv_lnk.test.dbo.[user] set
number=b.number,name=b.name
from srv_lnk.test.dbo.[user] a
join [user] b on a.id=b.id
where b.state=1
--同步后更新本机的标志
update [user] set state=0 where isnull(state,1)=1
--COMMIT TRAN --如果启用分布式事务处理,加上此句
go--创建作业,定时执行数据同步的存储过程
if exists(SELECT 1 from msdb..sysjobs where name='数据处理')
EXECUTE msdb.dbo.sp_delete_job @job_name='数据处理'
exec msdb..sp_add_job @job_name='数据处理'--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_synchro' --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理',
@name = '时间安排',
@freq_type=4, --4 每天,8 每周,16 每月
@freq_interval=1, --作业执行的天数
@freq_subday_type=0, --是否重复执行,0x1 在指定的时间, 0x4 分钟, 0x8 小时
@freq_subday_interval=1, --重复周期
@freq_recurrence_factor=0, --重复执行,则设置为1,否则设置为0
@active_start_time = 00000 --0点开始执行--添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_name = @jobname ,
@server_name = N'(local)'
go
CREATE PROCEDURE UP_ConfigDistributor
(@publishServer varchar(50),
@distributeServer varchar(50),
@replicationDb varchar(50),
@distributeDb varchar(50),
@data_folder_path varchar(100),
-- @working_directory_path nvarchar(255),
@sUnitCode varchar(9)
)
AS
--定义分发数据库数据文件
declare @distributeDbDataFile varchar(100)
select @distributeDbDataFile = @distributeDb + '.MDF'
--定义分发数据库日志文件
declare @distributeDbLogFile varchar(100)
select @distributeDbLogFile = @distributeDb + '.LDF'
declare @log_folder_path varchar(100)
select @log_folder_path = @data_folder_path
declare @temp1 varchar(100)
select @temp1 = replace(@data_folder_path,':','$')
select @temp1 = replace(@temp1,'Data','ReplData')
declare @working_directory_path nvarchar(255)
select @working_directory_path = '\\' + host_name() + '\' + @temp1
--第一步：
--定义分发服务器(执行一次)
if not exists (select * from master..sysservers where lower(srvname) =
'repl_distributor' collate database_default)
begin
exec master..sp_adddistributor @distributor = @distributeServer , @password = N''
end
-- 添加分发数据库
IF NOT EXISTS (SELECT * from master..sysdatabases WHERE name = @distributeDb collate database_default)
begin
exec master..sp_adddistributiondb @database = @distributeDb, @data_folder = @data_folder_path, @data_file = @distributeDbDataFile, @data_file_size = 2, @log_folder = @log_folder_path, @log_file = @distributeDbLogFile, @log_file_size = 0, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 0, @login = N'sa', @password = null
end
--定义分发发布者(执行一次)
IF NOT EXISTS (SELECT *
FROM msdb..MSdistpublishers
WHERE name = @publishServer collate database_default)
begin
-- 添加分发发布者
exec master..sp_adddistpublisher @publisher = @publishServer, @distribution_db = @distributeDb, @security_mode = 1, @working_directory = @working_directory_path, @trusted = N'false', @thirdparty_flag = 0
end
--启用复制数据库
exec master..sp_replicationdboption @dbname = @replicationDb, @optname = N'merge publish', @value = N'true'GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE UP_ConfigSubscriber
(
@publicationName varchar(50),
@distributeDb varchar(50),
@subscribeServer varchar(50),
@subscribeDb varchar(50),
@subscribeLogin varchar(30),
@subscribePassword varchar(30)
)
AS
-- 添加注册的订阅服务器
exec ('select * from ' + @distributeDb + '.dbo.MSsubscriber_info')
if @@rowcount = 0
begin
exec master..sp_addsubscriber @subscriber = @subscribeServer, @type = 0, @login = @subscribeLogin, @password = @subscribePassword, @security_mode = 0, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 2, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235900, @description = N''
exec master..sp_changesubscriber_schedule @subscriber = @subscribeServer, @agent_type = 1, @active_end_date = 0
end-- 添加合并订阅
if NOT EXISTS (select db_name, srvid
FROM sysmergesubscriptions
WHERE db_name = @subscribeDb
AND srvid = (select srvid from master..sysservers where srvname = @subscribeServer collate database_default)
AND pubid = (select pubid FROM sysmergepublications
WHERE name = @publicationName) AND status <>2)
begin
exec sp_addmergesubscription @publication = @publicationName, @subscriber = @subscribeServer, @subscriber_db = @subscribeDb, @subscription_type = N'push', @subscriber_type = N'local', @subscription_priority = 0.000000, @sync_type = N'automatic', @frequency_type = 0x40, @frequency_interval = null, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @enabled_for_syncmgr = N'false', @offloadagent = 0, @use_interactive_resolver = N'false'
end
use test --test为发布数据库
-- 添加合并发布
exec sp_addmergepublication @publication = @publicationName, @description = N'', @retention = 14, @sync_mode = N'native', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @centralized_conflicts = N'true', @dynamic_filters = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_subscription_copy = N'false', @allow_synctoalternate = N'false', @add_to_active_directory = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @publication = @publicationName,@frequency_type = 8, @frequency_interval = 64, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 12300, @active_end_time_of_day = 0, @snapshot_job_name = @snapshotJobName-- 添加合并项目
exec sp_addmergearticle @publication = @publicationName, @article = N'bFZRLS', @source_owner = N'dbo', @source_object = N'bFZRLS', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = @publicationFilterCondition, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
在C#里用代码依次调用以上3个存储过程即可（UP_ConfigDistributor，UP_CreatePublication,UP_ConfigSubscriber).
UP_CreatePublication可以先用可视化创建发布，然后查看sql教本获取