Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replmonitorhelppublisher

  No additional text.


Syntax
create procedure sys.sp_replmonitorhelppublisher
(
    @publisher sysname = NULL  -- pubisher - null means all publisher
    ,@refreshpolicy tinyint = 0 -- 0 = default cache refresh, 1 = optimistic force refresh, 2 = non-optimistic force refresh
)
as
begin
    set nocount on
    declare @retcode int
                ,@distdb sysname
                ,@cmd nvarchar(4000)
    
    -- security check.
    -- User must be member of 'replmonitor' role in some distribution database at this distributor.
    
    exec @retcode = sys.sp_MSrepl_DistributorReplMonitorAccess
    if @retcode != 0 or @@error != 0
        return (1)
    
    -- create temp table for results
    
    create table #tmp_replication_publisherdata
    (
        publisher sysname
        ,distribution_db sysname
        ,status int
        ,warning int	-- OR among all subscriptions
        ,publicationcount int
        ,returnstamp nvarchar(20) null
    )

    if @@error != 0
    begin
        raiserror(20507, 16, 1, '#tmp_replication_publisherdata', 'tempdb')
        return 1
    end
    
    -- if publisher is not specified then enumerate through each
    -- distribution database and collect the information
    -- if publisher is specified then execute in the specific dist db
    
    if (@publisher is null)
    begin
        
        -- publisher was not specified
        -- ennumerate the distribution databases
        
        declare #hcdistdb cursor local fast_forward for
            select name from msdb..MSdistributiondbs
        open #hcdistdb
        fetch next from #hcdistdb into @distdb
        while (@@fetch_status != -1)
        begin
            
            -- get data from the current distribution db
            
            select @cmd = quotename(@distdb) + '.sys.sp_replmonitorhelppublisherhelper'
            exec @retcode = @cmd @publisher = @publisher, @refreshpolicy = @refreshpolicy
            if @@error != 0 or @retcode != 0
                return 1
            
            -- get next distribution db
            
            fetch next from #hcdistdb into @distdb
        end -- while (@@fetch_status != -1)
        close #hcdistdb
        deallocate #hcdistdb
    end
    else
    begin
        
        -- publisher was specified
        -- validate and get the distribution database
        
        select @distdb = distribution_db
        from msdb..MSdistpublishers
        where upper(name) = upper(@publisher)
        if (@distdb is null)
        begin
            raiserror (25002, 16, -1)
            return (1)
        end
        
        -- get data
        
        select @cmd = quotename(@distdb) + '.sys.sp_replmonitorhelppublisherhelper'
        exec @retcode = @cmd @publisher = @publisher, @refreshpolicy = @refreshpolicy
        if @@error != 0 or @retcode != 0
            return 1
    end
    
    -- return the resultset
    
    select publisher
        ,distribution_db
        ,status
        ,warning
        ,publicationcount
        ,returnstamp
    from #tmp_replication_publisherdata
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

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