一、SQL SERVER数据库同步操作指南(程序间的接口) 步骤1、在HOST文件中将订阅服务器IP与计算机名(SQLSERVER实例名)绑定对应。并在ENTERPRICE MANAGER中连接远程服务器数据库。 2、创建SQLSERVER超级用户并重启SQLSERVER代理服务,指定登录的用户名为前用户。 3、创建快照文件夹,并设置相应的权限。 4、创建发布服务器及内容和快照并允许匿名订阅。 5、创建强制订阅。二、利用存储过程和代理作业定时同步数据 1、撰写相关存储过程。 Set ansi_nulls on Go CREATE proc yuancheng AS set nocount on set ansi_warnings on /*exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '192.168.16.11 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'sa ', 'mes' exec sp_dropserver 'ITSV ', 'droplogins '*/ ----由K3取数到本地表 DELETE FROM INSTORERECORD DELETE FROM PAYMENTAUDIT -----取入库数 INSERT INTO instorerecord (amount, jine, orderid, guige, unit) Select FAuxQty as shuliang,FAmount AS jine,left(FNote,charindex(',',FNote)-1) AS orderid,FInterID as guige,FEntryID as fentryid from openrowset('SQLOLEDB','192.168.16.11'; 'sa'; 'mes',AIS20021204083825.dbo.ICStockBillEntry ) where FNote like '%,%' ---------取付款数 INSERT INTO PaymentAudit (jine, fvid, orderid, feid ) SELECT FAmount as jine, FVoucherID as fvid, left(FExplanation ,charindex(',',FExplanation)-1) AS orderid,FEntryID as feid FROM openrowset('SQLOLEDB','192.168.16.11'; 'sa'; 'mes',AIS20021204083825.dbo.t_VoucherEntry) WHERE (FAccountID='1165' or FAccountID='1164' or FAccountID='1163') and FDC ='1' and FExplanation like '%,%' GO 此存储过程为从远程取数据存储到本地。 Set ansi_nulls on Go CREATE proc tongbuxin AS set nocount on set ansi_warnings on ------由本地同步到总部服务器 ---- DELETE From OPENROWSET('SQLOLEDB','192.168.16.11';'sa';'mes',wfdb_sell.dbo.orders) where factory='西部工厂' DELETE From OPENROWSET('SQLOLEDB','192.168.16.11';'sa';'mes',wfdb_sell.dbo.orddetail) where factory='西部工厂' INSERT OPENROWSET('SQLOLEDB','192.168.16.11';'sa';'mes',wfdb_sell.dbo.orders) SELECT *,'西部工厂' FROM orders INSERT OPENROWSET('SQLOLEDB','192.168.16.11';'sa';'mes',wfdb_sell.dbo.orddetail) SELECT *,'西部工厂' FROM orddetail GO 此存储过程为将本地表根据条件上传到远程服务器。 2、设置代理作业步骤、调度等。注意要启得SQLSERVER代理服务。
Sql Server数据同步
最新推荐文章于 2023-07-27 17:20:48 发布