--服务器上的表(查询分析器连接到服务器上创建)
--state字段为辅助更新而设置的附加字段,字段值说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[luser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [luser]
GO
create table [luser](id int identity(1,1) primary key,[user] varchar(4),pwd varchar(10),address varchar(50),state bit)
go
--创建触发器,维护state字段的值
create trigger t_state on [luser]
after update
as
update [luser] set state=1
from [luser] a join inserted b on a.id=b.id
where a.state is not null
go
----以下在局域网(本机操作)
if exists (select * from dbo.sysobjects where id = object_id(N'[luser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [luser]
create table [luser](id int primary key,[user] varchar(4),pwd varchar(10),address varchar(50))
go
--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名:rserver,用户名为:ruser,密码:rpwd
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','rserver'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'ruser','rpwd'
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"rserver" /U"ruser" /P"rpwd" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output
--进行分布事务处理,如果表用标识列做主键,用下面的方法
--BEGIN DISTRIBUTED TRANSACTION
--*/
--同步删除的数据
delete from [luser]
where id not in(select id from srv_lnk.test.dbo.[luser])
--同步新增的数据
insert into [luser]
select id,[user],pwd,address from srv_lnk.test.dbo.[luser]
where state is null
--同步修改的数据
update [luser] set
[user]=b.[user],pwd=b.pwd,address=b.address
from [luser] a
join srv_lnk.test.dbo.[user] b on a.id=b.id
where b.state=1
--同步后更新服务器上的标志
update srv_lnk.test.dbo.[luser] 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='数据处理'
可以的,参考:sp_addlinkedserver (T-SQL)
Creates a linked server, which allows access to distributed, heterogeneous queries against OLE DB data sources. After creating a linked server with sp_addlinkedserver, this server can then execute distributed queries. If the linked server is defined as SQL Server, remote stored procedures can be executed.
Syntax
Is the name of the linked server to create. server is sysname, with no default.
[@srvproduct =] 'product_name'
Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name, data_source, location, provider_string, and catalog do not need to be specified.
[@provider =] 'provider_name'
Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL. The OLE DB provider is expected to be registered with the given PROGID in the registry.
[@datasrc =] 'data_source'
Is the name of the data source as interpreted by the OLE DB provider. data_source is nvarchar(4000), with a default of NULL. data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.
[@location =] 'location'
Is the location of the database as interpreted by the OLE DB provider. location is nvarchar(4000), with a default of NULL. location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.
[@provstr =] 'provider_string'
Is the OLE DB provider-specific connection string that identifies a unique data source. provider_string is nvarchar(4000), with a default of NULL. provstr is passed as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.
[@catalog =] 'catalog'
Is the catalog to be used when making a connection to the OLE DB provider. catalog is sysname, with a default of NULL. catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider.