Sql Server数据同步

一、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代理服务。