Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHhelppublication

  No additional text.


Syntax


-- Name:
--          sp_IHhelppublication

-- Description:
--          HREPL publication properties

-- Security:
--          Internal
-- Requires Certificate signature for catalog access

-- Returns:
--          Result set of publication properties

-- Owner:
--          

CREATE PROCEDURE sys.sp_IHhelppublication
(
    @publication	sysname = '%',
    @found			int = 23456 OUTPUT,		-- a flag indicate returning row
    @publisher		sysname,
    @publisher_type	sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @pubid				int
    DECLARE @has_subscription	bit
    DECLARE @retcode			int
    DECLARE @no_row				bit
    DECLARE @publish_bit		int
    DECLARE @pubname sysname
    DECLARE @username sysname
    DECLARE @OPT_ENABLED_FOR_HET_SUB int

   	DECLARE @publication_ids TABLE
	(
		pubid	int
	)
	
    SELECT @publish_bit = 1
    SELECT @username = suser_sname()
    SELECT @OPT_ENABLED_FOR_HET_SUB = 0x4

	IF @publisher IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@publisher', 'sp_IHhelppublication')
		RETURN (1)
	END

    /*
    ** Initializations.
    */
    IF @found = 23456
    BEGIN
        SELECT @no_row=0
    END
    ELSE
    BEGIN
        SELECT @no_row=1
    END

    /*
    ** 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_IHhelppublication')
		RETURN (1)
	END

    IF @publication <> '%'
	BEGIN
		EXECUTE @retcode = dbo.sp_validname @publication

		IF @retcode <> 0
			RETURN (1)
	END

	-- Get list of matching publication id's for
	-- this publisher/type combo
	INSERT INTO @publication_ids
	SELECT pubid
	FROM   sys.fn_IHgetpubid(@publication, @publisher, @publisher_type)
	
	IF NOT EXISTS
	(
		SELECT	*
		FROM	@publication_ids
	)
	BEGIN
	    SELECT @found = 0
	    RETURN (0)
	END
	ELSE
	BEGIN
	    SELECT @found = 1

	    IF @no_row <>0
	    BEGIN
	        RETURN(0)
	    END
	END

    /*
    ** Create a temp table of pubids identifying publications that the current user has access to
    */
    CREATE TABLE #accessiblepubs (pubid int)

    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR
	SELECT	sp.pubid,
			sp.name
	FROM	syspublications sp,
			@publication_ids pi
	WHERE	((sp.name = @publication) or  (@publication = N'%'))
	  AND	sp.pubid = pi.pubid

    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,
												@publisher			= @publisher

        IF (@retcode = 0 AND @@error = 0)
        BEGIN
            INSERT INTO #accessiblepubs values(@pubid)
		END

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

    /*
    ** Join the table of accessible pubids to the publication entries retrieved from syspublications
    */
	SELECT	'pubid'						= sp.pubid,
			'name'						= sp.name,
			'restricted'				= 0,
			'status'					= sp.status,
			-- using 'task' is for backward compatibilty
			'task'						= convert(int, 1),
			'replication frequency'		= sp.repl_freq,
			'synchronization method'	= sp.sync_method,
			'description'				= sp.description,
			'immediate_sync'			= sp.immediate_sync,
			'enabled_for_internet'		= sp.enabled_for_internet,
			'allow_push'				= sp.allow_push,
			'allow_pull'				= sp.allow_pull,
			'allow_anonymous'			= sp.allow_anonymous,
			'independent_agent'			= sp.independent_agent,
			'immediate_sync_ready'		= sp.immediate_sync_ready,
			-- SyncTran
			'allow_sync_tran'			= sp.allow_sync_tran,
			'autogen_sync_procs'		= sp.autogen_sync_procs,
			'snapshot_jobid'			= sp.snapshot_jobid,
			'retention'					= sp.retention,
			'has subscription'			= CASE WHEN EXISTS
											(
												SELECT	*
												FROM	IHsubscriptions
												WHERE	article_id IN
												(
													SELECT	article_id
													FROM	IHarticles
													WHERE	publication_id = sp.pubid
												)
											)
											THEN 1 ELSE 0 END,
			'allow_queued_tran'			= sp.allow_queued_tran,
			-- Portable snapshot
			'snapshot_in_defaultfolder'	= sp.snapshot_in_defaultfolder,
			'alt_snapshot_folder'		= sp.alt_snapshot_folder,
			-- Pre/post-snapshot commands
			'pre_snapshot_script'		= sp.pre_snapshot_script,
			'post_snapshot_script'		= sp.post_snapshot_script,
			-- Snapshot compression
			'compress_snapshot'			= sp.compress_snapshot,
			-- Post 7.0 ftp support
			'ftp_address'				= sp.ftp_address,
			'ftp_port'					= sp.ftp_port,
			'ftp_subdirectory'			= sp.ftp_subdirectory,
			'ftp_login'					= sp.ftp_login,
			'allow_dts'					= sp.allow_dts,
			'allow_subscription_copy'	= sp.allow_subscription_copy,
			-- 7.5 Queued updates
			'centralized_conflicts'		= NULL,
			'conflict_retention'		= 14,
			'conflict_policy'			= NULL,
			'queue_type'				= NULL,
			'backward_comp_level'		= sp.backward_comp_level,
			'publish_to_AD'				= CASE
											WHEN sp.ad_guidname IS NULL
											THEN 0 ELSE 1 END,
			'allow_initialize_from_backup' = sp.allow_initialize_from_backup,
			'replicate_ddl' = sp.replicate_ddl,
			-- PeerToPeer only (for now, not allowed in hetero)
			'enabled_for_p2p' = 0,
			'publish_local_changes_only' = 0,
			-- Enabled for heterogeneous subscribers only
			'enabled_for_het_sub' = case when (sp.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB then 1 else 0 end


	FROM	syspublications sp,
			MSpublications msp,
			master.dbo.sysservers ss,
			#accessiblepubs acc
	WHERE	sp.pubid = msp.publication_id
	  AND	msp.publisher_id = ss.srvid
	  AND	UPPER(ss.srvname collate database_default) = UPPER(@publisher) collate database_default
	  AND	((sp.name = @publication) or  (@publication = N'%'))
	  AND	sp.pubid = acc.pubid
	ORDER BY sp.name

    RETURN (0)
END

 
Last revision 2008RTM
See also

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