May 7, 2012

sp_MSaddinitialarticle (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_MSaddinitialarticle(nvarchar @article
, uniqueidentifier @artid
, uniqueidentifier @pubid
, int @nickname
, int @column_tracking
, int @status
, int @pre_creation_command
, nvarchar @resolver_clsid
, nvarchar @insert_proc
, nvarchar @update_proc
, nvarchar @select_proc
, nvarchar @destination_object
, int @missing_count
, varbinary @missing_cols
, nvarchar @article_resolver
, nvarchar @resolver_info
, nvarchar @filter_clause
, int @excluded_count
, varbinary @excluded_cols
, nvarchar @destination_owner
, int @identity_support
, int @verify_resolver_signature
, bit @fast_multicol_updateproc
, bit @published_in_tran_pub
, bit @logical_record_level_conflict_detection
, bit @logical_record_level_conflict_resolution
, tinyint @partition_options
, int @processing_order
, tinyint @upload_options
, bit @delete_tracking
, bit @compensate_for_errors
, bigint @pub_identity_range
, bigint @identity_range
, int @threshold
, bit @stream_blob_columns
, bit @preserve_rowguidcol)

MetaData:

   
-- Called at the subscriber
create procedure sys.sp_MSaddinitialarticle(
@article sysname, -- Name of the article --
@artid uniqueidentifier, -- Article ID --
@pubid uniqueidentifier, -- Publication ID --
@nickname int, -- Article nickname --
@column_tracking int, -- Does the article have column tracking ? --
@status int, -- Status of the article --
@pre_creation_command int = 0, -- Precreate command of the article --
@resolver_clsid nvarchar(255) = NULL,-- Resolver module for the article --
@insert_proc nvarchar(255) = NULL,-- Insert sp for article --
@update_proc nvarchar(255) = NULL,-- Update sp for article --
@select_proc nvarchar(255) = NULL, -- Select SP for this article --
@destination_object sysname, -- Destination object name --
@missing_count int = NULL,
@missing_cols varbinary(128) = NULL,
@article_resolver nvarchar(255) = NULL,
@resolver_info nvarchar(517) = NULL,
@filter_clause nvarchar(2000) = NULL,
@excluded_count int = NULL,
@excluded_cols varbinary(128) = NULL,
@destination_owner sysname = NULL,
@identity_support int = 0,
@verify_resolver_signature int = 1, -- 0=do not verify signature, 1=verify that signature is from trusted source, more values may be added later --
@fast_multicol_updateproc bit = 0,
@published_in_tran_pub bit = 0,
@logical_record_level_conflict_detection bit = 0,
@logical_record_level_conflict_resolution bit = 0,
@partition_options tinyint = 0,
@processing_order int = 0,
@upload_options tinyint = 0, -- Determines whether non-global subs can update rows of that article.
@delete_tracking bit = 1, -- Determined whether deletes will be tracking for this article.
@compensate_for_errors bit = 0,
@pub_identity_range bigint = NULL,
@identity_range bigint = NULL,
@threshold int = NULL,
@stream_blob_columns bit = 0,
@preserve_rowguidcol bit = 1
) AS

SET NOCOUNT ON

declare @objid int
declare @sub_missing_cols binary(128)
declare @retcode int
declare @upload_options_previous tinyint
declare @delete_tracking_previous bit
declare @stream_blob_columns_previous bit
declare @compensate_for_errors_previous bit
declare @preserve_rowguidcol_previous bit
declare @qualified_name nvarchar(270)


--
-- Check for subscribing permission
--
exec @retcode=sys.sp_MSreplcheck_subscribe
if @retcode<>0 or @@ERROR<>0 return (1)

if (@artid is NULL)
BEGIN
RAISERROR (14057, 16, -1)
RETURN (1)
END

-- If this is a no-sync subscription then the article should exist before
-- we can create entry in sysmergearticles.

if exists (select * from dbo.sysmergesubscriptions
where upper(subscriber_server collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS)
and pubid = @pubid and db_name = DB_NAME() and sync_type = 2)
begin

if @destination_owner IS NULL
begin
select @qualified_name = quotename(@destination_object)
end
else
begin
select @qualified_name = quotename(@destination_owner) + '.' + quotename(@destination_object)
end

select @objid = object_id(@qualified_name)

if @objid IS NULL
begin
raiserror(21078, 16, -1, @destination_object)
RETURN (1)
end
end

-- Make sure the article does not already exist in a lightweight subscription.
if exists (select top 1 artid from dbo.sysmergearticles
where nickname=@nickname and lightweight=1)
begin
raiserror(21526,16,1, @article)
return 1
end

--
-- The columns that do not belong to the partition at subscriber side are nothing but
-- missing columns to the subscriber side - it just does not have them !
-- For the same reason, the excluded ones from publisher does not mean anything to
-- subscriber, which does not exclude anything for itself. We just reset to 0
--
select @missing_count = @missing_count + @excluded_count
exec @retcode= sys.sp_ORbitmap @missing_cols, @excluded_cols, @sub_missing_cols OUTPUT
if @@error<>0 or @retcode<>0 return(1)

if (@resolver_clsid='') select @resolver_clsid = NULL

if (@filter_clause='') set @filter_clause = NULL

-- If that article is already part of another publication, reuse its "upload_options" property.
set @upload_options_previous= (select top 1 upload_options
from dbo.sysmergearticles
where artid = @artid)
if @upload_options_previous is not null
set @upload_options= @upload_options_previous


-- If that article is already of another publication, reuse its "delete_tracking" property.
-- 1 is default so look for 0.
set @delete_tracking_previous= 1
select @delete_tracking_previous= delete_tracking from dbo.sysmergearticles
where artid = @artid and
delete_tracking = 0
if 0 = @delete_tracking_previous
set @delete_tracking=0

-- If that article is already of another publication, reuse its "stream_blob_columns" property.
-- 1 is default so look for 0.
set @stream_blob_columns_previous= 1
select @stream_blob_columns_previous= stream_blob_columns from dbo.sysmergearticles
where artid = @artid and
stream_blob_columns = 0
if 0 = @stream_blob_columns_previous
set @stream_blob_columns=0

-- If that article is already part of another publication, reuse its "compensate_for_errors" property.
set @compensate_for_errors_previous= (select top 1 compensate_for_errors
from dbo.sysmergearticles
where artid = @artid)
if @compensate_for_errors_previous is not null
set @compensate_for_errors= @compensate_for_errors_previous

-- If that article is already part of another publication, reuse its "preserve_rowguidcol" property.
set @preserve_rowguidcol_previous= (select top 1 preserve_rowguidcol
from dbo.sysmergearticles
where artid = @artid)
if @preserve_rowguidcol_previous is not null
set @preserve_rowguidcol= @preserve_rowguidcol_previous

--
-- Populate the local copy of dbo.sysmergearticles
--
if exists (select * from dbo.sysmergearticles where artid = @artid and pubid = @pubid )
begin

-- updating an existing entry to become not well-partitioned is never a problem. updating to make it
-- well-partitioned can only be allowed when this is the only entry.
-- if @well_partitioned = 1 and (select count(*) from dbo.sysmergepartitioninfo where artid = @artid) > 1
-- begin
-- raiserror(21535, 16, -1, @article)
-- return 1
-- end

update dbo.sysmergearticles
set name = @article,
pre_creation_command = pre_creation_command,
nickname = @nickname,
column_tracking = @column_tracking,
status = @status,
resolver_clsid = @resolver_clsid,
insert_proc = @insert_proc,
update_proc = @update_proc,
select_proc = @select_proc,
destination_object = @destination_object,
destination_owner = @destination_owner,
missing_col_count = @missing_count,
missing_cols = @sub_missing_cols,
article_resolver = @article_resolver,
resolver_info = @resolver_info,
subset_filterclause = @filter_clause,
excluded_col_count = 0,
excluded_cols = 0x00,
identity_support=@identity_support,
verify_resolver_signature = @verify_resolver_signature,
fast_multicol_updateproc = @fast_multicol_updateproc,
processing_order = @processing_order,
upload_options= @upload_options,
delete_tracking=@delete_tracking,
published_in_tran_pub = @published_in_tran_pub,
compensate_for_errors= @compensate_for_errors,
pub_range = @pub_identity_range,
range = @identity_range,
threshold = @threshold,
stream_blob_columns = @stream_blob_columns,
preserve_rowguidcol = @preserve_rowguidcol

where artid = @artid and pubid = @pubid

update dbo.sysmergepartitioninfo
set logical_record_level_conflict_detection = @logical_record_level_conflict_detection,
logical_record_level_conflict_resolution = @logical_record_level_conflict_resolution,
partition_options = @partition_options
where artid = @artid and pubid = @pubid

-- set up deleted col info
declare @deleted_cols varbinary(128)
execute sp_MSfillup_deleted_cols @objid, @deleted_cols output
update dbo.sysmergearticles set deleted_cols=@deleted_cols
where artid = @artid and pubid=@pubid

end
else
begin
-- if @well_partitioned = 1 and exists (select * from dbo.sysmergepartitioninfo where artid = @artid)
-- begin
-- raiserror(21535, 16, -1, @article)
-- return 1
-- end
-- else if @well_partitioned = 0 and exists
-- (select * from dbo.sysmergepartitioninfo where artid = @artid and well_partitioned = 1)
-- begin
-- raiserror(21536, 16, -1, @article)
-- return 1
-- end

select @objid = 0
insert into dbo.sysmergearticles (name, type, objid, sync_objid, artid, pre_creation_command, pubid,
nickname, column_tracking, status, resolver_clsid, destination_owner,
insert_proc, update_proc, select_proc, destination_object, missing_col_count, missing_cols,
article_resolver, resolver_info, subset_filterclause, excluded_col_count, excluded_cols, identity_support,
verify_resolver_signature, fast_multicol_updateproc, processing_order, upload_options, published_in_tran_pub,
delete_tracking, compensate_for_errors, pub_range, range, threshold, stream_blob_columns, preserve_rowguidcol)
values (@article, 0x0a, @objid, @objid, @artid, @pre_creation_command, @pubid,
@nickname, @column_tracking, @status, @resolver_clsid, @destination_owner,
@insert_proc, @update_proc, @select_proc, @destination_object, @missing_count, @sub_missing_cols,
@article_resolver, @resolver_info, @filter_clause, 0, 0x00, @identity_support, @verify_resolver_signature,
@fast_multicol_updateproc, @processing_order, @upload_options, @published_in_tran_pub,
@delete_tracking, @compensate_for_errors, @pub_identity_range, @identity_range, @threshold, @stream_blob_columns, @preserve_rowguidcol)

insert into dbo.sysmergepartitioninfo (artid, pubid, logical_record_level_conflict_detection, logical_record_level_conflict_resolution, partition_options)
values (@artid, @pubid, @logical_record_level_conflict_detection, @logical_record_level_conflict_resolution, @partition_options)

end
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
RETURN (1)
END

RETURN 0

No comments:

Post a Comment

Total Pageviews