Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenum_subscriptions

  No additional text.


Syntax
create procedure sys.sp_MSenum_subscriptions
(
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,
    @exclude_anonymous bit = 0
)
as
begin
    declare @subscriber sysname
    declare @subscriber_id smallint
    declare @subscriber_db sysname
    declare @subscriber_name sysname
    declare @type int
    declare @status int
    declare @distribution_agent nvarchar(100)
    declare @publisher_id smallint
    declare @independent_agent bit
    declare @offload_enabled bit
    declare @offload_server sysname

    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(4000)
    declare @delivery_time int
    declare @delivered_transactions int
    declare @delivered_commands int
    declare @average_commands int
    declare @delivery_rate int
    declare @delivery_latency int
    declare @error_id int
    declare @publication_id int
    declare @job_id binary(16)
    declare @agent_id int
    declare @local_job bit
    declare @profile_id int
    declare @last_timestamp binary(8)
			,@subscriber_type tinyint

    set nocount on
    
    -- security check
    -- only replmonitor can execute this
    
    if not (is_member(N'db_owner') = 1 or isnull(is_member(N'replmonitor'),0) = 1)
    begin
        raiserror(14260, 16, -1)
        return (1)
    end

    select @publisher_id = srvid from master.dbo.sysservers where
       UPPER(srvname) = UPPER(@publisher)

    select @publication_id = publication_id from dbo.MSpublications where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication and
            (publication_type = 0 or publication_type = 1)

    create table #subscriptions (subscriber sysname NOT NULL,  status int NOT NULL,
        subscriber_db sysname NOT NULL,
        type tinyint NOT NULL, distribution_agent nvarchar(100) NOT NULL, last_action nvarchar(4000) NULL,
        action_time nvarchar(24) NULL, start_time nvarchar(24) NULL, duration int NULL,
        delivery_rate float NULL,
        delivery_latency int NULL, delivered_transactions int NULL,
        delivered_commands int NULL,
        delivery_time int NULL, average_commands int NULL,
        error_id int NULL,
        job_id binary(16) NULL, local_job bit NULL, profile_id int NOT NULL,
        agent_id int NOT NULL, last_timestamp binary(8) NOT NULL, offload_enabled bit NOT NULL,
        offload_server sysname NULL, subscriber_type tinyint NULL)

    -- This is to force all queries to return rows ordered by job_id
	-- Note: There might be dist agents left for cleaning up sub, in this case
	-- they are not in sub table and we don't want to show them
    create unique clustered index ucsubscriptions ON #subscriptions (agent_id)
    declare hC CURSOR LOCAL FAST_FORWARD FOR select id, name, subscriber_id, subscriber_db,
        job_id, local_job, subscription_type, profile_id, subscriber_name, offload_enabled,
        offload_server
        from MSdistribution_agents a
        where exists (select * from dbo.MSsubscriptions s where
			(a.id = s.agent_id or a.anonymous_agent_id = s.agent_id) and
			s.publisher_id = @publisher_id and
			s.publisher_db = @publisher_db and
			-- For 6.x publisher, we don't know the association between the publication
			-- and subscriptions. Show every dist agent under each publication.
			(s.publication_id = @publication_id or s.publication_id = 0 ) and
			(a.subscriber_id >= 0  or a.subscriber_id is NULL)) and
			(@exclude_anonymous = 0 or a.anonymous_agent_id is null)
        for read only

    -- declare hC CURSOR LOCAL FAST_FORWARD FOR select a.id, a.name, a.subscriber_id, ms.subscriber_db,
    --  a.job_id, a.local_job, ms.subscription_type, a.profile_id, a.subscriber_name
    --    from MSdistribution_agents a, master.dbo.sysservers s, dbo.MSsubscriptions ms
    --    where
    --    a.publisher_id = @publisher_id and
    --    a.publisher_db = @publisher_db and
    --  (a.publication = @publication or a.publication = 'ALL') and
    --  a.subscriber_id >= 0 and
    --  ms.publisher_db = @publisher_db and
    --  ms.publication_id = @publication_id and
    --  ms.subscriber_id = a.subscriber_id and
    --  ms.subscriber_db = a.subscriber_db and
    --  s.srvid = ms.subscriber_id
    --  for read only

    open hC
    fetch hC into  @agent_id, @distribution_agent, @subscriber_id, @subscriber_db,
        @job_id, @local_job, @type, @profile_id, @subscriber_name, @offload_enabled,
        @offload_server
    while (@@fetch_status <> -1)
    begin
        /* Stuff in the values for no history case */
        select @status = 0,
            @start_time = NULL,
            @time = NULL, @duration = NULL, @comments = NULL,
            @delivery_time = NULL, @delivered_transactions = NULL,
            @delivered_commands = NULL, @average_commands = NULL,
            @delivery_rate = NULL, @delivery_latency = NULL,
            @error_id = NULL,
            @last_timestamp = 0x00000000

        -- Get the status of the agent
        select @status = runstatus,
            @start_time = sys.fn_replformatdatetime(start_time),
            @time = sys.fn_replformatdatetime(time),
            @duration = duration,
            @comments = comments,
            @delivery_time = 0, @delivered_transactions = delivered_transactions,
            @delivered_commands = delivered_commands, @average_commands = average_commands,
            -- Note: return average rate here !!! delivery_rate column is current rate
            @delivery_rate = delivery_rate,
            @delivery_latency = delivery_latency,
            @error_id = error_id, @last_timestamp = timestamp
            from MSdistribution_history with (READPAST)
            where
                agent_id = @agent_id and
                timestamp = (select max(timestamp) from MSdistribution_history with (READPAST)
                    where
                    agent_id = @agent_id)

        -- For anonymous subscriptions, @subscriber_name is not NULL
         if @subscriber_name is NULL
		 begin
            select @subscriber = srvname from master.dbo.sysservers where srvid=@subscriber_id
			select @subscriber_type = type from MSsubscriber_info where
				UPPER(publisher) = UPPER(@publisher) and
				UPPER(subscriber) = UPPER(@subscriber)
         end
		 else
		 begin
			select @subscriber = @subscriber_name
			select @subscriber_db = @subscriber_db + '-' + convert(nvarchar(30), @agent_id)
			-- Don't know the subscriber type for anonymous
		 end

        insert into #subscriptions values ( @subscriber, @status, @subscriber_db,
            @type, @distribution_agent, @comments, @time, @start_time, @duration,
            @delivery_rate, @delivery_latency, @delivered_transactions,
            @delivered_commands,  @delivery_time, @average_commands,
            @error_id, @job_id, @local_job, @profile_id, @agent_id, @last_timestamp,
            @offload_enabled, @offload_server, @subscriber_type)

        fetch hC into  @agent_id, @distribution_agent, @subscriber_id, @subscriber_db,
            @job_id, @local_job, @type, @profile_id, @subscriber_name, @offload_enabled,
            @offload_server
      end

    select * from #subscriptions order by job_id asc

    drop table #subscriptions
    close hC
    deallocate hC
end

 
Last revision 2008RTM
See also

  sp_instdist (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash