--定時同步服務(wù)器上的數(shù)據(jù)
--例子:
--測試環(huán)境,SQLServer2000,遠(yuǎn)程服務(wù)器名:xz,用戶名為:sa,無密碼,測試數(shù)據(jù)庫:test
--服務(wù)器上的表(查詢分析器連接到服務(wù)器上創(chuàng)建)
createtable[user](idintprimarykey,numbervarchar(4),namevarchar(10))
go
--以下在局域網(wǎng)(本機(jī)操作)
--本機(jī)的表,state說明:null表示新增記錄,1表示修改過的記錄,0表示無變化的記錄
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[user]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[user]
GO
createtable[user](idintidentity(1,1),numbervarchar(4),namevarchar(10),statebit)
go
--創(chuàng)建觸發(fā)器,維護(hù)state字段的值
createtriggert_stateon[user]
afterupdate
as
update[user]setstate=1
from[user]ajoininsertedbona.id=b.id
wherea.stateisnotnull
go
--為了方便同步處理,創(chuàng)建鏈接服務(wù)器到要同步的服務(wù)器
--這里的遠(yuǎn)程服務(wù)器名為:xz,用戶名為:sa,無密碼
ifexists(select1frommaster..sysserverswheresrvname='srv_lnk')
execsp_dropserver'srv_lnk','droplogins'
go
execsp_addlinkedserver'srv_lnk','','SQLOLEDB','xz'
execsp_addlinkedsrvlogin'srv_lnk','false',null,'sa'
go
--創(chuàng)建同步處理的存儲過程
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_synchro]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_synchro]
GO
createprocp_synchro
as
--setXACT_ABORTon
--啟動遠(yuǎn)程服務(wù)器的MSDTC服務(wù)
--execmaster..xp_cmdshell'isql/S"xz"/U"sa"/P""/q"execmaster..xp_cmdshell''netstartmsdtc'',no_output"',no_output
--啟動本機(jī)的MSDTC服務(wù)
--execmaster..xp_cmdshell'netstartmsdtc',no_output
--進(jìn)行分布事務(wù)處理,如果表用標(biāo)識列做主鍵,用下面的方法
--BEGINDISTRIBUTEDTRANSACTION
--同步刪除的數(shù)據(jù)
deletefromsrv_lnk.test.dbo.[user]
whereidnotin(selectidfrom[user])
--同步新增的數(shù)據(jù)
insertintosrv_lnk.test.dbo.[user]
selectid,number,namefrom[user]wherestateisnull
--同步修改的數(shù)據(jù)
updatesrv_lnk.test.dbo.[user]set
number=b.number,name=b.name
fromsrv_lnk.test.dbo.[user]a
join[user]bona.id=b.id
whereb.state=1
--同步后更新本機(jī)的標(biāo)志
update[user]setstate=0whereisnull(state,1)=1
--COMMITTRAN
go
--創(chuàng)建作業(yè),定時執(zhí)行數(shù)據(jù)同步的存儲過程
ifexists(SELECT1frommsdb..sysjobswherename='數(shù)據(jù)處理')
EXECUTEmsdb.dbo.sp_delete_job@job_name='數(shù)據(jù)處理'
execmsdb..sp_add_job@job_name='數(shù)據(jù)處理'
--創(chuàng)建作業(yè)步驟
declare@sqlvarchar(800),@dbnamevarchar(250)
select@sql='execp_synchro'--數(shù)據(jù)處理的命令
,@dbname=db_name()--執(zhí)行數(shù)據(jù)處理的數(shù)據(jù)庫名
execmsdb..sp_add_jobstep@job_name='數(shù)據(jù)處理',
@step_name='數(shù)據(jù)同步',
@subsystem='TSQL',
@database_name=@dbname,
@command=@sql,
@retry_attempts=5,--重試次數(shù)
@retry_interval=5--重試間隔
--創(chuàng)建調(diào)度
EXECmsdb..sp_add_jobschedule@job_name='數(shù)據(jù)處理',
@name='時間安排',
@freq_type=4,--每天
@freq_interval=1,--每天執(zhí)行一次
@active_start_time=00000--0點(diǎn)執(zhí)行
go