May 7, 2012

sp_MSchange_mergearticle (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_MSchange_mergearticle(uniqueidentifier @pubid
, uniqueidentifier @artid
, nvarchar @property
, nvarchar @value
, int @value_numeric)

MetaData:

 create procedure sys.sp_MSchange_mergearticle (  
@pubid uniqueidentifier,
@artid uniqueidentifier,
@property sysname = NULL,
@value nvarchar(2000) = NULL,
@value_numeric int= null
) AS

set nocount on

declare @artidstr nvarchar(38)
declare @pubidstr nvarchar(38)
declare @value_str nvarchar(270)
declare @artnick int
declare @retcode int
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(4000)
declare @publication sysname
declare @publisher sysname
declare @subscriber sysname
declare @subscriber_db sysname
declare @pubid_iter uniqueidentifier
declare @subid_iter uniqueidentifier
declare @islightweight bit
declare @publishes_to_any bit
declare @publishes_to_non_global bit
declare @qualified_name nvarchar(520)
declare @objid int
declare @SCHEMA_TYPE_COMPENSATE_FOR_ERRORS int
declare @SCHEMA_TYPE_UPLOADOPTIONS int
declare @SCHEMA_TYPE_CHANGE_MERGE_ARTICLE_90RTM int
declare @previoustinyintvalue tinyint
declare @upload_first nvarchar(10)

-- Security check
if 1 <> is_member('db_owner')
begin
RAISERROR (15247, 11, -1)
return (1)
end

set @SCHEMA_TYPE_COMPENSATE_FOR_ERRORS= 24
set @SCHEMA_TYPE_UPLOADOPTIONS= 26
set @SCHEMA_TYPE_CHANGE_MERGE_ARTICLE_90RTM = 303

--
-- Parameter Check: @property.
-- Check to make sure that @property is a valid property
--
if @property IS NULL OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT in
('partition_options',
'processing_order',
'published_in_tran_pub',
'compensate_for_errors',
'subscriber_upload_options',
'stream_blob_columns')
begin
raiserror (21259, 16, -1, @property)
return (1)
end

select top 1 @islightweight= lightweight
from dbo.sysmergearticles where artid=@artid

if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'partition_options'
begin
if @value_numeric NOT IN (0, 1, 2, 3)
BEGIN
RAISERROR (22526, 16, -1, '@partition_options')
return 1
END

--
-- Update the syssubsetdefintions table with the new column tracking.
--
update dbo.sysmergepartitioninfo set partition_options = @value_numeric
where artid = @artid and pubid = @pubid
if @@ERROR <> 0 return 1

if @value_numeric in (2, 3)
begin
if 1=@islightweight
begin
update dbo.sysmergearticles set well_partitioned_lightweight= 1
where artid=@artid and pubid=@pubid
if @@ERROR <> 0 return 1
end

select top 1 @publication= name
from dbo.sysmergepublications
where pubid= @pubid
exec @retcode = sys.sp_MSvalidate_wellpartitioned_articles @publication
if @@error <> 0 or @retcode <> 0 return 1
end
else
begin
if 1=@islightweight
begin
update dbo.sysmergearticles set well_partitioned_lightweight= 0
where artid=@artid and pubid=@pubid
if @@ERROR <> 0 return 1
end
end
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'processing_order'
begin

declare @processing_order int
select @processing_order = convert(int, @value)

update dbo.sysmergearticles set processing_order = @processing_order
where artid = @artid and pubid = @pubid
if @@error<>0
return 1
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'published_in_tran_pub'
begin
declare @published_in_tran_pub_bit bit

-- Check to make sure that we have a true/false. --
if lower(@value collate SQL_Latin1_General_CP1_CS_AS) not in ('true', 'false')
begin
raiserror (14148, 16, -1, 'published_in_tran_pub')
return 1
end

-- Determine the bit value. --
if lower(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
begin
set @published_in_tran_pub_bit = 1
end
else
set @published_in_tran_pub_bit = 0

-- Update the subscription with the new 'published_in_tran_pub' value. --
update dbo.sysmergearticles set published_in_tran_pub = @published_in_tran_pub_bit
where artid = @artid and pubid = @pubid
if @@error <> 0
begin
raiserror (14053, 16, -1)
return 1
end
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'compensate_for_errors'
begin
update dbo.sysmergearticles set compensate_for_errors = @value_numeric
where artid = @artid
if @@error<>0 return 1

-- Insert a schemachange for all publications the article belongs to.
set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications
where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db = db_name() and
pubid in (select pubid from dbo.sysmergearticles
where artid=@artid)
order by pubid asc)

while @pubid_iter is not null
begin
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
if @schemaversion is NULL
set @schemaversion = 1
set @schemaguid = newid()

set @schematype = @SCHEMA_TYPE_COMPENSATE_FOR_ERRORS
select @schematext = 'exec dbo.sp_MSchange_mergearticle @pubid=''' + convert(nchar(36), @pubid_iter) + ''', @artid=''' + convert(nchar(36), @artid) + ''', @property=''compensate_for_errors'', @value_numeric=''' + cast(@value_numeric as nchar(1)) + ''''

exec @retcode = sys.sp_MSinsertschemachange @pubid_iter, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR <> 0 OR @retcode <> 0 return 1

set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications
where pubid > @pubid_iter and
upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db = db_name() and
pubid in (select pubid from dbo.sysmergearticles
where artid=@artid)
order by pubid asc)
end
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'stream_blob_columns'
begin
update dbo.sysmergearticles set stream_blob_columns = @value_numeric
where artid = @artid
if @@error<>0 return 1

-- Insert a schemachange for all publications the article belongs to.
set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications
where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db = db_name() and
pubid in (select pubid from dbo.sysmergearticles
where artid=@artid)
order by pubid asc)

while @pubid_iter is not null
begin
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
if @schemaversion is NULL
set @schemaversion = 1
set @schemaguid = newid()

set @schematype = @SCHEMA_TYPE_CHANGE_MERGE_ARTICLE_90RTM

select @schematext = 'exec dbo.sp_MSchange_mergearticle @pubid=''' + convert(nchar(36), @pubid_iter) + ''', @artid=''' + convert(nchar(36), @artid) + ''', @property=''stream_blob_columns'', @value_numeric=''' + cast(@value_numeric as nchar(1)) + ''''

exec @retcode = sys.sp_MSinsertschemachange @pubid_iter, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR <> 0 OR @retcode <> 0 return 1

set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications
where pubid > @pubid_iter and
upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db = db_name() and
pubid in (select pubid from dbo.sysmergearticles
where artid=@artid)
order by pubid asc)
end
end
else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'subscriber_upload_options'
begin
set @publishes_to_any= 0
set @publishes_to_non_global= 0

select top 1 @artnick= nickname, @previoustinyintvalue = upload_options from dbo.sysmergearticles where artid = @artid
if @@error<>0 return 1

update dbo.sysmergearticles set upload_options= @value_numeric where artid = @artid

-- Propagate the schemachange if this replica has its own publications, and the article
-- belongs to them.
set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications where
upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername()) and
publisher_db = db_name() and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid)
order by pubid asc)

while @pubid_iter is not null
begin
set @publishes_to_any= 1
set @schematext= 'exec sp_MSchange_mergearticle @pubid=''' + cast(@pubid_iter as nchar(36)) + ''', @artid=''' + cast(@artid as nchar(36)) + ''', @property=''subscriber_upload_options'', @value_numeric=' + cast(@value_numeric as nchar(1))
select @schemaversion= isnull(max(schemaversion), 0) + 1 from dbo.sysmergeschemachange
set @schemaguid= newid()
set @schematype= @SCHEMA_TYPE_UPLOADOPTIONS

exec @retcode=sys.sp_MSinsertschemachange @pubid_iter, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0 return 1

set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications where
pubid > @pubid_iter and
upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername()) and
publisher_db = db_name() and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid)
order by pubid asc)
end

if 1=@publishes_to_any
begin
if exists (select * from dbo.sysmergesubscriptions where
subscriber_type<>1 and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid) and
pubid in (select pubid from dbo.sysmergepublications where
upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername()) and
publisher_db = db_name()))
begin
set @publishes_to_non_global= 1
end
end

if 1=@publishes_to_non_global
begin
if 0=@value_numeric
begin
-- Non-global subscribers subscribe to this replica for publications
-- that contain the article which becomes updateable:
-- Set the generation of all rows to 0, so that data and metadata will be resent to the
-- non-global subscribers. The data will also be resent to other replicas, but this is
-- still better than having to reinitialize the non-global subscribers.

update dbo.MSmerge_tombstone set generation= 0 where tablenick = @artnick
update dbo.MSmerge_contents set generation= 0 where tablenick = @artnick
update dbo.MSmerge_past_partition_mappings set generation= 0 where tablenick = @artnick
end
else if ((@previoustinyintvalue = 0) and (@value_numeric <> 0))
begin
-- If there are non-global subscriptions to publications that contain this article
-- that is changing its upload_options, they need to be reinitialized,
-- because the subscribers might have data that are not uploaded yet. This data
-- may no longer get uploaded after the change to upload_options, which would cause
-- non-convergence.

set @subid_iter= (select top 1 subid from dbo.sysmergesubscriptions
where subscriber_type<>1 and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid) and
(upper(subscriber_server collate SQL_Latin1_General_CP1_CS_AS) <> upper(publishingservername()) or
db_name <> db_name())
order by subid asc)

while @subid_iter is not null
begin
select @subscriber= subscriber_server, @subscriber_db= db_name
from dbo.sysmergesubscriptions
where subid=@subid_iter

select top 1
@publication= name,
@upload_first = case automatic_reinitialization_policy
when 1 then 'true'
else 'false'
end
from dbo.sysmergepublications
where pubid= (select pubid from dbo.sysmergesubscriptions
where subid=@subid_iter)

exec @retcode= sys.sp_reinitmergesubscription
@publication=@publication,
@subscriber= @subscriber,
@subscriber_db= @subscriber_db,
@upload_first= @upload_first

if @@error<>0 or @retcode<>0 return 1

set @subid_iter= (select top 1 subid from dbo.sysmergesubscriptions
where subid>@subid_iter and
subscriber_type<>1 and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid) and
(upper(subscriber_server collate SQL_Latin1_General_CP1_CS_AS) <> upper(publishingservername()) or
db_name <> db_name())
order by subid asc)
end
end
end
else if exists (select * from dbo.sysmergesubscriptions where
subscriber_type<>1 and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid) and
upper(subscriber_server collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername()) and
db_name = db_name())
begin
-- The replica is a non-global subscriber to publications that contain this article.

if 0 <> @value_numeric
begin
select top 1 @artnick= nickname
from dbo.sysmergearticles where artid=@artid

-- Delete row metadata.
if 1=@islightweight
begin
delete from dbo.MSmerge_rowtrack where tablenick = @artnick
end
else
begin
delete from dbo.MSmerge_tombstone where tablenick = @artnick
delete from dbo.MSmerge_contents where tablenick = @artnick
delete from dbo.MSmerge_past_partition_mappings where tablenick = @artnick
end
end

-- Recreate triggers and procs for all publications the replica subscribed to, and which
-- contain this article.


select top 1 @objid= objid from dbo.sysmergearticles where artid=@artid

exec @retcode= sys.sp_MSget_qualified_name
@object_id= @objid,
@qualified_name= @qualified_name output
if @@error<>0 or @retcode<>0 return 1

set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications where
pubid in (select pubid from dbo.sysmergearticles where artid=@artid)
order by pubid asc)

while @pubid_iter is not null
begin
exec @retcode= sys.sp_MSResetTriggerProcs
@qual_source_object= @qualified_name,
@pubid= @pubid_iter
if @@error<>0 or @retcode<>0 return 1

set @pubid_iter= (select top 1 pubid from dbo.sysmergepublications where
pubid > @pubid_iter and
pubid in (select pubid from dbo.sysmergearticles where artid=@artid)
order by pubid asc)
end
end -- upload_options
end

return 0

No comments:

Post a Comment

Total Pageviews