May 11, 2012

sp_MSdropmergearticle (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_MSdropmergearticle(uniqueidentifier @pubid
, uniqueidentifier @artid
, bit @ignore_merge_metadata)

MetaData:

 create procedure sys.sp_MSdropmergearticle(@pubid uniqueidentifier, @artid uniqueidentifier, @ignore_merge_metadata bit = 0)  
as
declare @snapshot_ready int
declare @objid int
declare @retcode int
declare @qualified_name nvarchar(517)
declare @filterid int
declare @proc_name nvarchar(258)
declare @sync_objid int
declare @view_type int
declare @type tinyint
declare @tablenick int
declare @drop_downlevel_procs bit
declare @preserve_rowguidcol bit
declare @viewname nvarchar(258)
declare @SCHEMA_TYPE_DDL_ACTIONS int
declare @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP int
declare @SCHEMA_TYPE_NONSQLALTERTABLE int

set @SCHEMA_TYPE_DDL_ACTIONS= 300
set @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP= 301
set @SCHEMA_TYPE_NONSQLALTERTABLE= 13

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

select @objid = NULL
select @type = type, @objid = objid from dbo.sysmergeextendedarticlesview where artid = @artid AND pubid = @pubid
-- nothing to drop
if @objid is NULL return 0

exec sys.sp_MSget_qualified_name @objid, @qualified_name OUTPUT
if @qualified_name is null return 1

--
-- Mark all entries in sysmergeschemachange with schematype
-- SCHEMA_TYPE_USER_SCHEMA 31
-- SCHEMA_TYPE_USER_DEFINED_DATA_TYPE
-- SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE
-- SCHEMA_TYPE_ASSEMBLY
-- SCHEMA_TYPE_PARTITIONSCHEME
-- SCHEMA_TYPE_PARTITIONFUNCTION
-- SCHEMA_TYPE_XMLSCHEMANAMESPACE
-- SCHEMA_TYPE_FULLTEXTCATALOG
-- SCHEMA_TYPE_USER_DEFINED_TABLE_TYPE
-- as inactive.
-- These entries will either be deleted or activated when snapshot runs depending on whether
-- they are in the dependency list of the articles still remaining in the publication.
--

update dbo.sysmergeschemachange set schemastatus = 0 where pubid = @pubid AND schematype in (88, 89, 90, 91, 92, 93, 96, 31, 105)
if @@ERROR <> 0 return 1

-- Remove the corresponding rows from dbo.sysmergeschemachange
-- However, keep schemachanges like alter table; otherwise, the following will
-- fail for msgbased:
-- 1) After having synched, add column.
-- 2) Drop article, then readd it.
-- 3) DML at pub (new schema) then at sub (old schema).
-- 4) Sub requests reinit with upload.
-- 5) As a result, the upload cannot be applied due to table schema mismatch;
-- however, there will be no schemaonly message, as the DDL schemachange is gone.
DELETE FROM dbo.sysmergeschemachange
WHERE artid = @artid AND pubid = @pubid and schematype not in (@SCHEMA_TYPE_DDL_ACTIONS, @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP, @SCHEMA_TYPE_NONSQLALTERTABLE)
if @@ERROR <> 0 return 1

-- this code is only needed if the article was added incrementally
delete from dbo.sysmergeschemachange
where substring(convert(binary(16), artid),5,4) = convert(binary(4), @objid) and
substring(convert(binary(16), artid),0,5) = 0x00000000
and pubid = @pubid

--
-- Removing a schema only article is a lot simpler than
-- removing a table article so a different code path is created
-- to handle this.
--
if @type in (0x20, 0x40, 0x80, 0xA0)
begin
--
-- Remove the corresponding record in dbo.sysmergeschemaarticles
--
delete dbo.sysmergeschemaarticles where artid = @artid and pubid = @pubid

--
-- drop the system pre snapshot script schema chnage since it could contain the
-- the schema only article (in case of view and functions) if this is the last
-- article of type view or func. A subsequent snapshot
-- run will anyway regenerate the system pre snapshot script
--
if not exists (select * from dbo.sysmergeschemaarticles where pubid = @pubid and (type = 0x40 or type = 0x80 or type = 0xA0))
begin
delete from dbo.sysmergeschemachange where pubid = @pubid and schematype = 60
end

--
-- If this is the last schema only article for the underlying
-- view or proc object, unmark the 0x200 bit in sysobject.replinfo
--
if not exists (select * from dbo.sysmergeschemaarticles
where objid = @objid)
begin
exec %%Object(MultiName = @qualified_name).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
-- exec %%Object(MultiName = @qualified_name).LockExclusiveMatchID(ID = @objid)
if @@error <> 0
return 1

if object_id('sysmergeschemaarticles') is NULL
begin
exec %%Object(ID = @objid).SetSchemaPublished(Value = 0)
if @@error <> 0
return 1
end
else if not exists (select * from sysmergeschemaarticles
where objid = @objid)
begin
exec %%Object(ID = @objid).SetSchemaPublished(Value = 0)
if @@error <> 0
return 1
end
end
end
else
begin
--
-- Retrieve the object id of the underlying table.
--
select @sync_objid = sync_objid,
@view_type = view_type,
@artid = artid,
@objid = objid,
@tablenick = nickname
from dbo.sysmergearticles where artid = @artid AND pubid = @pubid

--
-- If this is the last article that refers to the base table, drop the
-- triggers and stored procs
--
if NOT exists (select * from dbo.sysmergearticles WHERE artid = @artid AND pubid <> @pubid)
begin
-- set the identity column as not for replication before calling article cleanup
-- so that article cleanup can reseed the table to have the highest identity value
declare @colname sysname

-- Acquire sch-M lock up-front on the published object
exec %%Object(MultiName = @qualified_name).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
-- exec %%Object(MultiName = @qualified_name).LockExclusiveMatchID(ID = @objid)
if @@error <> 0
return 1

select @colname = name
from sys.columns
where object_id = @objid and
is_identity = 1 and -- is identity
ColumnProperty(object_id, name, 'IsIdNotForRepl') = 1 -- 'not for repl' property
if @colname is not null
begin
-- Mark 'not for repl'
EXEC %%ColumnEx(ObjectID = @objid, Name = @colname).SetIdentityNotForRepl(Value = 0)
IF @@ERROR <> 0
return 1
end

--
-- Cleanup the triggers and stored procs
--
EXECUTE @retcode = sys.sp_MSarticlecleanup @artid = @artid, @pubid = @pubid, @ignore_merge_metadata = @ignore_merge_metadata
if @@ERROR <> 0 OR @retcode <> 0
BEGIN
return 1
END

--
-- Clear the replication bit in sys.objects. Now merge and transactional level
-- uses different replication bit, checking transactional level is not needed.
--

exec %%Relation(ID = @objid).SetMergePublished(Value = 0, SetColumns = 0)
-- rmak: How about the merge published bits in sys.columns??

-- Delete pending requests for resending rows.
delete from dbo.MSmerge_metadataaction_request where tablenick=@tablenick

IF @@ERROR <> 0
return 1
end
else
begin

-- reset the trigger only if this article is shared with other publications and also the snapshot is ready
if exists (select 1
from dbo.sysmergepublications pub join dbo.sysmergearticles art
on pub.pubid = art.pubid
where pub.snapshot_ready = 1
and pub.pubid <> @pubid
and UPPER(publisher) = UPPER(publishingservername()) and publisher_db = DB_NAME())
begin
exec @retcode = sys.sp_MSaddmergetriggers @qualified_name
if @retcode<>0 or @@ERROR<>0 return (1)
end

-- Always drop the article proc's they are not shared among publications --

select @drop_downlevel_procs = 0
if (sys.fn_MSmerge_islocalpubid(@pubid) = 1)
begin
if exists (select 1 from dbo.sysmergepublications where pubid = @pubid and backward_comp_level<90)
select @drop_downlevel_procs = 1
end

EXECUTE @retcode = sys.sp_MSdroparticleprocs @artid = @artid, @pubid = @pubid
if @@ERROR <> 0 OR @retcode <> 0
begin
return 1
end

-- Drop the article-specific conflict table.
exec @retcode= sys.sp_MSdrop_article_conflict_table @pubid=@pubid, @artid=@artid
if @@error<>0 or @retcode<>0 return 1

-- If the article's has a temporary ( view type = 2) or a permanent view (view_type = 1 ) drop the sync object --
if (@objid <> @sync_objid)
begin
select @viewname = sys.objects.name from sys.objects where type='V' and is_ms_shipped = 1
and object_id = @sync_objid
if @viewname IS NOT NULL
begin
set @viewname = QUOTENAME(@viewname)
exec ('drop view ' + @viewname)
if @@ERROR<>0
return 1
end
end

-- Drop repl_view_
select @viewname = NULL
select @viewname = object_name(repl_view_id) from dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if object_id(@viewname) is not NULL
begin
set @viewname = QUOTENAME(@viewname)
exec ('drop view ' + @viewname)
select @viewname = NULL
end

-- if this article is published in another publication do not drop the identity constraint
-- do not delete the publisher entry from MSmerge_identity_range. Update the pubid
-- with the other pubid that exists
if object_id('dbo.MSmerge_identity_range', 'U') is not NULL
begin
exec @retcode = sys.sp_MSremoveidrangesupport @pubid, @artid, 0 -- @propagate_ddl_change --
IF @@ERROR <> 0 or @retcode <> 0
return 1
end


end
--
-- Remove the row from dbo.sysmergearticles.
--
-- drop the expand procs, membership eval proc and logical record views before removing
-- the row from sysmergepartitioninfo
-- do not worry about the errors here since this is just for cleanup
select @viewname = NULL
select @viewname = object_name(logical_record_view) from dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if object_id(@viewname) is not NULL
begin
set @viewname = QUOTENAME(@viewname)
exec ('drop view ' + @viewname)
select @viewname = NULL
end
select @proc_name = NULL
select @proc_name = quotename(membership_eval_proc_name) from dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if object_id(@proc_name) is not NULL
begin
exec ('drop proc ' + @proc_name)
select @proc_name = NULL
end
select @proc_name = quotename(expand_proc) from dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if object_id(@proc_name) is not NULL
begin
exec ('drop proc ' + @proc_name)
select @proc_name = NULL
end

DELETE FROM dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if @@ERROR <> 0
BEGIN
return 1
END
DELETE FROM dbo.sysmergearticles WHERE artid = @artid AND pubid = @pubid
if @@ERROR <> 0
BEGIN
return 1
END


-- delete all the filter components that are defined upon the designated article --
select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters where
artid = @artid AND pubid = @pubid
while (@filterid is not null)
begin
select @proc_name = expand_proc from dbo.sysmergesubsetfilters where
artid = @artid AND pubid = @pubid and join_filterid = @filterid

if (@proc_name IS NOT NULL) and exists (select * from sys.objects where
name = @proc_name and type = 'P')
begin
set @proc_name= quotename(@proc_name)
exec ('drop proc ' + @proc_name)
IF @@ERROR <> 0
return 1
end
delete from dbo.sysmergesubsetfilters where
artid = @artid AND pubid = @pubid and join_filterid = @filterid
IF @@ERROR <> 0
return 1
select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters where
artid = @artid AND pubid = @pubid
end
end

No comments:

Post a Comment

Total Pageviews