April 27, 2012

sp_helpmergearticle (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_helpmergearticle(nvarchar @publication
, nvarchar @article)

MetaData:

 create procedure sys.sp_helpmergearticle (  
@publication sysname = '%', -- The publication name --
@article sysname = '%' -- The article name --
) AS

SET NOCOUNT ON

--
-- Declarations.
--
declare @retcode int
declare @max_nickname int
--
-- Create a temporary table to hold all information.
--
declare @helpmergearticle TABLE
(
id int identity NOT NULL,
name sysname collate database_default not null,
source_owner sysname collate database_default not null,
source_object sysname collate database_default not null, -- converted from objid --
sync_object_owner sysname collate database_default null,
sync_object sysname collate database_default null, -- converted from sync_objid --
description nvarchar(255) collate database_default null,
status tinyint NULL,
creation_script nvarchar(255) collate database_default null,
conflict_table nvarchar(270) collate database_default null,
article_resolver nvarchar(255) collate database_default null,
subset_filterclause nvarchar(1000) collate database_default null,
pre_creation_command tinyint NULL,
schema_option binary(8) NULL,
type smallint NULL,
column_tracking int NULL,
resolver_info nvarchar(517) collate database_default null,
vertical_partition bit NULL,
destination_owner sysname collate database_default null,
identity_support int NULL,
pub_identity_range bigint NULL,
identity_range bigint NULL,
threshold int NULL,
verify_resolver_signature int NULL default 1,
destination_object sysname collate database_default not null,
allow_interactive_resolver int NULL,
fast_multicol_updateproc int NULL,
check_permissions int NULL,
processing_order int NOT NULL default 0,
upload_options tinyint NOT NULL default 0,
identityrangemangementoption int not NULL default 0,
delete_tracking bit default 1,
compensate_for_errors bit not null default 0,
partition_options tinyint not null default 0,
artid uniqueidentifier NULL,
pubid uniqueidentifier NULL,
stream_blob_columns bit NOT NULL default 0
)


--
-- Running sp_help is OK from everywhere, whether enabled for publishing or not
--
IF object_id('sysmergesubscriptions') is NULL
RETURN (0)

--
-- Parameter Check: @publication.
-- Check to make sure that the publication exists, that it conforms
-- to the rules for identifiers, and that it isn't NULL.
--

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

if @publication <> '%'
BEGIN
if (is_member('db_owner') = 1 or is_member('replmonitor') = 1)
begin
if NOT EXISTS (select pubid 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
end
else
begin
if NOT EXISTS (select pubid FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername())
and publisher_db=db_name() and ({fn ISPALUSER(pubid)} = 1))
BEGIN
select * from @helpmergearticle
RETURN (0)
END
end
END

--
-- Parameter Check: @article.
-- Check to make sure that the article exists, that it conforms
-- to the rules for identifiers, and that it isn't NULL.
--

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

if @article <> '%'
BEGIN
if NOT EXISTS (select *
FROM dbo.sysmergeextendedarticlesview
WHERE name = @article
AND pubid IN (select pubid
FROM dbo.sysmergepublications
WHERE name like @publication
and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()))
BEGIN
RAISERROR (20027, 16, -1, @article)
RETURN (1)
END

END

select @max_nickname = max(nickname) from dbo.sysmergearticles

INSERT INTO @helpmergearticle
(name,
source_owner,
source_object,
sync_object_owner,
sync_object,
description,
status,
creation_script,
conflict_table,
pre_creation_command,
schema_option,
type,
column_tracking,
article_resolver,
subset_filterclause,
resolver_info,
vertical_partition,
destination_owner,
identity_support,
pub_identity_range,
identity_range,
threshold,
verify_resolver_signature,
destination_object,
allow_interactive_resolver,
fast_multicol_updateproc,
check_permissions,
processing_order,
upload_options,
identityrangemangementoption,
delete_tracking,
compensate_for_errors,
partition_options,
artid,
pubid,
stream_blob_columns)
select art.name,
users1.name,
objects.name,
users2.name,
syncobjects.name,
art.description,
art.status,
art.creation_script,
art.conflict_table,
art.pre_creation_command,
art.schema_option,
case
when objectproperty(art.objid, 'IsSchemaBound') = 1 and art.type <> 0x80 then 0x0100 | convert(smallint, art.type)
else convert(smallint, ISNULL(art.type,0x0a))
end,
art.column_tracking,
art.article_resolver,
art.subset_filterclause,
art.resolver_info,
art.vertical_partition,
art.destination_owner,
art.identity_support,
ABS(art.pub_range),
ABS(art.range),
art.threshold,
art.verify_resolver_signature,
art.destination_object,
art.allow_interactive_resolver,
art.fast_multicol_updateproc,
art.check_permissions,
art.processing_order,
art.upload_options,
case when art.identity_support = 1 then 1 else 0 end,
art.delete_tracking,
art.compensate_for_errors,
0,
art.artid,
art.pubid,
art.stream_blob_columns
FROM sysmergeextendedarticlesview art
inner join dbo.sysmergepublications pubs on art.pubid = pubs.pubid
inner join sys.objects objects on objects.object_id = art.objid
left outer join sys.objects syncobjects on art.sync_objid = syncobjects.object_id
inner join sys.schemas users1 on objects.schema_id = users1.schema_id
left outer join sys.schemas users2 on syncobjects.schema_id = users2.schema_id
WHERE art.name LIKE @article
AND pubs.name LIKE @publication
AND UPPER(pubs.publisher) = UPPER(publishingservername())
AND pubs.publisher_db = db_name()
and (1 = {fn ISPALUSER(pubs.pubid)} or
1 = is_member('db_owner') or
1 = isnull(is_member('replmonitor'),0))
order by art.processing_order, isnull(art.nickname, @max_nickname+1), art.name

update hma set hma.partition_options = 3
from @helpmergearticle hma join dbo.sysmergearticles sma
on hma.artid = sma.artid
and hma.pubid = sma.pubid
and sma.lightweight = 1
and sma.well_partitioned_lightweight = 1

if exists (select * from sys.objects where name = 'sysmergepartitioninfo')
begin
update hma set hma.partition_options = smpi.partition_options
from @helpmergearticle hma join dbo.sysmergepartitioninfo smpi
on hma.artid = smpi.artid
and hma.pubid = smpi.pubid
end


declare @source_owner sysname
declare @source_object sysname
declare @identityrangemangementoption int
declare @objid int
declare @artid uniqueidentifier
declare @status int
declare #arts CURSOR LOCAL FAST_FORWARD FOR
select source_owner, source_object, identityrangemangementoption, name, artid, status from @helpmergearticle
open #arts
fetch #arts into @source_owner, @source_object, @identityrangemangementoption, @article, @artid, @status
while (@@fetch_status <> -1)
begin
select @objid = object_id(quotename(@source_owner) + '.' + quotename(@source_object))
if @identityrangemangementoption = 0 and
exists (select * from sys.columns where object_id = @objid and
is_identity = 1 and
ColumnProperty(object_id, name, 'IsIdNotForRepl') = 1)
begin
update @helpmergearticle
set identityrangemangementoption = 2
where source_owner = @source_owner and source_object = @source_object
end

-- pick only active articles - articles which have been prepared
--
if @identityrangemangementoption = 1 and (@status = 2 or @status = 6)
begin
-- do the adjust identity range only if this is not a republisher of that article
if not exists (select * from dbo.sysmergearticles where artid = @artid and sys.fn_MSmerge_islocalpubid(pubid)=0)
begin
-- adjust the identity range on the publisher for the article if needed
exec @retcode = sys.sp_adjustpublisheridentityrange @table_name=@source_object, @table_owner=@source_owner
if @retcode<>0 or @@error<>0
begin
raiserror(20676, 11, -1, @article)
end
end
end --
fetch #arts into @source_owner, @source_object, @identityrangemangementoption, @article, @artid, @status
end
close #arts
deallocate #arts

select * from @helpmergearticle order by id

RETURN (0)

No comments:

Post a Comment

Total Pageviews