-- Name:
-- sp_distagentstatus
-- Description:
-- Internal procedure for heterogeneous snapshot agent to
-- determine distribution agent status.
-- Security:
-- Internal
-- Requires Certificate signature for catalog access
-- Returns:
-- Result set: Agent status
-- Owner:
-- sward
create procedure sys.sp_distagentstatus
(
@publication sysname,
@publisher sysname
)
AS
BEGIN
DECLARE @independent_agent int
DECLARE @publisher_id int
SELECT @publisher_id = srvid
FROM master.dbo.sysservers
WHERE UPPER(@publisher) = UPPER(srvname collate database_default)
-- Determine whether the given publication shares an
-- agent with other publicaitons in the same database
-- or not
SELECT @independent_agent = 0
SELECT @independent_agent = independent_agent
FROM dbo.MSpublications
WHERE publisher_id = @publisher_id
AND publisher_db = DB_NAME()
AND publication = @publication
IF @independent_agent = 0
BEGIN
-- If the publication shares an agent with
-- other publications, change the publication
-- name to 'ALL' before querying the MSdistribution_agents
-- table
SELECT @publication = N'ALL'
END
SELECT (SELECT TOP 1 s.status
FROM dbo.MSsubscriptions s
WHERE s.agent_id = msda.id),
msda.job_id
FROM MSdistribution_agents msda
WHERE msda.publisher_id = @publisher_id
AND msda.publisher_db = DB_NAME()
AND msda.publication = @publication
END