Sql Server使用脚本创建分发服务及事务复制的可更新订阅(四)

?

?

--	创建项目并将其添加到发布中（在发布数据库执行）
--	http://msdn.microsoft.com/zh-cn/library/ms173857

/*1. 添加可筛选的表(默认架构dbo)*/
declare @tableName			nvarchar(100)
declare @publName			nvarchar(100)
declare @mark				bit			-- 区分是否有sid的列，有则进行筛选
declare @filterNum			nvarchar(10)-- 一个数据库多个发布加编号区别
declare @filterClause		nvarchar(100)
declare @SQLaddarticle		nvarchar(max)
declare @SQLarticlefilter	nvarchar(max)
declare @SQLarticleview		nvarchar(max)
set @publName = N'tran_repl' --	【指定发布名称】
set @filterClause = N'dbo.f_SIDTOInt(SID) % 2 = 0' --	【指定发布名称】
select @filterNum = CONVERT(NVARCHAR(10),count(*)) from distribution.dbo.MSpublications
declare cur_addTable cursor local fast_forward
for 
	/*有主键 并且 有SID列(用于筛选)*/
	select name,1 mark from sys.tables t1(nolock) where is_ms_shipped = 0
	and exists(select 1 from sys.columns t2(nolock) where t1.object_id=t2.object_id and t2.name='SID')
	and name in(select table_name from information_schema.key_column_usage(nolock) 
		where objectproperty(object_id(constraint_name),'isprimarykey')=1 )
	union all
	/*有主键 并且 无SID列(不可筛选)*/
	select name,0 mark from sys.tables t1(nolock) where is_ms_shipped = 0
	and not exists(select 1 from sys.columns t2(nolock) where t1.object_id=t2.object_id and t2.name='SID')
	and name in(select table_name from information_schema.key_column_usage (nolock)
		where objectproperty(object_id(constraint_name),'isprimarykey')=1 )
open cur_addTable
fetch next from cur_addTable into @tableName,@mark
while @@fetch_status = 0
begin
	if ( @mark = 1 ) /*可筛选的表对象*/
	begin
		set @SQLaddarticle = N'
		exec sp_addarticle 
		@publication = N'''+@publName+''', 
		@article = N'''+@tableName+''', 
		@source_owner = N''dbo'', 
		@source_object = N'''+@tableName+''', 
		@type = N''logbased'', 
		@description = null, 
		@creation_script = null, 
		@pre_creation_cmd = N''drop'', 
		@schema_option = 0x0000000008035CDF, 
		@identityrangemanagementoption = N''none'', 
		@destination_table = N'''+@tableName+''', 
		@destination_owner = N''dbo'', 
		@status = 24,
		@vertical_partition = N''false'''
		exec(@SQLaddarticle)
		/*添加项目筛选器*/
		set @SQLarticlefilter = N' 
		exec sp_articlefilter 
		@publication = N'''+@publName+''', 
		@article = N'''+@tableName+''', 
		@filter_name = N''FLTR_'+@tableName+'_'+@filterNum+'__'+rtrim(ltrim(str(@@spid)))+''', 
		@filter_clause = N'''+@filterClause+''', 
		@force_invalidate_snapshot = 1, 
		@force_reinit_subscription = 1'
		exec(@SQLarticlefilter)
		/*添加项目同步对象*/
		set @SQLarticleview = N'
		exec sp_articleview 
		@publication = N'''+@publName+''', 
		@article = N'''+@tableName+''', 
		@view_name = N''SYNC_'+@tableName+'_'+@filterNum+'__'+rtrim(ltrim(str(@@spid)))+''', 
		@filter_clause = N'''+@filterClause+''', 
		@force_invalidate_snapshot = 1, 
		@force_reini