Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelpmergepub_withrownumbers

  No additional text.


Syntax
create procedure sys.sp_MShelpmergepub_withrownumbers (
	@publication	sysname = '%',	  /* The publication name */
	@found			int			= NULL	OUTPUT,
	@publication_id uniqueidentifier = NULL OUTPUT,
	@reserved		nvarchar(20) = NULL,
	@publisher		sysname = NULL,
	@publisher_db	sysname = NULL
	) AS

	SET NOCOUNT ON

	/*
	** Declarations.
	*/

	declare @retcode			int
	declare @no_row				bit
	declare @has_subscription	bit
	declare @pubid 			uniqueidentifier

	declare @tmp_tbl TABLE (
		id						int					identity NOT NULL,
		name					sysname				collate database_default not null,
		description				nvarchar(255)		collate database_default null,
		status					tinyint				NULL,
		retention				int					NULL,
		sync_mode				tinyint				NULL,
		allow_push				int					NULL,
		allow_pull				int					NULL,
		allow_anonymous			int					NULL,
		centralized_conflicts	int					NULL,
		priority				float(8)			NOT NULL,
		snapshot_ready			tinyint				NULL,
		publication_type		int					NULL,
		pubid					uniqueidentifier	NOT NULL,
		snapshot_jobid			binary(16)			NULL,
		enabled_for_internet	int					NULL,
		dynamic_filters			int					NULL,
		has_subscription		bit					NULL,
		-- Portable snapshot support
		snapshot_in_defaultfolder		bit default 1		NOT NULL,
		alt_snapshot_folder		nvarchar(255)		collate database_default null,
		-- Pre/post-snapshot commands
		pre_snapshot_script		nvarchar(255)		collate database_default null,
		post_snapshot_script	nvarchar(255)		collate database_default null,
		-- Snapshot compression
		compress_snapshot		bit default 0		NOT NULL,
		-- Post 7.0 FTP support
		ftp_address				sysname				collate database_default null,
		ftp_port				int					NOT NULL,
		ftp_subdirectory		nvarchar(255)		collate database_default null,
		ftp_login				sysname				collate database_default null,
		conflict_retention		int					NULL,
		keep_partition_changes	int					NULL,
		allow_subscription_copy int					NULL,
		allow_synctoalternate	int					NULL,
		validate_subscriber_info nvarchar(500)		collate database_default null,
		backward_comp_level		int					not null default 10, --defaulted to 70 server
		publish_to_activedirectory			bit					null,
		max_concurrent_merge	int					NULL,
		max_concurrent_dynamic_snapshots int  NOT NULL default 0,
		use_partition_groups	int	default 0,
		num_of_articles			int default 0,
		replicate_ddl			int default 0,
		publication_number		smallint			NOT NULL default 0,
		allow_subscriber_initiated_snapshot bit default 0,
		allow_web_synchronization bit default 0,
		web_synchronization_url nvarchar(500)		collate database_default null,
		allow_partition_realignment	bit	default 1,
		retention_period_unit	tinyint default 0,
		has_downloadonly_articles bit default 0,
		decentralized_conflicts	int					NULL,
		generation_leveling_threshold	int NULL default 0,
		automatic_reinitialization_policy bit default 0
		-- ANY COLUMNS ADDED HERE AND RETRIEVED IN THE RESULTSET OF THIS PROC SHOULD PROBABLY ALSO BE ADDED TO
		-- sp_MShelpmergepub_withoutrownumbers. sp_MShelpmergepub_withrownumbers is called by UI and SQL2000 or prior subscribers.
		-- sp_MShelpmergepub_withoutrownumbers is called by Yukon subscribers. We need to make sure that the column list
		-- in the resultset is the same between these procs.
	)

	/*
	** Initializations.
	*/
	select @has_subscription = 0

	if @found is NULL
	BEGIN
		select @no_row=0
	END
	else
	BEGIN
		select @no_row=1
	END

	if @publisher is NULL
		set @publisher=publishingservername()
	if @publisher_db is NULL
		set @publisher_db=db_name()

	select @found		= 0	
	
	/*
	** Running sp_help is OK from everywhere, whether enabled for publishing or not
	*/
	
	IF object_id('sysmergesubscriptions') is NULL
		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_MShelpmergepub_withrownumbers')
		RETURN (1)
	END
		
    if @publication <> '%'
    begin
        -- do a pal role check
        select @pubid = pubid from dbo.sysmergepublications
            where UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name() and name = @publication
        if @pubid is not NULL
        begin
            if {fn ISPALUSER(@pubid)} <> 1
            begin
                return 0
            end
        end
    end

	IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = 'internal'
		GOTO SelectPubs
	
	if	NOT EXISTS (select * FROM dbo.sysmergepublications pub, dbo.sysmergesubscriptions sub
		WHERE pub.name like @publication
			and UPPER(pub.publisher)=UPPER(@publisher)
			and pub.publisher_db=@publisher_db
			and sub.pubid = pub.pubid
			and UPPER(sub.subscriber_server) = UPPER(@publisher)
			and sub.db_name = @publisher_db)
		BEGIN
			select @found = 0
			RETURN (0)
		END
	else
		BEGIN
			select @found = 1
			select @publication_id = pubid FROM dbo.sysmergepublications
				WHERE name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
			if exists (select * from dbo.sysmergesubscriptions where pubid<>subid and pubid in
				(select pubid from dbo.sysmergepublications where name like @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db))
				select @has_subscription = 1
			if @no_row <> 0
				RETURN(0)
		END

SelectPubs:
	
	/* This is valid at all sites - used for decentralized conflicts */
	IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = 'internal'
		begin
			INSERT into @tmp_tbl(name, description, status, retention, sync_mode,
					allow_push, allow_pull, allow_anonymous,
					centralized_conflicts,
					priority, snapshot_ready, publication_type, pubid, snapshot_jobid,
					enabled_for_internet,
					dynamic_filters, snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script,
					post_snapshot_script, compress_snapshot, ftp_address, ftp_port,
					ftp_subdirectory, ftp_login, conflict_retention,
					keep_partition_changes, allow_subscription_copy, allow_synctoalternate,
					validate_subscriber_info,backward_comp_level, publish_to_activedirectory, max_concurrent_merge,
					max_concurrent_dynamic_snapshots, use_partition_groups, replicate_ddl, publication_number,
					allow_subscriber_initiated_snapshot,
					allow_web_synchronization, web_synchronization_url,allow_partition_realignment, retention_period_unit,
		            has_downloadonly_articles, decentralized_conflicts, generation_leveling_threshold, automatic_reinitialization_policy
					)
			select	pubs.name, pubs.description, pubs.status, pubs.retention, pubs.sync_mode,
					pubs.allow_push, pubs.allow_pull, pubs.allow_anonymous, pubs.centralized_conflicts,
					subs.priority, pubs.snapshot_ready, pubs.publication_type, pubs.pubid, pubs.snapshot_jobid,
					pubs.enabled_for_internet,
					pubs.dynamic_filters,
					pubs.snapshot_in_defaultfolder,
					pubs.alt_snapshot_folder,
					pubs.pre_snapshot_script,
					pubs.post_snapshot_script,
					pubs.compress_snapshot,
					pubs.ftp_address,
					pubs.ftp_port,
					pubs.ftp_subdirectory,
					pubs.ftp_login,
					pubs.conflict_retention,
					pubs.keep_before_values,
					pubs.allow_subscription_copy,
					pubs.allow_synctoalternate,
					case when pubs.validate_subscriber_info = N'' then NULL else pubs.validate_subscriber_info end,
					pubs.backward_comp_level,
					case when pubs.ad_guidname is NULL then 0 else 1 end,
					pubs.max_concurrent_merge,
					pubs.max_concurrent_dynamic_snapshots,
					pubs.use_partition_groups,  -- select what the table has and rmo will decide
					pubs.replicate_ddl,
					pubs.publication_number,
					pubs.allow_subscriber_initiated_snapshot,
					pubs.allow_web_synchronization,
					pubs.web_synchronization_url,
					pubs.allow_partition_realignment,
					pubs.retention_period_unit,
					sys.fn_MSmerge_hasdownloadonlyarticles(pubs.pubid),
					pubs.decentralized_conflicts,
					pubs.generation_leveling_threshold,
					pubs.automatic_reinitialization_policy
			FROM	dbo.sysmergesubscriptions	subs,
					dbo.sysmergepublications	pubs
					WHERE pubs.name LIKE @publication
						AND UPPER(pubs.publisher)=UPPER(@publisher)
						AND pubs.publisher_db=@publisher_db
						AND subs.subid = pubs.pubid
						AND subs.subscriber_type = 1
						AND (1 = {fn ISPALUSER(pubs.pubid)} or
							  1 = is_srvrolemember('db_owner') or
							  1 = isnull(is_member('replmonitor'),0))
			ORDER BY name
		end
	/* This is valid only at publishers and republishers */
	else
		begin
			INSERT into @tmp_tbl(name, description, status, retention, sync_mode,
					allow_push, allow_pull, allow_anonymous,
					centralized_conflicts,
					priority, snapshot_ready, publication_type, pubid, snapshot_jobid,
					enabled_for_internet,
					dynamic_filters, has_subscription,
					snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script,
					post_snapshot_script, compress_snapshot, ftp_address,
					ftp_port, ftp_subdirectory, ftp_login, conflict_retention,
					keep_partition_changes, allow_subscription_copy, allow_synctoalternate,
					validate_subscriber_info, backward_comp_level, publish_to_activedirectory,max_concurrent_merge,
					max_concurrent_dynamic_snapshots,use_partition_groups, replicate_ddl, publication_number,
					allow_subscriber_initiated_snapshot,
					allow_web_synchronization, web_synchronization_url,allow_partition_realignment, retention_period_unit,
					has_downloadonly_articles, decentralized_conflicts, generation_leveling_threshold, automatic_reinitialization_policy)
			select	pubs.name, pubs.description, pubs.status, pubs.retention, pubs.sync_mode,
					pubs.allow_push, pubs.allow_pull, pubs.allow_anonymous, pubs.centralized_conflicts,
					subs.priority, pubs.snapshot_ready, pubs.publication_type, pubs.pubid, pubs.snapshot_jobid,
					pubs.enabled_for_internet,
					pubs.dynamic_filters, case when exists (select * from dbo.sysmergesubscriptions where pubid<>subid and pubid in
												(select in_pubs.pubid from dbo.sysmergepublications in_pubs where in_pubs.name = pubs.name
													and UPPER(in_pubs.publisher)=UPPER(@publisher) and in_pubs.publisher_db=@publisher_db))
										  then 1
										  else 0 end,
					pubs.snapshot_in_defaultfolder, pubs.alt_snapshot_folder,
					pubs.pre_snapshot_script, pubs.post_snapshot_script,
					pubs.compress_snapshot, pubs.ftp_address,
					pubs.ftp_port, pubs.ftp_subdirectory,
					pubs.ftp_login,
					pubs.conflict_retention,
					pubs.keep_before_values,
					pubs.allow_subscription_copy,
					pubs.allow_synctoalternate,
					case when pubs.validate_subscriber_info = N'' then NULL else pubs.validate_subscriber_info end,
					pubs.backward_comp_level,
					case when pubs.ad_guidname is NULL then 0 else 1 end ,
					pubs.max_concurrent_merge,
					pubs.max_concurrent_dynamic_snapshots,
					pubs.use_partition_groups,
					pubs.replicate_ddl,
					pubs.publication_number,
					pubs.allow_subscriber_initiated_snapshot,
					pubs.allow_web_synchronization,
					pubs.web_synchronization_url,
					pubs.allow_partition_realignment,
					pubs.retention_period_unit,
					sys.fn_MSmerge_hasdownloadonlyarticles(pubs.pubid),
					pubs.decentralized_conflicts,
					pubs.generation_leveling_threshold,
					pubs.automatic_reinitialization_policy
			FROM	dbo.sysmergesubscriptions	subs,
					dbo.sysmergepublications	pubs
					WHERE pubs.name LIKE @publication
						 and UPPER(pubs.publisher)=UPPER(@publisher)
						 and pubs.publisher_db=@publisher_db
						AND subs.subid = pubs.pubid
						AND subs.subscriber_type = 1
						AND UPPER(subs.subscriber_server) = UPPER(@publisher)
						AND subs.db_name = @publisher_db
						AND (1 = {fn ISPALUSER(pubs.pubid)} or
							  1 = is_srvrolemember('db_owner') or
							  1 = isnull(is_member('replmonitor'),0))
			ORDER BY name
		end
	if @@ERROR <> 0
		RETURN (1)

	update @tmp_tbl set snapshot_ready=0 where snapshot_ready>1
	if @@ERROR<>0
		RETURN (1)

    	declare hC1 cursor local fast_forward for select pubid from @tmp_tbl for read only
    	open hC1
    	fetch hC1 into @pubid
    	while (@@fetch_status <> -1)
	begin
		update @tmp_tbl set num_of_articles = (select count(*) from dbo.sysmergearticles where pubid = @pubid)
		if @@ERROR <> 0
		begin
			close hC1
			deallocate hC1
			return (1)
		end
    		fetch hC1 into @pubid
    	end
    	close hC1
    	deallocate hC1
    	
	select * FROM @tmp_tbl
	RETURN (0)

 
Last revision 2008RTM
See also

  sp_helpmergepublication (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MShelpmergepub_withoutrownumbers (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