Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_enumpublications

  No additional text.


Syntax


-- Name:
--  sp_MSrepl_enumpublications

-- Description:
--  Enumerate publications on a database

-- Returns:
--		0 == Failed
--		1 == Succeed

-- Security:
--  public, PAL access for tran publications, dbo check for merge publications
--  Requires Certificate signature for catalog access

-- Notes:
--  Used by the UI to generate a list of pubications

--  Publication type:
--      0 == TRAN
--      1 == SNAPSHOT
--      2 == MERGE

CREATE PROCEDURE sys.sp_MSrepl_enumpublications
(
    @reserved bit = 0   -- Set to 1 when used by UI
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode int
                ,@pubid int
                ,@pubname sysname
                ,@username sysname
                ,@OPT_ENABLED_FOR_P2P int
                ,@category int
                ,@skippalcheck bit
                ,@dbname sysname
                ,@fpublished bit
                ,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION int

    
    -- initialize
    
    select @dbname = db_name()
            ,@OPT_ENABLED_FOR_P2P = 0x1
            ,@username = SUSER_SNAME()
            ,@fpublished = 0
            ,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION = 0x08
    
    -- Verify database is published
    -- Have put the code from fn_MSrepl_ispublished inline for faster processing
    -- If fn_MSrepl_ispublished() is updated - this code block should be updated as well
    
    SELECT @category = category
    FROM master.sys.sysdatabases
    WHERE name = @dbname

    IF (@category IS NOT NULL)
    BEGIN
        -- We have entry for this db_name in sysdatabases
        -- Is this database a distributor?
        IF (@category & 16 = 16)
        BEGIN
            -- DB is a distributor - is it used for HREPL publisher?
            IF OBJECT_ID(N'msdb.dbo.MSdistpublishers') IS NOT NULL
            BEGIN
                IF EXISTS
                (
                    SELECT name
                    FROM msdb.dbo.MSdistpublishers
                    WHERE distribution_db = @dbname
                        AND publisher_type != N'MSSQLSERVER'
                )
                BEGIN
                    SELECT @fpublished = 1
                END
            END
        END
        ELSE
        BEGIN
            -- Check if this database is a transactional or merge publisher
            IF (@category & 1 = 1 OR @category & 4 = 4)
            BEGIN
                SELECT @fpublished = 1
            END
        END
    END -- @category not null
    
    -- If the database is not published - return
    
    IF (@fpublished = 0)
    BEGIN
        RETURN (0)
    END
    
    -- Security Check.
    -- Skip PAL check if DBO
    -- For PAL check - Part of the query to gather the list of publications uses security context
    
    select @skippalcheck = case when (is_member ('db_owner') = 1 OR is_srvrolemember('sysadmin') = 1) then 1 else 0 end
    if (@skippalcheck = 0)
    begin
        DECLARE @accessiblepubs TABLE
        (
            pubid   int
        )
    end
    
    -- Create local temp table if needed
    
    if (@reserved = 0)
    begin
        create TABLE #tmp_publications
        (
            publisher sysname not null,
            dbname sysname not null,
            publication sysname not null,
            publisher_type sysname not null,
            publication_type int not null,
            description nvarchar(255) null,
            allow_queued bit default 0 NOT NULL,
            enabled_for_p2p bit default 0 NOT NULL,
            enabled_for_p2pconflictdetection bit default 0 NOT NULL
        )
    end
    
    -- Get snapshot or transactional publications
    
    IF object_id(N'dbo.syspublications') IS NOT NULL
    BEGIN
        if (@skippalcheck = 0)
        begin
            -- Catalog accessible pub ids
            DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR
      SELECT pubid, name
            FROM dbo.syspublications

            OPEN #hC
            FETCH #hC INTO @pubid, @pubname
            WHILE (@@fetch_status <> -1)
            BEGIN
                EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @pubname,
                                                @raise_fatal_error = 0,
                                                @given_login = @username
                IF (@retcode = 0 AND @@error = 0)
                BEGIN
                    INSERT INTO @accessiblepubs values(@pubid)
                END

                FETCH #hC INTO @pubid, @pubname
            END
            CLOSE #hC
            DEALLOCATE #hC
        end -- if (@skippalcheck = 0)
        
        -- Determine if distribution db is being cataloged
        
        IF (@category & 16 != 16)
        BEGIN
            -- SQL Server publication db
            INSERT INTO #tmp_publications
            (
                publisher,
                dbname,
                publication,
                publisher_type,
                publication_type,
                description,
                allow_queued,
                enabled_for_p2p,
                enabled_for_p2pconflictdetection
            )
            SELECT publishingservername(),
                        @dbname,
                        name,
                        N'MSSQLSERVER',
                        repl_freq,
                        description,
                        allow_queued_tran,
                        (options & @OPT_ENABLED_FOR_P2P),
                        (options & @OPT_ENABLED_FOR_P2PCONFLICTDETECTION)
            FROM dbo.syspublications
            WHERE @skippalcheck = 1
                OR (pubid IN (SELECT pubid FROM @accessiblepubs))
        END
        ELSE
        BEGIN
            -- Distribution db - Heterogeneous publications
            INSERT INTO #tmp_publications
            (
                publisher,
                dbname,
                publication,
                publisher_type,
                publication_type,
                description,
                allow_queued,
                enabled_for_p2p
            )
            SELECT ss.srvname,
                        @dbname, -- distribution db name for enumerator to work
                        sp.name,
                        msd.publisher_type,
                        sp.repl_freq,
                        sp.description,
                        sp.allow_queued_tran,
                        (sp.options & @OPT_ENABLED_FOR_P2P)
            FROM dbo.syspublications sp
                join dbo.MSpublications msp on sp.pubid = msp.publication_id
                join master.dbo.sysservers ss on msp.publisher_id = ss.srvid
                join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname
            WHERE @skippalcheck = 1
                OR (pubid IN (SELECT pubid FROM @accessiblepubs))
        END
    END -- IF object_id(N'dbo.syspublications') IS NOT NULL
    
    -- Get merge publications
    
    IF object_id(N'dbo.sysmergepublications') IS NOT NULL
    BEGIN
        INSERT INTO #tmp_publications
        (
            publisher,
            dbname,
            publication,
            publisher_type,
            publication_type,
            description
        )
        SELECT publisher,
                    publisher_db,
                    name,
                    N'MSSQLSERVER',
                    2,
                    description
        FROM dbo.sysmergepublications
        WHERE (@skippalcheck = 1 OR {fn ISPALUSER(pubid)} = 1)
            and   publisher_db = @dbname
            and   UPPER(publisher) = UPPER(publishingservername())
    END -- object_id(N'dbo.sysmergepublications') IS NOT NULL
    
    -- Return result set if we created local table
    
    if (@reserved = 0)
    begin
        SELECT *
       FROM #tmp_publications
        ORDER BY publisher, dbname, publication
    end
    
    -- all done
    
    RETURN (0)
END

 
Last revision 2008RTM
See also

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