Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_helppublication

  No additional text.


Syntax
create procedure sys.sp_MSrepl_helppublication
(
    @publication sysname,
    @found int OUTPUT, -- flag indicate returning row
    @publisher sysname,
    @publisher_type sysname
)
AS
BEGIN
    SET NOCOUNT ON
    
    -- Declarations.
    
    DECLARE @pubid      int
                ,@has_subscription bit
                ,@retcode int = 0
                ,@no_row bit
                ,@publish_bit int = 1
                ,@pubname sysname
                ,@username sysname
                ,@count  int
                ,@dbname sysname
                ,@skippalcheck bit
                -- Option Bitfield Constants
                ,@OPT_ENABLED_FOR_P2P int = 0x1
                ,@OPT_PUBLISH_ONLY_LOCAL_CHANGES int = 0x2
                ,@OPT_ENABLED_FOR_HET_SUB int = 0x4
                ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int = 0x8
                ,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT int = 0x10
				,@OPT_ALLOW_PARTITION_SWITCH int = 0x20
				,@OPT_REPLICATE_PARTITION_SWITCH int = 0x40

    /*
    ** Initializations.
    */
    SELECT   @username = suser_sname()
                ,@dbname = db_name()
                ,@no_row = case when (@found = 23456) then 0 else 1 end
                -- Skip PAL check if DBO
                ,@skippalcheck = case when (is_member ('db_owner') = 1 OR is_srvrolemember('sysadmin') = 1) then 1 else 0 end

    /*
    ** Check if the database is published for transactional
    */
    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
                            WHERE name = db_name() collate database_default
                                AND (category & @publish_bit) = @publish_bit)
        RETURN(0)
    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that there are some publications
    ** to display.
    */
    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_helppublication')
        RETURN (1)
    END

    IF @publication <> N'%'
    BEGIN
        EXECUTE @retcode = sys.sp_validname @publication
        IF @retcode <> 0
            RETURN (1)
    END

    IF EXISTS (SELECT * FROM dbo.syspublications
                            WHERE ((name = @publication) or  (@publication = N'%')))
    BEGIN
        SELECT @found = 1
    END
    ELSE
    BEGIN
        SELECT @found = 0
        RETURN (0)
    END

    if (@skippalcheck = 0)
    begin
        
        -- Create a temp table of pubids identifying publications that the current user has access to
        
        DECLARE @accessiblepubs TABLE (pubid int)

        SELECT @found = 0
        DECLARE #hC  CURSOR LOCAL FAST_FORWARD FOR
            SELECT pubid, name FROM dbo.syspublications WHERE ((name = @publication) or  (@publication = N'%'))

        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)
                SELECT @found = 1
            end

            FETCH #hC INTO @pubid, @pubname
        END
        CLOSE #hC
        DEALLOCATE #hC
    end

    if @no_row <> 0
      	return (0)
    /*
    ** Join the table of accessible pubids to the publication entries retrieved from syspublications
    */
    SELECT  'pubid'                  = outter.pubid,
                'name'                   = name,
                'restricted'             = 0,
                'status'                 = status,
                -- using 'task' is for backward compatibilty
                'task'                   = convert(int, 1),
                'replication frequency'  = repl_freq,
                'synchronization method' = sync_method,
                'description'            = description,
                'immediate_sync'            = immediate_sync,
                'enabled_for_internet'    = enabled_for_internet,
                'allow_push'             = allow_push,
                'allow_pull'             = allow_pull,
                'allow_anonymous'        = allow_anonymous,
                'independent_agent'        = independent_agent,
                'immediate_sync_ready'    = immediate_sync_ready,
                -- SyncTran
                'allow_sync_tran'        = allow_sync_tran,
                'autogen_sync_procs'        = autogen_sync_procs,
                'snapshot_jobid'         = snapshot_jobid,
                'retention'              = retention,
                'has subscription'       = case when EXISTS (select * from dbo.syssubscriptions where artid in
                                                (select artid from dbo.sysextendedarticlesview where pubid = outter.pubid ) )
                                                then 1 else 0 end,
                'allow_queued_tran'      = allow_queued_tran,
                -- Portable snapshot
                'snapshot_in_defaultfolder'      = snapshot_in_defaultfolder,
                'alt_snapshot_folder'    = alt_snapshot_folder,
                -- Pre/post-snapshot commands
                'pre_snapshot_script'    = pre_snapshot_script,
                'post_snapshot_script'   = post_snapshot_script,
                -- Snapshot compression
                'compress_snapshot'      = compress_snapshot,
                -- Post 7.0 ftp support
                'ftp_address'            = ftp_address,
                'ftp_port'               = ftp_port,
                'ftp_subdirectory'       = ftp_subdirectory,
                'ftp_login'              = ftp_login,
                'allow_dts'              = allow_dts,
                'allow_subscription_copy'  = allow_subscription_copy,
                -- 7.5 Queued updates
                'centralized_conflicts'  = centralized_conflicts,
                'conflict_retention'     = conflict_retention,
                'conflict_policy'        = conflict_policy,
                'queue_type'             = queue_type,
                'backward_comp_level' = backward_comp_level,
                'publish_to_AD' =       case when ad_guidname is NULL then 0 else 1 end,
                'allow_initialize_from_backup' = allow_initialize_from_backup,
                'replicate_ddl' = replicate_ddl,
                -- PeerToPeer only
                'enabled_for_p2p' = case when (options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P then 1 else 0 end,
                'publish_local_changes_only' = case when (options & @OPT_PUBLISH_ONLY_LOCAL_CHANGES) = @OPT_PUBLISH_ONLY_LOCAL_CHANGES then 1 else 0 end,
                -- Enabled for heterogeneous subscribers only
                'enabled_for_het_sub' = case when (options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB then 1 else 0 end,
                --PeerToPeer conflict detection
                'enabled_for_p2p_conflictdetection' = case when (options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION then 1 else 0 end,
                'originator_id' = originator_id,
                'p2p_continue_onconflict' = case when (options & @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT) = @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT then 1 else 0 end,
                'allow_partition_switch' = case when (options & @OPT_ALLOW_PARTITION_SWITCH) = @OPT_ALLOW_PARTITION_SWITCH then 1 else 0 end,
                'replicate_partition_switch' = case when (options & @OPT_REPLICATE_PARTITION_SWITCH) = @OPT_REPLICATE_PARTITION_SWITCH then 1 else 0 end
    FROM dbo.syspublications as outter
    WHERE ((name = @publication) or  (@publication = N'%'))
        AND (@skippalcheck = 1 OR (pubid IN (SELECT pubid FROM @accessiblepubs)))
    ORDER BY name

    RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_helppublication (Procedure)
sp_helppublication_snapshot (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_helppublication_snapshot (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