April 22, 2012

sp_dropmergepullsubscription (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_dropmergepullsubscription(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db
, bit @reserved)

MetaData:

 create procedure sys.sp_dropmergepullsubscription(  
@publication sysname = NULL, -- Publication name --
@publisher sysname = NULL, -- Publisher server --
@publisher_db sysname = NULL, -- Publication database --
@reserved bit = 0
)AS

SET NOCOUNT ON

--
-- Declarations.
--

declare @retcode int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
declare @local_db sysname
declare @merge_jobid binary(16)
declare @job_step_uid uniqueidentifier
declare @cmd nvarchar(255)
declare @pubidstr nvarchar(38)
declare @subscriber sysname
declare @subscriber_db sysname
declare @subscriber_type int
declare @local_job bit
declare @implicit_transaction int
declare @close_cursor_at_commit int
declare @owner_sid varbinary(85)
declare @owner_name sysname
declare @qualified_publication_name nvarchar(512)
declare @subscriber_type_anon tinyint
declare @REPLICA_STATUS_BeforeRestore tinyint

set @subscriber_type_anon= 3
set @REPLICA_STATUS_BeforeRestore= 7

select @close_cursor_at_commit = 0
select @implicit_transaction = 0

--
-- Get original setting values before setting them to false for recursive calling
--
IF (@reserved = 0)
BEGIN
SELECT @implicit_transaction = @@options & 2
SELECT @close_cursor_at_commit = @@options & 4
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
END

--
-- Security Check.
--
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@ERROR <> 0 or @retcode <> 0
return(1)

--
-- Initializations.
--
set @local_db = DB_NAME()
set @subscriber = @@SERVERNAME
set @subscriber_db = DB_NAME()

--
-- Assign parameter values appropriately
--
IF object_id('dbo.sysmergesubscriptions', 'U') is null
BEGIN
RAISERROR (14055, 16, -1)
RETURN (1)
END

--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is defined
--
IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_dropmergepullsubscription')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @publisher
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)

--
-- Parameter Check: @publisher_db
--
IF @publisher_db IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher_db', 'sp_dropmergepullsubscription')
RETURN (1)
END

--
-- Parameter Check: @publication.
-- If the publication name is specified, check to make sure that it
-- conforms to the rules for identifiers and that the publication
-- actually exists. Disallow NULL.
--
if @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_dropmergepullsubscription')
RETURN (1)
END

IF LOWER(@publication) = 'all'
BEGIN
declare hC1 CURSOR LOCAL FAST_FORWARD FOR
select DISTINCT name FROM dbo.sysmergepublications
FOR READ ONLY
OPEN hC1
FETCH hC1 INTO @publication
if @@fetch_status = -1
begin
CLOSE hC1
DEALLOCATE hC1
RETURN (0) -- - It's OK to have no publication when 'ALL'
end
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE sys.sp_dropmergepullsubscription @publication = @publication,
@publisher = @publisher,
@publisher_db = @publisher_db,
@reserved = 1
FETCH hC1 INTO @publication
END
CLOSE hC1
DEALLOCATE hC1
RETURN (0)
END

IF LOWER(@publisher) = 'all'
BEGIN
declare hC4 CURSOR LOCAL FAST_FORWARD FOR
select DISTINCT publisher FROM dbo.sysmergepublications
FOR READ ONLY
OPEN hC4
FETCH hC4 INTO @publisher
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE sys.sp_dropmergepullsubscription @publication = @publication,
@publisher = @publisher,
@publisher_db = @publisher_db,
@reserved = 1
FETCH hC4 INTO @publisher
END
CLOSE hC4
DEALLOCATE hC4
RETURN (0)
END

--
-- Validate that the publisher is a valid server
--
--
select @publisher_srvid = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
IF @publisher_srvid IS NULL
BEGIN
RAISERROR (14080, 16, -1, @publisher)
RETURN (1)
END
--

-- Previously the condition is set as 'AND subid<>pubid' which is fatally errorous --
IF LOWER(@publisher_db) = 'all'
BEGIN
declare hC5 CURSOR LOCAL FAST_FORWARD FOR
select DISTINCT publisher_db
FROM dbo.sysmergepublications
WHERE upper(publisher collate SQL_Latin1_General_CP1_CS_AS)=upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) and
name=@publication
FOR READ ONLY

OPEN hC5
FETCH hC5 INTO @publisher_db
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE sys.sp_dropmergepullsubscription @publication = @publication,
@publisher = @publisher,
@publisher_db = @publisher_db,
@reserved = 1
FETCH hC5 INTO @publisher_db
END
CLOSE hC5
DEALLOCATE hC5
RETURN (0)
END

--
-- return error if only there is no 'ALL'. Same is true for the rest of error handling.
--

select @pubid= pubid from dbo.sysmergepublications
where name = @publication and
upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db = @publisher_db
if @pubid is null
BEGIN
if @reserved = 0
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END

set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
--
-- Only members of the sysadmin group and the creator of the distribution
-- agent can drop a pull subscription successfully. This behavior matches
-- the behavior of the sysjobs_view. DBO of the subscriber database,
-- sysadmins (owner is undefined) can drop a subscription if the owner_sid
-- is null.
--

EXEC sys.sp_MSget_mergepullsubsagent_owner @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@owner_sid = @owner_sid OUTPUT

IF (@owner_sid is not null AND
(SUSER_SID() <> @owner_sid) AND
(ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0))
BEGIN
SELECT @owner_name = SUSER_SNAME(@owner_sid)
SELECT @qualified_publication_name = @publisher + N':' +
@publisher_db + N':' +
@publication
RAISERROR(21121,16,-1,@owner_name, @qualified_publication_name)
RETURN (1)
END

--
-- Get subscriptions from either local replicas or global replicas
--
--
select @subid = subs1.subid, @subscriber_type = subs1.subscriber_type from
dbo.sysmergesubscriptions subs1,
dbo.sysmergesubscriptions subs2,
dbo.sysmergepublications pubs
where subs1.srvid = @subscriber_srvid
and subs1.db_name = @subscriber_db
and subs2.srvid = @publisher_srvid
and subs2.db_name = @publisher_db
and subs1.pubid = subs2.subid
and subs2.pubid = pubs.pubid
and pubs.name = @publication
and upper(pubs.publisher collate SQL_Latin1_General_CP1_CS_AS)=upper(@publisher collate SQL_Latin1_General_CP1_CS_AS)
and pubs.publisher_db=@publisher_db
--
select @subid = subid from dbo.sysmergesubscriptions
where pubid = @pubid and pubid <> subid and db_name = @subscriber_db and UPPER(subscriber_server) = UPPER(@subscriber)
if @subid IS NULL
begin
if @reserved = 0
raiserror (14050, 16, -1)
RETURN (0)
end

exec @retcode = sys.sp_resetsnapshotdeliveryprogress @drop_table = N'true'
if @@error <> 0 or @retcode <> 0
begin
return (1)
end

begin tran
save TRAN dropmergepullsubscription

--
-- Drop the local merge task
--
select @merge_jobid = merge_jobid from dbo.MSmerge_replinfo WHERE repid = @subid

if (@merge_jobid IS NOT NULL)
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @merge_jobid)
BEGIN
SELECT @job_step_uid = job_step_uid
FROM MSsubscription_properties
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND publication = @publication
AND publication_type = 2

-- Checks if the job name matches one that is generated
-- by replication
EXEC @retcode = sys.sp_MSispullmergejobnamegenerated
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@job_id = @merge_jobid
IF @@ERROR <> 0
GOTO FAILURE

-- Only drop the job if the name was generated
IF @retcode = 0
BEGIN
EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @merge_jobid,
@job_step_uid = @job_step_uid
IF @@ERROR <> 0 or @retcode <> 0
GOTO FAILURE
END
END
END

if @subid <> @pubid
BEGIN
DELETE dbo.MSmerge_replinfo WHERE repid = @subid
IF @@ERROR <> 0
GOTO FAILURE

-- delete supportability settings for the subscriptions that we are about to delete.
delete from dbo.MSmerge_supportability_settings where subid = @subid
delete from dbo.MSmerge_log_files where subid = @subid

delete from dbo.sysmergesubscriptions where subid = @subid
if @@ERROR <> 0
GOTO FAILURE

exec sys.sp_MScleanup_subscriber_history @subid=@subid
if @@ERROR <> 0
GOTO FAILURE

-- Call sp_MSunregistersubscription so that the reg entries get deleted --
exec @retcode = sys.sp_MSunregistersubscription @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = @@SERVERNAME,
@subscriber_db = @subscriber_db
IF @retcode<>0 or @@ERROR<>0
GOTO FAILURE

exec @retcode= sys.sp_MSpublicationcleanup @publisher=@publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@force_preserve_rowguidcol = 0
IF @retcode<>0 or @@ERROR <> 0
BEGIN
RAISERROR (20025, 16, -1, @publication)
GOTO FAILURE
END

-- If the only remaining subscriptions are old entries (before restore),
-- we remove them now.
if not exists (select * from dbo.sysmergesubscriptions
where status <> @REPLICA_STATUS_BeforeRestore)
begin
delete from dbo.sysmergesubscriptions
truncate table dbo.MSmerge_supportability_settings
truncate table dbo.MSmerge_log_files
truncate table dbo.MSrepl_errors
truncate table dbo.MSmerge_history
truncate table dbo.MSmerge_articlehistory
truncate table dbo.MSmerge_sessions
delete from dbo.MSmerge_replinfo
end
END

IF object_id('MSsubscription_properties', 'U') is not NULL
BEGIN
DELETE FROM MSsubscription_properties
WHERE upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) AND
publisher_db = @publisher_db AND
publication = @publication

IF @@ERROR <> 0
GOTO FAILURE

IF NOT EXISTS (SELECT * FROM MSsubscription_properties)
BEGIN
exec @retcode = sys.sp_MSsub_cleanup_prop_table
IF @@ERROR <> 0 or @retcode <> 0
GOTO FAILURE
END
END

COMMIT TRAN
--
-- Set back original settings
--
IF @reserved = 0
BEGIN
declare @whattodrop int -- 1=hws, 2=lws, 3=both
set @whattodrop= 0

if not exists (select * from dbo.sysmergesubscriptions
where subscription_type <> 3 and
status <> @REPLICA_STATUS_BeforeRestore)
begin
set @whattodrop= 1
end

if not exists (select * from dbo.sysmergesubscriptions
where subscription_type = 3 and
status <> @REPLICA_STATUS_BeforeRestore)
begin
set @whattodrop= @whattodrop + 2
end

--
-- If last subscription is dropped and the DB is not enabled for publishing,
-- then remove the merge system tables
--
if 0 <> @whattodrop and
0 = (select category & 4 FROM master.dbo.sysdatabases WHERE name = DB_NAME())
begin
execute @retcode= sys.sp_MSdrop_mergesystables @whattodrop=@whattodrop
if @@ERROR <> 0 or @retcode <> 0 return (1)

execute @retcode=sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'
if @@ERROR <> 0 or @retcode <> 0 goto FAILURE
end

IF @implicit_transaction <>0
SET IMPLICIT_TRANSACTIONS ON
IF @close_cursor_at_commit <>0
SET CURSOR_CLOSE_ON_COMMIT ON
END

-- note that here we will ignore any errors.
EXEC sys.sp_dropreplsymmetrickey @check_replication = 1, @throw_error = 0

RETURN(0)

FAILURE:
RAISERROR (14056, 16, -1)
if @@trancount > 0
begin
ROLLBACK TRANSACTION dropmergepullsubscription
COMMIT TRANSACTION
end
--
-- Set back original settings
--
IF @reserved = 0
BEGIN
IF @implicit_transaction <>0
SET IMPLICIT_TRANSACTIONS ON
IF @close_cursor_at_commit <>0
SET CURSOR_CLOSE_ON_COMMIT ON
END
return 1

No comments:

Post a Comment

Total Pageviews