May 16, 2012

sp_MShelp_subscription_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_MShelp_subscription_status(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @retention
, bit @independent_agent)

MetaData:

   
--
-- Name:
-- sp_MShelp_subscription_status
--
-- Description:
-- Determine whether a subscription is out-of-date
--
-- Security:
-- Must be 'sysadmin' or 'db_owner' in distribution database.
-- Requires Certificate signature for catalog access
--
-- Returns:
-- Success/failure
--
-- Notes:
-- This stored procedure is called by sp_MSdrop_subscription_status to determine whether
-- a subscription is currently out-of-date. If out-of-date, the output
-- parameter @out_of_date is set to 0. If not out-of-date, @out_of_date is set to 1.
-- This stored procedure runs at the distributor in the distribution database.
-- Owner:
-- <current owner>

CREATE PROCEDURE sys.sp_MShelp_subscription_status(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@retention int,
@out_of_date int OUTPUT,
@independent_agent bit = 0
)AS

declare @subscriber_id int
declare @publisher_id int
declare @publication_id int
declare @retcode int
declare @agent_id int
declare @min_valid_day datetime
declare @subscription_time datetime
declare @last_history datetime
declare @last_status int

--
-- Security check. 'sysadmin' or db_owner of distribution db required.
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

select @out_of_date = 0 -- Default value set to in-sync
select @publisher_id = srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher)
select @subscriber_id = srvid from master.dbo.sysservers where UPPER(srvname collate database_default)=UPPER(@subscriber)

select @publication_id = publication_id
from MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication

select @subscription_time = subscription_time
from MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db

select @min_valid_day = dateadd(hour, -@retention, getdate())

BEGIN TRAN
select @agent_id = id from MSdistribution_agents
where publisher_id = @publisher_id and
publisher_db = @publisher_db and
((publication = @publication and @independent_agent = 1 )
or (LOWER(publication) = 'all' and @independent_agent = 0)) and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db

if @agent_id is NOT NULL
begin
select Top 1 @last_status = runstatus, @last_history = time
from MSdistribution_history where agent_id = @agent_id
order by timestamp DESC

if EXISTS (select * from MSdistribution_history where agent_id = @agent_id) and (@last_history < @min_valid_day)
and (@retention <> 0)
select @out_of_date = 1
else
if (not EXISTS (select * from MSdistribution_history where agent_id = @agent_id)) and
(@subscription_time < @min_valid_day) and (@retention <> 0)
select @out_of_date = 1
end

COMMIT TRAN
return (0)

FAILURE:
if @@TRANCOUNT = 1
ROLLBACK TRAN
else
COMMIT TRAN
return (1)

No comments:

Post a Comment

Total Pageviews