SQLServer 事务复制中使用脚本添加某个对象的发布

原文:SQLServer 事务复制中使用脚本添加某个对象的发布

--	use [发布库]

--添加表:创建项目并将其添加到发布中

exec sp_addarticle

@publication = N'ReplicationName',

@article = N'MyObject',

@source_owner = N'dbo',

@source_object = N'MyObject',

@destination_owner = N'dbo',

@destination_table = N'MyObject',

@schema_option = 0x0000004008037FDF, --项目属性(自参考)

@status = 0,

@description = N'', --default

@type = N'logbased', --default

@creation_script = null, --default

@pre_creation_cmd = N'drop', --default

@vertical_partition = N'false', --default

@identityrangemanagementoption = N'manual',  --default

@force_invalidate_snapshot = 1,

@ins_cmd = N'CALL sp_MSins_dboMyObject',

@del_cmd = N'CALL sp_MSdel_dboMyObject',

@upd_cmd = N'SCALL sp_MSupd_dboMyObject'

go

--添加视图

exec sp_addarticle

@publication = N'ReplicationName',

@article = N'v_myview',

@source_owner = N'dbo',

@source_object = N'v_myview',

@destination_owner = N'dbo',

@destination_table = N'v_myview',

@type = N'view schema only',

@description = null,

@creation_script = null,

@pre_creation_cmd = N'drop',

@schema_option = 0x0000000008000001,

@force_invalidate_snapshot = 1

go

--添加存储过程

exec sp_addarticle

@publication = N'ReplicationName',

@article = N'usp_myproc',

@source_owner = N'dbo',

@source_object = N'usp_myproc',

@destination_owner = N'dbo',

@destination_table = N'usp_myproc',

@type = N'proc schema only',

@description = null,

@creation_script = null,

@pre_creation_cmd = N'drop',

@schema_option = 0x0000000008000001,

@force_invalidate_snapshot = 1

go

--添加函数

exec sp_addarticle

@publication = N'ReplicationName',

@article = N'f_myfunc',

@source_owner = N'dbo',

@source_object = N'f_myfunc',

@destination_owner = N'dbo',

@destination_table = N'f_myfunc',

@type = N'func schema only',

@description = null,

@creation_script = null,

@pre_creation_cmd = N'drop',

@schema_option = 0x0000000008000001,

@force_invalidate_snapshot = 1

go

/*

添加视图/存储过程/函数 主要是 @type 不一样:

@type = N'view schema only', --视图

@type = N'proc schema only', --存储过程

@type = N'func schema only', --函数

*/

--对其请求订阅中的新项目的订阅添加到发布中

exec sp_refreshsubscriptions @publication = N'ReplicationName'

go

--启动快照代理

exec sys.sp_startpublication_snapshot @publication = N'ReplicationName'

go

/*

--移除发布项目(即取消某个对象的同步)

exec sp_dropsubscription @publication = N'ReplicationName',@article = N'MyObject',@subscriber = N'all'

go

exec sp_droparticle @publication = N'ReplicationName',@article = N'MyObject',@force_invalidate_snapshot = 0

go

*/