CREATE PROCEDURE sys.sp_MSregister_all_merge_pull_subscriptions AS
begin
set nocount on
declare @publisher sysname
declare @publisher_db sysname
declare @publication sysname
declare @distributor sysname
declare @subscription_id uniqueidentifier
declare @subscription_type_id int
declare @retcode int
declare @use_web_sync int
declare @hostname sysname
declare @use_interactive_resolver int
declare @dbname sysname
if object_id(N'dbo.MSsubscription_properties', 'U') is NULL
or object_id(N'dbo.sysmergesubscriptions', 'U') is NULL
or object_id(N'dbo.sysmergesubscriptions', 'U') is NULL
or object_id(N'dbo.MSmerge_replinfo', 'U') is NULL
begin
return 0
end
select @dbname = db_name()
-- Register all merge pull subscription
DECLARE #current_sub CURSOR LOCAL FAST_FORWARD FOR
select rs.publisher,rs.publisher_db,rs.publication,p.distributor,s.subscription_type,rs.use_web_sync,rs.hostname,replinfo.use_interactive_resolver,s.subid
from MSsubscription_properties rs
join sysmergepublications p
on (UPPER(rs.publisher) = UPPER(p.publisher) and rs.publisher_db = p.publisher_db and rs.publication = p.name)
join sysmergesubscriptions s
on (UPPER(s.pubid) = UPPER(p.pubid))
join MSmerge_replinfo replinfo
on (replinfo.repid = s.subid)
where s.subscription_type <> 0 and rs.enabled_for_syncmgr <> 1
FOR READ ONLY
open #current_sub
fetch #current_sub into @publisher, @publisher_db,@publication,@distributor,@subscription_type_id,@use_web_sync,@hostname,@use_interactive_resolver,@subscription_id
while ( @@fetch_status <> -1 )
begin
if(@subscription_id is null)
select @subscription_id = newid()
exec @retcode = sys.sp_MSregistersubscription @replication_type = 2,
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = @@servername,
@subscriber_db = @dbname,
@subscriber_security_mode = 1,
@distributor = @distributor,
@distributor_security_mode = 1,
@subscription_id = @subscription_id,
@subscription_type = @subscription_type_id,
@use_web_sync = @use_web_sync,
@hostname = @hostname,
@use_interactive_resolver=@use_interactive_resolver
IF @@ERROR <> 0 or @retcode <> 0
return 1
fetch next from #current_sub into @publisher, @publisher_db,@publication,@distributor,@subscription_type_id,@use_web_sync,@hostname,@use_interactive_resolver,@subscription_id
end
close #current_sub
deallocate #current_sub
return 0
end