April 22, 2012

sp_dropmergepublication (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_dropmergepublication(nvarchar @publication
, bit @ignore_distributor
, bit @reserved
, bit @ignore_merge_metadata)

MetaData:

 --   
-- Name: sp_dropmergepublication
--
-- Descriptions:
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_dropmergepublication(
@publication sysname, -- The publication name --
@ignore_distributor bit = 0,
@reserved bit = 0,
@ignore_merge_metadata bit = 0
) AS

set nocount on
--
-- Declarations.
--
declare @ad_guidname sysname
declare @pubid uniqueidentifier
declare @article sysname
declare @cmd nvarchar(255)
declare @retcode int
declare @distproc nvarchar(300)
declare @distributor sysname
declare @distribdb sysname
declare @working_dir varchar(255)
declare @working_dir_drive varchar(255)
declare @pub_dir nvarchar(255)
declare @db_name sysname
declare @implicit_transaction int
declare @close_cursor_at_commit int
declare @dynamic_filters bit
declare @alt_snapshot_folder nvarchar(255)
declare @pub_alt_snapshot_folder nvarchar(255)
,@publishingservername sysname

select @close_cursor_at_commit = 0
select @implicit_transaction = 0
,@publishingservername = publishingservername()
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0
--
-- Save setting values first before changing them
--
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

set @ad_guidname = NULL
--
-- Initializations.
--
select @db_name = db_name()

--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

if LOWER(@publication) = 'all'
BEGIN
declare hC1 CURSOR LOCAL FAST_FORWARD FOR select name
FROM dbo.sysmergepublications where UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
FOR READ ONLY

OPEN hC1
FETCH hC1 INTO @publication
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE sys.sp_dropmergepublication @publication=@publication,
@ignore_distributor = @ignore_distributor,
@reserved = 1,
@ignore_merge_metadata = @ignore_merge_metadata

FETCH hC1 INTO @publication
END
CLOSE hC1
DEALLOCATE hC1
RETURN (0)
END

if @publication IS NULL
BEGIN
RAISERROR (14003, 16, -1)
RETURN (1)
END
--
-- Get the @pubid.
--
if NOT EXISTS (select * FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name())
BEGIN
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END

select @pubid = pubid,
@dynamic_filters = @dynamic_filters,
@alt_snapshot_folder = alt_snapshot_folder,
@ad_guidname=ad_guidname
FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()

--
-- Ignore external publications
--
if exists (select * from dbo.sysmergesubscriptions where subid=@pubid
and pubid=@pubid and db_name<>db_name())
RETURN (0)

--
-- Check to make sure that there are push or pull subscriptions on the publication.
--
if EXISTS (select * FROM dbo.sysmergesubscriptions subs,
dbo.sysmergepublications pubs,
dbo.MSmerge_replinfo repinfo
WHERE pubs.name = @publication
AND UPPER(pubs.publisher)=UPPER(publishingservername())
AND pubs.publisher_db=db_name()
AND subs.pubid = pubs.pubid
AND subs.status <> 2 -- Having a deleted subscription row is fine
AND repinfo.repid <> @pubid
AND repinfo.repid = subs.subid
AND subs.subid <> subs.pubid
AND subs.subscriber_type <> 3 and subs.subscriber_type <> 2)
BEGIN
RAISERROR (14005, 16, -1)
RETURN (1)
END

exec @retcode = sys.sp_MSdropmergepalrole @pubid
if @retcode<>0 or @@error<>0
begin
return 1
end

begin tran
save TRANSACTION dropmergepublication

exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(20713, 16, -1, 'sp_dropmergepublication', @publication)
goto FAILURE

end

select @got_merge_admin_applock = 1

-- if this is the last publication and there are no other subscriptions set the ignore_merge_metadata to 1
if not exists (select 1 from dbo.sysmergesubscriptions where subid<>@pubid and sys.fn_MSmerge_islocalsubid(subid)=1)
begin
select @ignore_merge_metadata = 1
end


--
-- Delete all articles from the publication.
--

update dbo.sysmergepublications set snapshot_ready=0 where pubid=@pubid -- so that articles can be dropped
if @@ERROR<>0
goto FAILURE

EXECUTE @retcode = sys.sp_dropmergearticle @publication = @publication, @article = 'all',
@ignore_distributor = @ignore_distributor, @ignore_merge_metadata = @ignore_merge_metadata
if @@ERROR <> 0 OR @retcode <> 0
begin
RAISERROR (20040, 16, -1, @publication)
goto FAILURE
end

--
-- Delete sync task of Publication.
--
execute @retcode = sys.sp_MSdropmergepub_snapshot @publication = @publication,
@ignore_distributor = @ignore_distributor
if @@ERROR <> 0 OR @retcode <> 0
begin
RAISERROR (20010, 16, -1, @publication)
goto FAILURE
end

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

--
-- Remove my subscription entries from dbo.sysmergesubscriptions and dbo.MSmerge_replinfo
--
if exists (select * from dbo.sysmergesubscriptions where pubid = @pubid and subid <> @pubid)
begin
DELETE from dbo.MSmerge_replinfo
WHERE repid in (select subid from dbo.sysmergesubscriptions where pubid = @pubid and subid <> @pubid)
if @@ERROR <> 0
goto FAILURE

DELETE from dbo.sysmergesubscriptions WHERE pubid = @pubid and subid <> @pubid
if @@ERROR <> 0
goto FAILURE
exec sys.sp_MScleanup_subscriber_history
if @@ERROR<>0
goto FAILURE
end

--
-- Remove my own subscription from dbo.sysmergesubscriptions.
--
if exists (select * from dbo.sysmergesubscriptions where subid = @pubid)
begin
DELETE from dbo.sysmergesubscriptions WHERE subid = @pubid
if @@ERROR <> 0
goto FAILURE

exec sys.sp_MScleanup_subscriber_history @subid = @pubid
if @@ERROR<>0
goto FAILURE
end

if exists (select * from dbo.MSmerge_replinfo where repid = @pubid)
begin
DELETE from dbo.MSmerge_replinfo WHERE repid = @pubid
if @@ERROR <> 0
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 <> 7) -- 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


--
-- if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
--
if @ignore_distributor = 0
begin
--
-- Get distribution server information for remote RPC call.
--
EXEC @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT,
@directory = @working_dir OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
goto FAILURE
END

--
-- Drop the publication info from the distributor
--
select @distproc = RTRIM(@distributor) + '.' + QUOTENAME(@distribdb) +
'.dbo.sp_MSdrop_publication'
EXECUTE @retcode = @distproc
@publisher = @publishingservername,
@publisher_db = @db_name,
@publication = @publication,
@alt_snapshot_folder = @alt_snapshot_folder

if @@ERROR <> 0 OR @retcode <> 0
begin
goto FAILURE
end
end


-- Remove all dynamic snapshot jobs of this publication --
exec @retcode = sys.sp_MSdropmergedynamicsnapshotjob
@publication = @publication,
@ignore_distributor = @ignore_distributor

if @@ERROR <> 0 OR @retcode <> 0
begin
goto FAILURE
end

--
-- Execute the cleanup routine for the publication
--
exec sys.sp_MSpublicationcleanup @publisher=@publishingservername,
@publisher_db = @db_name,
@publication = @publication, @ignore_merge_metadata = @ignore_merge_metadata

if @@ERROR <> 0
goto FAILURE

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

delete from dbo.sysmergesubscriptions where pubid=@pubid
if @@ERROR<>0
goto FAILURE

exec sys.sp_MScleanup_subscriber_history
if @@ERROR<>0
goto FAILURE

-- If the only remaining subscriptions are old entries (before restore),
-- we remove them now.
if not exists (select * from dbo.sysmergesubscriptions
where status <> 7) -- 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

exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
COMMIT TRANSACTION

--
-- 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 (0)

FAILURE:
RAISERROR (14006, 16, -1)
-- UNDONE : This code is specific to 6.X nested transaction semantics --
if @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
ROLLBACK TRANSACTION dropmergepublication
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