CREATE PROCEDURE sys.sp_MSrepl_subscriptionsummary
(
@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@publisher sysname,
@publisher_type sysname
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @retcode int,
@procedure nvarchar(1000),
@distributor_rpc sysname,
@distribution_db sysname,
@subscriber_id int,
@publisher_db sysname,
@independent_agent bit,
@pubid int
SET @retcode = 0
SET @pubid = NULL
SET @publisher_db = db_name()
-- PAL check and validate publication
exec @retcode = sys.sp_MSreplcheck_pull @publication = @publication,
@raise_fatal_error = 0
IF (@@error != 0) or (@retcode != 0)
RETURN 1
-- Check to see if database is activated for publication
IF sys.fn_MSrepl_istranpublished(db_name(),1) <> 1
BEGIN
-- "This database is no enabled for publication."
RAISERROR (14013, 16, -1)
END
-- Parameter check: @publication
IF @publication IS NULL
BEGIN
-- "The parameter @publication cannot be NULL."
RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_subscriptionsummary')
END
-- Parameter Check : @publisher
IF @publisher IS NULL
BEGIN
SET @publisher = publishingservername()
END
-- Retrieve publication options
SELECT @pubid = pubid,
@independent_agent = independent_agent
FROM dbo.syspublications
WHERE name = @publication
AND pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
IF @pubid IS NULL
BEGIN
-- "The publication '@publication' does not exist."
RAISERROR (20026, 16, -1, @publication)
RETURN 1
END
-- Check that the specified subscription exists
IF NOT EXISTS (SELECT ss.*
FROM dbo.sysextendedarticlesview seav
JOIN dbo.syssubscriptions ss WITH (NOLOCK)
ON ss.artid = seav.artid
WHERE seav.pubid = @pubid
AND ss.srvid > -1 and ss.srvname = UPPER(@subscriber)
AND ss.dest_db = @subscriber_db) -- Note: we don't limit this procedure to push subscriptions only
BEGIN
-- "The subscription could not be found."
RAISERROR (20021, 16, -1)
RETURN 1
END
EXEC @retcode = sys.sp_helpdistributor
@rpcsrvname = @distributor_rpc OUTPUT,
@distribdb = @distribution_db OUTPUT,
@publisher = @publisher
IF @@error <> 0 OR @retcode <> 0 or @distribution_db IS NULL
BEGIN
-- "The Distributor has not been installed correctly."
RAISERROR (20036, 16, -1)
RETURN 1
END
IF @independent_agent = 0
BEGIN
SET @publication = 'ALL'
END
DECLARE @distribution_status int,
@distribution_message nvarchar(255),
@distribution_time datetime,
@distribution_duration int
SET @procedure = QUOTENAME(RTRIM(@distributor_rpc)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSrepl_distributionagentstatussummary'
EXEC @retcode = @procedure
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriber_db,
@distribution_status = @distribution_status OUTPUT,
@distribution_message = @distribution_message OUTPUT,
@distribution_time = @distribution_time OUTPUT,
@distribution_duration = @distribution_duration OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN 1
END
SELECT @distribution_status AS distribution_status,
@distribution_message AS distribution_message,
@distribution_time AS distribution_time,
@distribution_duration AS distribution_duration
RETURN 0
END