Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_publicationsummary

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepl_publicationsummary
(
    @publication    sysname,
    @publisher      sysname,
    @publisher_type sysname
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode          int,
                @distproc         nvarchar(1000),
                @distributor      sysname,
                @distribdb        sysname,
                @loc_publisher    sysname,
                @loc_publisher_db sysname,
                @publisher_db     sysname,
                @tranpubid        int,
                @mergepubid       uniqueidentifier,
                @snap_job_id      binary(16),
                @log_job_id       binary(16),
                @snap_status      int,
                @snap_time        datetime,
                @snap_comments    nvarchar(255),
                @snap_duration int,
                @log_status       int,
                @log_time         datetime,
                @log_comments     nvarchar(255),
                @log_duration int,
                @db_name sysname

    select @tranpubid  = NULL
            ,@mergepubid = NULL
            ,@retcode = 0
            ,@db_name = db_name()
    
    -- PAL check and validate publication
    
    exec @retcode = sp_MSreplcheck_pull @publication = @publication,
                                        @raise_fatal_error = 0
    IF (@@error != 0) or (@retcode != 0)
        RETURN 1
    
    -- Get distributor information
    
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher  = @publisher,
                                    @rpcsrvname = @distributor OUTPUT,
                                    @distribdb  = @distribdb   OUTPUT
    
    -- Set local publisher name and publisher db
    
    IF @publisher IS NULL AND @publisher_type = N'MSSQLSERVER'
    BEGIN
        SELECT @loc_publisher = publishingservername(),
                    @loc_publisher_db = @db_name,
                    @publisher_db     = @db_name
    END
    ELSE
    BEGIN
        IF @publisher_type LIKE N'ORACLE%'
        BEGIN
            SELECT @loc_publisher    = @publisher,
                        @loc_publisher_db = @publisher,
                        @publisher_db     = @distribdb
        END
    END
    
    -- Check to see if db is tran published
    
    IF sys.fn_MSrepl_istranpublished(@db_name, 1) = 1
    BEGIN
        -- Get tran publication id
        SELECT @tranpubid = pubid
        FROM   dbo.syspublications
        WHERE  pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
    END
    
    -- Check to see if db is merge published
    
    IF sys.fn_MSrepl_ismergepublished(@db_name) = 1
    BEGIN
        -- Get merge merge publication id (if not HREPL)
        IF @publisher_type = N'MSSQLSERVER'
        BEGIN
            SELECT @mergepubid = pubid
            FROM    dbo.sysmergepublications
            WHERE    name = @publication
        END
    END
    
    -- If no publication found (tran or merge), return error
    
    IF @tranpubid IS NULL AND @mergepubid IS NULL
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END
    
    -- Collect the agent status
    
    SELECT @distproc = CASE WHEN @publisher_type = N'MSSQLSERVER'
                                 THEN QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + '.'
                                 ELSE '' END + N'sys.sp_MSrepl_agentstatussummary'
    EXECUTE @retcode =  @distproc    @loc_publisher,
                                    @publisher_db,
                                    @publication,
                                    @snap_status   OUTPUT,
                                    @snap_time     OUTPUT,
                                    @snap_comments OUTPUT,
                                    @snap_duration OUTPUT,
                                    @log_status    OUTPUT,
          @log_time      OUTPUT,
                                    @log_comments  OUTPUT,
                                    @log_duration OUTPUT
    IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)

    IF @tranpubid IS NOT NULL
    BEGIN
        
        -- processing for transactional publication
        
        SELECT    name                                   AS name,
                @loc_publisher_db                      AS pub_db,
                @loc_publisher                         AS publisher,
                @publisher_type                        AS publisher_type,
                repl_freq                              AS pub_type,
                CONVERT(tinyint, immediate_sync_ready) AS snapshot_available,
                allow_sync_tran                        AS allow_immediate,
                allow_queued_tran                      AS allow_queued,
                options                                AS allow_peer2peer,
                0                                      AS allow_web_synchronization,
                0                                      AS is_filtered,
                0                                      AS has_dynamic_filters,
                N''                                     AS dynamic_filters_function_list,
                @snap_status                           AS snapshot_status,
                @snap_comments                         AS snapshot_message,
                @snap_time                             AS snapshot_time,
                @log_status                            AS logreader_status,
                @log_comments                          AS logreader_message,
                @log_time                              AS logreader_time,
                @snap_duration                         AS snapshot_duration,
                @log_duration                         AS logreader_duration
        FROM    dbo.syspublications
        WHERE    pubid = @tranpubid
    END
    ELSE IF @mergepubid IS NOT NULL
    BEGIN
        
        -- processing for merge publication
        
        SELECT    name                          AS name,
                @loc_publisher_db             AS pub_db,
                @loc_publisher                AS publisher,
                @publisher_type               AS publisher_type,
                2                             AS pub_type,
                snapshot_ready                AS snapshot_available,
                0                             AS allow_immediate,
                0                             AS allow_queued,
                0                             AS allow_peer2peer,
                allow_web_synchronization     AS allow_web_synchronization,
                publication_type              AS is_filtered,
                dynamic_filters               AS has_dynamic_filters,
                dynamic_filters_function_list AS dynamic_filters_function_list,
                @snap_status                  AS snapshot_status,
                @snap_comments                AS snapshot_message,
                @snap_time                    AS snapshot_time,
                @log_status                   AS logreader_status,
                @log_comments                 AS logreader_message,
                @log_time                     AS logreader_time,
                @snap_duration                         AS snapshot_duration,
                @log_duration                         AS logreader_duration
        FROM    dbo.sysmergepublications
        WHERE    pubid = @mergepubid
    END

    RETURN 0
END

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_publicationsummary (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