April 18, 2012

sp_dropmergearticle (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_dropmergearticle(nvarchar @publication
, nvarchar @article
, bit @ignore_distributor
, bit @reserved
, bit @force_invalidate_snapshot
, bit @force_reinit_subscription
, bit @ignore_merge_metadata)

MetaData:

   
create procedure sys.sp_dropmergearticle(
@publication sysname, -- The publication name --
@article sysname, -- The article name --
@ignore_distributor bit = 0,
@reserved bit = 0,
@force_invalidate_snapshot bit = 0,
@force_reinit_subscription bit = 0,
@ignore_merge_metadata bit = 0
) AS

set nocount on

declare @artid uniqueidentifier
declare @snapshot_ready int
declare @objid int
declare @pubid uniqueidentifier
declare @pubidstr nvarchar(38)
declare @retcode int
declare @qualified_name nvarchar(270)
declare @filterid int
declare @proc_name sysname
declare @implicit_transaction int
declare @close_cursor_at_commit int
declare @sync_objid int
declare @view_type int
declare @type tinyint
declare @compatlevel int
declare @SCHEMA_TYPE_DROPARTICLE int
declare @automatic_reinitialization_policy bit
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

select @close_cursor_at_commit = 0
select @implicit_transaction = 0
set @SCHEMA_TYPE_DROPARTICLE= 28
--
-- 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

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

select @pubid = pubid,
@snapshot_ready=snapshot_ready,
@compatlevel = backward_comp_level,
@automatic_reinitialization_policy = automatic_reinitialization_policy
FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
BEGIN
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END

if @snapshot_ready>0
begin
if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
begin
raiserror(21379, 16, -1, @article, @publication)
return (1)
end
update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid
if @@ERROR<>0
return (1)
end

set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

--
-- Parameter Check: @article.
-- If the @article is 'all', drop all articles for the specified
-- publication (@publication).
--
if LOWER(@article) = 'all'
BEGIN
declare hC CURSOR LOCAL FAST_FORWARD FOR select DISTINCT name FROM dbo.sysmergeextendedarticlesview WHERE pubid=@pubid FOR READ ONLY

OPEN hC
FETCH hC INTO @article
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE sys.sp_dropmergearticle @publication, @article,
@ignore_distributor = @ignore_distributor,
@reserved = 1, @ignore_merge_metadata = @ignore_merge_metadata
FETCH hC INTO @article
END
CLOSE hC
DEALLOCATE hC
RETURN (0)
END

--
-- Parameter Check: @article.
-- The @article name must conform to the rules for identifiers.
--

if @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_dropmergearticle')
RETURN (1)
END

--
-- Parameter Check: @publication.
-- The @publication name must conform to the rules for identifiers.
--

if @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_dropmergearticle')
RETURN (1)
END

--
-- Ascertain the existence of the article.
--
select @type = NULL
select @type = type ,
@artid = artid,
@objid = objid
from dbo.sysmergeextendedarticlesview
where name = @article
and pubid = @pubid
if @type is NULL
begin
raiserror (20027, 16, -1, @article)
return (1)
end

-- if all articles are to be dropped, ignore this checking.
if @reserved=0 and exists (select * from dbo.sysmergesubsetfilters where pubid=@pubid and join_articlename=@article)
begin
raiserror(21421, 16, -1, @article)
return (1)
end

-- if it is a table article and the publication could have down level subscribers do not allow the drop
if (@compatlevel < 90) and @reserved = 0 and
exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1) and
not exists (select * from dbo.sysmergeschemaarticles where pubid = @pubid and name = @article)
begin
RAISERROR (21338, 16, -1, @article, @publication)
RETURN (1)
end

--
-- Delete article from dbo.sysmergearticles and clear publish bit in
-- sys.objects.
--

begin tran
save TRAN dropmergearticle

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

select @got_merge_admin_applock = 1


exec @retcode = sys.sp_MSdropmergearticle @pubid, @artid, @ignore_merge_metadata
if @@ERROR <> 0 or @retcode <> 0
goto FAILURE

if @compatlevel >= 90
begin
-- post a schema change to drop the article on the subscriber
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(2000)
declare @artidstr nvarchar(38)

set @artidstr = '''' + convert(nchar(36), @artid) + ''''

set @schemaguid = newid()
-- in this schemachange perform article cleanup and drop the article as well if this is not a republisher
-- put in SCHEMA_TYPE_DROPARTICLE
set @schemaguid = newid()
set @schematype = @SCHEMA_TYPE_DROPARTICLE
select @schematext = 'exec sp_MSdropmergearticle ' + @pubidstr + ', ' + @artidstr
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
if @schemaversion is NULL
select @schemaversion = 1
exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0 goto FAILURE
end

--
-- set the pub type to subset or full as appropriate
--
execute @retcode = sys.sp_MSsubsetpublication @publication
if @@ERROR <> 0 or @retcode <> 0
goto FAILURE

if @reserved=0
begin
-- now check if after this article has been removed the publication is still dynamically
-- filtered if it was dynamically filtered before
-- this is a lot of computation but I don't see a better way to do this
declare @can_use_partition_groups bit
declare @has_dynamic_filters bit
declare @dynamic_filters_function_list nvarchar(500)
declare @validate_subscriber_info nvarchar(500)
declare @uses_host_name bit
declare @uses_suser_sname bit
declare @dont_raise_error bit
declare @dynamic_filters bit
declare @use_partition_groups smallint
declare @partid_eval_proc nvarchar(258)

select @dont_raise_error = 1
select @dynamic_filters = dynamic_filters,
@use_partition_groups = use_partition_groups,
@partid_eval_proc = quotename(partition_id_eval_proc)
from dbo.sysmergepublications where pubid = @pubid

exec @retcode = sys.sp_MScheck_dynamic_filters @publication,
@can_use_partition_groups output,
@has_dynamic_filters output,
@dynamic_filters_function_list output,
@validate_subscriber_info output,
@uses_host_name output,
@uses_suser_sname output,
@dont_raise_error
if @@error<>0 or @retcode<>0
goto FAILURE

if @dynamic_filters = 1 and @has_dynamic_filters = 0
begin
if exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and (status=1 or status=6))
begin
if @force_reinit_subscription = 0
begin
raiserror(20697, 16, -1, @article, @publication)
goto FAILURE
end
else
begin
-- reinit all subscriptions
exec @retcode = sys.sp_MSreinitmergepublication
@publication = @publication,
@upload_first = @automatic_reinitialization_policy

if @retcode<>0 or @@ERROR<>0
goto FAILURE
end
end

update dbo.sysmergepublications
set dynamic_filters = 0, dynamic_filters_function_list = NULL
where pubid = @pubid
if @@error<>0
goto FAILURE

-- delete all schemachanges except for the ones just added.
delete from dbo.sysmergeschemachange where pubid = @pubid and schematype not in (@SCHEMA_TYPE_DROPARTICLE)
if @@error<>0
goto FAILURE
end

if @use_partition_groups in (1,2) and @can_use_partition_groups = 0
begin
if (object_id(@partid_eval_proc) is not NULL)
begin
exec ('drop procedure ' + @partid_eval_proc)
end
update dbo.sysmergepublications
set use_partition_groups = NULL,
partition_id_eval_proc = NULL
where pubid = @pubid
end
end

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

--
-- 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 (14047, 16, -1, @article)
if @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
ROLLBACK TRANSACTION dropmergearticle
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