t_subscription = 1'
exec(@SQLarticleview)
print '已筛选:'+@tableName
end
else if ( @mark = 0 )
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)
print '无筛选:'+@tableName
end
fetch next from cur_addTable into @tableName,@mark
end
close cur_addTable
deallocate cur_addTable
/*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', --默认,已发布表的架构和初始数