May 2, 2012

sp_MSadd_article (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_MSadd_article(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @article
, int @article_id
, nvarchar @destination_object
, nvarchar @source_object
, nvarchar @description
, nvarchar @source_owner
, nvarchar @destination_owner
, nvarchar @internal)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_article  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname,
@article_id int = NULL,
@destination_object sysname = NULL,
@source_object sysname = NULL,
@description nvarchar(255) = NULL,
@source_owner sysname = NULL,
@destination_owner sysname = null,
@internal sysname = N'PRE-YUKON' -- Can be: 'PRE-YUKON', 'YUKON ADD SUB', 'YUKON ADD AGENT'
)
as
begin
set nocount on

declare @publisher_id smallint
declare @publication_id int
declare @retcode int
declare @thirdparty_flag bit
declare @immediate_sync bit
declare @allow_anonymous bit
declare @subscription_seqno binary(16)
declare @subscribed tinyint
declare @active tinyint

select @subscribed = 1
select @active = 2

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
-- sp_MSvalidate_distpublisher ensures this
--
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSadd_article', 'distribution')
return (1)
end
--
-- Check if publisher is a defined as a distribution publisher in the current database
exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
if @retcode <> 0
begin
return(1)
end

-- Get publication id
select @publication_id = publication_id,
@thirdparty_flag = thirdparty_flag,
@immediate_sync = immediate_sync, @allow_anonymous = allow_anonymous
from dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @publication_id is NULL
begin
raiserror (20026, 11, -1, @publication)
return (1)
end

-- Make sure article does not already exist
if exists (select * from MSarticles where publication_id = @publication_id and
publisher_id = @publisher_id and publisher_db = @publisher_db and article = @article)
begin
if @thirdparty_flag = 1
begin
raiserror (14030, 16, -1, @article, @publication)
return (1)
end
else
begin
exec @retcode = sys.sp_MSdrop_article
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@article = @article
if @retcode <> 0 or @@error <> 0
begin
return (1)
end
end
end

-- If it is a third party publication - check if a subscription exists
-- for the publication already. If it does then we cannot add any
-- articles - the existing subscriptions will have to dropped first and then
-- resubscribed
-- excluding virtual subscriptions.
if (@thirdparty_flag = 1)
begin
if exists (select * from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
subscriber_id >= 0)
begin
raiserror(21131, 16, -1, @publication)
return (1)
end
end

begin tran
save tran MSadd_article

-- Generate new article id when one is not provided by a
-- third party publisher or merge publication
if @article_id is NULL
begin
if (@thirdparty_flag = 1)
begin
-- Generate unique id per publisher
select @article_id = max(article_id) + 1 from MSarticles where
publisher_id = @publisher_id
end
else
begin
-- 6.5 behavior : retain for compatibility
-- NOTE: article_id is incremented per publisher/publisher_db/publication
select @article_id = max(article_id) + 1 from MSarticles where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id
end

if @article_id is null
select @article_id = 1
end
else
begin
--
-- for third party publisher - validate the article id
--
if (@thirdparty_flag = 1)
begin
if exists (select * from MSarticles where
article_id = @article_id and
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id)
begin
if @@trancount > 0
begin
rollback tran MSadd_article
commit tran
end
raiserror (14155, 16, 2)
return (1)
end
end
end

insert into MSarticles (publisher_id, publisher_db, publication_id,
article, article_id, destination_object,
source_owner, source_object, description, destination_owner)
values (@publisher_id, @publisher_db, @publication_id,
@article, @article_id, @destination_object,
@source_owner, @source_object, @description, @destination_owner)
if @@error <> 0
begin
if @@trancount > 0
begin
rollback tran MSadd_article
commit tran
end
return (1)
end

-- For third party publications create immediate sync and anonymous virtual subscription
-- with 'subscribed' status and then change anonymous virtual to 'active' status
-- SQL Server publications will do this via RPC calls to sp_MSadd_subscription
if @thirdparty_flag = 1
begin
select @subscription_seqno = 0x00
if @immediate_sync = 1
begin
-- @status passed to sp_MSadd_subscription should always be @subscribed.
exec @retcode = sys.sp_MSadd_subscription
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@article_id = @article_id,
@subscriber = NULL, -- virtual subscription
@status = @subscribed,
@subscription_seqno = @subscription_seqno,
@sync_type = 1, -- virtual subscriptions are automatic sync type
@internal = @internal
if @retcode <> 0 or @@error <> 0
begin
if @@trancount > 0
begin
rollback tran MSadd_article
commit tran
end
return (1)
end
-- For SQL publications, we have logic to immediately active the
-- virtual anonymous subscription to no_sync subscriptions. However, this
-- feature is not exposed (You need to pass undocumented command line arg
-- '-NoInitialSync' to the distribution agent for the anonymous subscription).
-- Do the same for 3rd party publications

if @allow_anonymous = 1
begin
--
@publisher sysname,
@publisher_db sysname,
@subscriber sysname,
@article_id int,
@status int,
@subscription_seqno varbinary(16),
-- post 6x
@destination_db sysname = '%'
--
exec @retcode = sys.sp_MSupdate_subscription
@publisher = @publisher,
@publisher_db = @publisher_db,
@subscriber = NULL, -- virtual subscription
@article_id = @article_id,
@status = @active,
@subscription_seqno = @subscription_seqno,
@destination_db = '%' -- For virtual sub, @destination_db is not used. Can pass in anything
if @retcode <> 0 or @@error <> 0
begin
if @@trancount > 0
begin
rollback tran MSadd_article
commit tran
end
return (1)
end
end
end
end

commit tran
end

No comments:

Post a Comment

Total Pageviews