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

/*2. 添加视图/存储过程/函数对象(默认架构dbo)*/
declare @publName		nvarchar(100)
declare @ObjectName		nvarchar(100)
declare @Type			nvarchar(30)
declare @ObjectType		nvarchar(30)
declare @SQLaddObject	nvarchar(max)
set @publName = N'tran_repl' --	【指定发布名称】
declare cur_addObject cursor local fast_forward
for select name,type from mytest.sys.objects where type in(N'P') and is_ms_shipped = 0
	union all
	select name,type from mytest.sys.objects a where type in(N'V') and is_ms_shipped = 0
	and exists(select 1 from mytest.sys.sql_modules b where a.object_id=b.object_id and is_schema_bound = 0)
	union all
	select name,N'B' as type from mytest.sys.objects a where type in(N'V') and is_ms_shipped = 0 
	and exists(select 1 from sys.sql_modules b where a.object_id=b.object_id and is_schema_bound = 1)/*架构绑定的索引视图*/
	union all
	select name,type from mytest.sys.objects where type in(N'TF',N'FN') and is_ms_shipped = 0
	order by type,name
open cur_addObject
fetch next from cur_addObject into @ObjectName,@ObjectType
while @@fetch_status = 0
begin
	SET @Type = 
	(
		CASE 
		WHEN @ObjectType = N'V' THEN N'view schema only'
		WHEN @ObjectType = N'B' THEN N'indexed view schema only'
		WHEN @ObjectType = N'P' THEN N'proc schema only'
		WHEN @ObjectType in(N'TF',N'FN') THEN N'func schema only'
		END
	)
	set @SQLaddObject = N'
	exec sp_addarticle 
	@publication = N'''+@publName+''', 
	@article = N'''+@ObjectName+''', 
	@source_owner = N''dbo'', 
	@source_object = N'''+@ObjectName+''', 
	@type =  N'''+@Type+''', 
	@description = null, 
	@creation_script = null, 
	@pre_creation_cmd = N''drop'', 
	@schema_option = 0x0000000008000001, 
	@status = 16,
	@destination_owner = N''dbo'', 
	@destination_table = N'''+@ObjectName+''''
	exec(@SQLaddObject)
	print @ObjectType+ '：' + @ObjectName
	fetch next from cur_addObject into @ObjectName,@ObjectType
end
close cur_addObject
deallocate cur_addObject

?

【创建订阅】

?

		
/***********************************【创建订阅】***************************************/
/*【要在发布服务器上运行的脚本】*/

use [mytest]

--	将订阅添加到发布并设置订阅服务器的状态
--	(警告: distribution 代理作业隐式创建，并将在 SQL Server 代理服务帐户下运行)
--	http://technet.microsoft.com/zh-cn/library/ms181702(v=sql.100).aspx
exec sys.sp_addsubscription 
@publication = N'tran_repl',	--指定发布名称
@subscriber = N'KK',		--订阅服务器
@destination_db = N'mytestA',--订阅数据库
@subscription_type = N'Push',		--推送订阅
@sync_type = N'automatic',			--默认,已发布表的架构和初始数