create procedure sys.sp_MSrestore_sub_merge
(
@subscriber_security_mode int,
@subscriber_login sysname,
@subscriber_password nvarchar(524),
@distributor_security_mode int,
@distributor_login sysname,
@distributor_password sysname,
@publisher_security_mode int,
@publisher_login sysname,
@publisher_password sysname,
@job_login nvarchar(257),
@job_password sysname
)
AS
SET NOCOUNT ON
declare @retcode int
declare @detached int
declare @attached int
DECLARE @publisher sysname /* Expression used in the cursor */
DECLARE @publisher_db sysname /* Expression used in the cursor */
DECLARE @publication sysname /* Expression used in the cursor */
DECLARE @distributor sysname /* Expression used in the cursor */
declare @subnickname binary(6)
declare @subid uniqueidentifier
declare @detached_subid uniqueidentifier
declare @subscriber_server sysname
declare @subscriber_db sysname
declare @enabled_for_syncmgr_int int
declare @enabled_for_syncmgr nvarchar(5)
declare @pubid uniqueidentifier, @prev_pubid uniqueidentifier
declare @REPLICA_STATUS_BeforeRestore tinyint
-- Initialization
select @retcode = 0
select @detached = 3
select @attached = 4
select @subscriber_server = @@SERVERNAME
select @subscriber_db = db_name()
set @REPLICA_STATUS_BeforeRestore= 7
if object_id('sysmergesubscriptions') is not NULL
begin
set @publication= null
select top 1 @publication = p.name from dbo.sysmergepublications p, dbo.sysmergesubscriptions s
where p.allow_subscription_copy = 0 and p.pubid = s.subid and s.db_name = @subscriber_db and
UPPER(s.subscriber_server) = UPPER(@subscriber_server)
if @publication is not null
BEGIN
RAISERROR (21205, 16, -1, @publication)
RETURN (1)
END
end
else
return 0
DECLARE hCdetachedsubscriptions CURSOR LOCAL FAST_FORWARD FOR
SELECT subid from dbo.sysmergesubscriptions where status = @detached
FOR READ ONLY
-- Open a cursor for all subscriptions that have a status of 'detached'
OPEN hCdetachedsubscriptions
FETCH hCdetachedsubscriptions INTO @detached_subid
WHILE (@@fetch_status <> -1)
BEGIN
/* Generate a guid for the Subscriber ID */
set @subid = newid()
/* Look for existing nickname from any other subscription */
exec sys.sp_MSgetreplnick NULL, NULL , NULL, @subnickname out
if @@ERROR <> 0 goto Cleanup
/* Generate a new replica nickname from the @subid */
if (@subnickname is null)
begin
EXECUTE sys.sp_MSgenreplnickname
@srcguid= @subid,
@replnick= @subnickname output
if @@ERROR <> 0 goto Cleanup
end
/*
** Touch up existing rows in dbo.sysmergesubscriptions & dbo.MSmerge_replinfo.
** Set status of attached subsription as "attached"
*/
update dbo.sysmergesubscriptions
set subid = @subid,
subscriber_server = @@servername,
db_name = @subscriber_db,
status = @attached,
replnickname = @subnickname
where subid = @detached_subid
if @@ERROR <> 0 goto Cleanup
update dbo.MSmerge_replinfo
set repid = @subid,
login_name = suser_sname(suser_sid()),
merge_jobid = NULL
where repid = @detached_subid
if @@ERROR <> 0 goto Cleanup
-- update identity range table with new subid and also with no identity information
-- for that subid so that a new one can be issued on the first merge.
if object_id('MSmerge_identity_range') is not NULL
begin
update dbo.MSmerge_identity_range
set range_begin = NULL,
range_end = NULL,
next_range_begin = NULL,
next_range_end = NULL,
max_used = NULL,
subid = @subid
where subid = @detached_subid
if @@ERROR <> 0 goto Cleanup
exec @retcode = sys.sp_MScreate_zero_identity_constraint @subid
if @retcode<>0 or @@error<>0
goto Cleanup
end
FETCH hCdetachedsubscriptions INTO @detached_subid
END
close hCdetachedsubscriptions
deallocate hCdetachedsubscriptions
-- Check for expired subscriptions.
exec @retcode= sys.sp_MScheckforexpiredmergesubscriptions
@mark_expired_as_failed_attach=1
if @@error<>0 or @retcode<>0 return 1
-- The open local generations from the detach-database are again the open generations in the
-- newly attached database. We will change the guidsrc of these open generations since they
-- are now really different generations.
-- However the nickname of the detach-database is still
-- in the nicknames list. This needs to be replaced by the nickname that was newly created
-- for the detach-database, otherwise with message based the detach-database will never
-- receive changes the attach-database puts into such a generation.
if (object_id('dbo.MSmerge_genhistory','U')) is not null
begin
update dbo.MSmerge_genhistory
set nicknames= @subnickname,
guidsrc = newid()
where genstatus=0
if @@ERROR <> 0 goto Cleanup
end
if object_id('sysmergearticles') is not NULL
begin
if exists (select * from dbo.sysmergearticles)
begin
select @pubid = NULL
select top 1 @pubid = pubid from dbo.sysmergepublications
order by pubid
while @pubid is not null
begin
exec @retcode = sys.sp_MSregenerate_mergetriggersprocs @pubid=@pubid
if @@error <> 0 or @retcode <> 0
goto Cleanup
select @prev_pubid = @pubid
select @pubid = NULL
select top 1 @pubid = pubid from dbo.sysmergepublications
where pubid > @prev_pubid
order by pubid
end
end
end
/* Add pull subscription agents only if MSsubscription_properties exists */
if object_id('MSsubscription_properties') is not NULL
AND (@job_login IS NOT NULL
OR @job_password IS NOT NULL
OR @subscriber_security_mode != 1
OR @subscriber_login IS NOT NULL
OR @subscriber_password IS NOT NULL
OR @publisher_security_mode != 1
OR @publisher_login IS NOT NULL
OR @publisher_password IS NOT NULL
OR @distributor_security_mode != 1
OR @distributor_login IS NOT NULL
OR @distributor_password IS NOT NULL)
begin
-- Add the agents for pull or anonymous subscriptions.
DECLARE hCmergesubscription CURSOR LOCAL FAST_FORWARD FOR
SELECT publisher, publisher_db, publication, distributor, enabled_for_syncmgr
FROM dbo.MSsubscription_properties
-- only do this for merge subscriptions
where publication_type = 2
--where publication is not NULL and
-- publication <> N'' and
-- subscription_type <> @push
FOR READ ONLY
OPEN hCmergesubscription
FETCH hCmergesubscription INTO @publisher,
@publisher_db, @publication, @distributor, @enabled_for_syncmgr_int
WHILE (@@fetch_status <> -1)
BEGIN
/* Enable the subscription to be synchronized via SyncMgr if the original one was */
if @enabled_for_syncmgr_int = 0
set @enabled_for_syncmgr = 'false'
else
set @enabled_for_syncmgr = 'true'
EXECUTE @retcode = sys.sp_addmergepullsubscription_agent
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@distributor = @distributor,
@subscriber_security_mode = @subscriber_security_mode,
@subscriber_login = @subscriber_login,
@subscriber_password = @subscriber_password,
@publisher_security_mode = @publisher_security_mode,
@publisher_login = @publisher_login,
@publisher_password = @publisher_password,
@distributor_security_mode = @distributor_security_mode,
@distributor_login = @distributor_login,
@distributor_password = @distributor_password,
@job_login = @job_login,
@job_password = @job_password,
@frequency_type = 2 , /* 2== OnDemand */
@enabled_for_syncmgr = @enabled_for_syncmgr
if @@error <> 0 or @retcode <> 0
begin
select @retcode = 1
goto Cleanup
end
FETCH hCmergesubscription INTO @publisher,
@publisher_db, @publication, @distributor, @enabled_for_syncmgr_int
END
close hCmergesubscription
deallocate hCmergesubscription
end
else
begin
/*
** Check to see if MSsubscription_properties table exists.
** If not, create it.
*/
exec @retcode = sys.sp_MScreate_sub_tables_internal
@tran_sub_table = 0,
@property_table = 1,
@sqlqueue_table = 0
if @@error <> 0 or @retcode <> 0 goto Cleanup
end
return 0
Cleanup:
return 1
