-- 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