June 4, 2012

sp_MSsubscription_status (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_MSsubscription_status(int @agent_id)

MetaData:

 CREATE PROCEDURE sys.sp_MSsubscription_status  
(
@agent_id int
)
as
begin
set nocount on

declare @retcode tinyint
declare @status tinyint
declare @inactive tinyint
declare @active tinyint
declare @subscribed tinyint
declare @initiated tinyint
declare @article_id int
declare @publication sysname
declare @article sysname
declare @msg nvarchar(255)
declare @automatic tinyint
declare @none tinyint
declare @success int
declare @last_sync datetime
declare @publication_id int
declare @is_p2p bit

select @success = 2
select @inactive = 0
select @subscribed = 1
select @active = 2
select @initiated = 3
SELECT @automatic = 1
select @none = 2
select @is_p2p = 0

-- Security Check
-- Only perform security check if user is not a member of replmonitor
if isnull(is_member(N'replmonitor'), 0) != 1
begin
-- Security Check
exec @retcode = sys.sp_MScheck_pull_access @agent_id = @agent_id, -- agent id
@agent_type = 0 -- only called by distribution agent
if @@error <> 0 or @retcode <> 0
begin
return (1)
end
end

-- If one article is inactive, and no_sync subscription fail.
if exists (select * from dbo.MSsubscriptions where
status = @inactive and
sync_type in (@none, @automatic) and
agent_id = @agent_id)
begin
-- Find out if there are any PeerToPeer publications for this agent
exec sys.sp_MSispeertopeeragent @agent_id = @agent_id, @is_p2p = @is_p2p output

if @is_p2p = 1
begin
-- The subscription(s) have been marked inactive. You will have to rebuild the topology surrounding this node. See Peer-To-Peer documentation for more information.
raiserror(18854, 16,-1)
return(1)
end

-- The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
raiserror(21074, 16,-1)
return(1)
end
else if exists (select * from dbo.MSsubscriptions where
status = @subscribed and
sync_type = @none and
agent_id = @agent_id)
begin
select @status = @subscribed,
@msg = formatmessage(18857)
end
else
begin
select top 1 @article_id = article_id,
@publication_id = publication_id
from dbo.MSsubscriptions where
agent_id = @agent_id and
sync_type = @automatic and
status = @subscribed

if @publication_id is not null
begin
-- Get the publication name to use later in the formated message
select @publication = publication from dbo.MSpublications where
publication_id = @publication_id

-- If there's more than one article in subscribed state
-- Send a general waiting message.
-- Otherwise, indicate the article name
if exists (select * from dbo.MSsubscriptions where
agent_id = @agent_id and
status = @subscribed and
sync_type = @automatic and
article_id <> @article_id)
begin
-- Snapshot not available message
if @publication is not null
select @msg = formatmessage(21075, @publication)
else -- It is null for 6.5
select @msg = formatmessage(21088)
end
else
begin
-- article_id is unique across pub db for tran
-- but merge article may use the same id
select @article = article from MSarticles where
article_id = @article_id and
publication_id = @publication_id
-- It is null for 6.5
if @article is not null
select @msg = formatmessage(21076, @article)
else
begin
-- Snapshot not available message
if @publication is not null
select @msg = formatmessage(21075, @publication)
else -- It is null for 6.5
select @msg = formatmessage(21088)
end
end

-- If one article is active, the status is active
if exists ( select * from dbo.MSsubscriptions where
agent_id = @agent_id and
sync_type = @automatic and
status = @active)
set @status = @active
else
set @status = @subscribed
end
else
-- For concurrent snapshot, logreader has to run first.
begin
select top 1 @publication_id = publication_id, @status = status from dbo.MSsubscriptions s where
s.status = @initiated and
s.agent_id = @agent_id and
s.sync_type = @automatic

if @publication_id is not null
begin
select @publication = publication from dbo.MSpublications where
publication_id = @publication_id
select @msg = formatmessage(21388, @publication)
end
end
end

-- If nothing returned, all articles are active.
select 'msg' = @msg, 'status' = @status
where @msg is not null
end

No comments:

Post a Comment

Total Pageviews