sqlserver 2008 通过 实现发布订阅

新建发布订阅

1. 在源服务器新建发布

CREATE PROCEDURE [dbo].[app_publish] 
    @name varchar(50),
    @db_name varchar(50),
    @login_name varchar(20),
    @password varchar(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
-- 添加事务发布
exec sp_replicationdboption @dbname = @db_name, @optname = N'publish', @value = N'true'

exec sp_addpublication @publication = @name, @description = '数据库的事务发布。', 
        @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', 
        @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, 
        @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', 
        @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', 
        @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', 
        @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

exec sp_addpublication_snapshot @publication = @name, @frequency_type = 4, @frequency_interval = 1, 
        @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 2, 
        @frequency_subday_interval = 10, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, 
        @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, 
        @publisher_login = @login_name, @publisher_password = @password

declare @table_name varchar(50);
declare @ins_cmd varchar(50),
        @del_cmd varchar(50),
        @upd_cmd varchar(50);
-- 创建一个游标
declare cursor_table cursor scroll for select table_name from app_table;
-- 打开游标
open cursor_table;
-- 获取第一条记录
fetch first from cursor_table into @table_name;
-- 遍历表名, 执行添加订阅协议
while(@@FETCH_STATUS = 0)
begin
    set @ins_cmd = N'CALL sp_MSins_dbo'+@table_name;
    set @del_cmd = N'CALL sp_MSdel_dbo'+@table_name;
    set @upd_cmd = N'SCALL sp_MSupd_dbo'+@table_name;
    exec sp_addarticle @publication = @name, @article = @table_name, @source_owner = N'dbo', @source_object = @table_name, 
            @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', 
            @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = @table_name, 
            @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = @ins_cmd, @del_cmd = @del_cmd, @upd_cmd = @upd_cmd;
    fetch next from cursor_table into @table_name;
end
-- 关闭游标
close cursor_table;
-- 删除游标
deallocate cursor_table;
END

2. 新建推送订阅

CREATE PROCEDURE [dbo].[app_subscription] 
    -- Add the parameters for the stored procedure here
    @subscriber varchar(30),
    @publication varchar(20),
    @destination_db varchar(20),
    @subscriber_login varchar(20),
    @subscriber_password varchar(20)
    
AS
BEGIN
    declare @start_date varchar(20);
    set @start_date = convert(varchar(8),GETDATE(),112); 
    exec sp_addsubscription @publication = @publication, @subscriber = @subscriber, @destination_db = @destination_db, 
        @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', 
        @subscriber_type = 0
    exec sp_addpushsubscription_agent @publication = @publication, @subscriber = @subscriber, 
        @subscriber_db = @destination_db, @job_login = null, @job_password = null, @subscriber_security_mode = 0, 
        @subscriber_login = @subscriber_login, @subscriber_password = @subscriber_password, @frequency_type = 64, @frequency_interval = 0, 
        @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, 
        @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, 
        @active_start_date = @start_date, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', 
        @dts_package_location = N'Distributor'
END

删除发布订阅

---- 删除事务发布 ---
EXEC sp_droppublication @publication = 'all';

---- 删除订阅 ----
EXEC sp_dropsubscription 
  @publication = 'uuu', 
  @article = N'all',
  @subscriber = 'WHY';
  
exec  sp_subscription_cleanup @publisher='PC-201311211015\SHICH',@publisher_db = 'erp',@publication = 'uuu'