Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_enable_articles_for_het_sub

  No additional text.


Syntax


-- Name:
--          sp_MSrepl_enable_articles_for_het_sub

-- Description:
--          Change the properties of all publication articles so that
--          they are suitable for a publication enabled for heterogeneous
--          subscriptions.

-- Security:
--          Internal

-- Returns:

-- Owner:
--          


create procedure sys.sp_MSrepl_enable_articles_for_het_sub
(
    @publication	sysname,
    @publisher		sysname,
    @publisher_type	sysname
    ,@mode tinyint -- 0 enable, 1 disable
)
AS
BEGIN
	DECLARE @retcode	int
	DECLARE @pubid		int
	DECLARE @pubtype	int
	DECLARE @article	sysname
	DECLARE @src_owner	sysname
	DECLARE @schema_option nvarchar(255)
	DECLARE @dest_owner	sysname
					,@ENABLE tinyint
					,@DISABLE tinyint
					,@artid int
	
    -- Security Check
	EXEC @retcode = sys.sp_MSreplcheck_publish

    IF @@ERROR <> 0 or @retcode <> 0
    BEGIN
	 	RETURN (1)
	END

	-- Get publisher id
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
				,@ENABLE = 0
				,@DISABLE = 1

	-- Get publication info
	SELECT	@pubtype = repl_freq
	FROM	syspublications
	WHERE	pubid = @pubid

	-- Prepare cursor for articles
	DECLARE hArticles CURSOR LOCAL FAST_FORWARD FOR
	SELECT	name, dest_owner, artid
	FROM	sysarticles
	WHERE	pubid = @pubid

	OPEN	hArticles
	FETCH	hArticles INTO @article, @dest_owner, @artid

	WHILE (@@FETCH_STATUS != -1)
	BEGIN
	
		if (@mode = @ENABLE)
		begin
			-- Set the destination owner to NULL.
			EXEC @retcode = sys.sp_changearticle	@publication				= @publication,
							@article					= @article,
							@property					= 'destination_owner',
							@value						= NULL,
							@force_invalidate_snapshot	= 1,
							@force_reinit_subscription	= 1,
							@publisher					= @publisher

			IF	@retcode != 0 OR @@ERROR != 0
			BEGIN
			    CLOSE		hArticles
			    DEALLOCATE	hArticles
			    RETURN (1)
			END

			-- Set fire_triggers_on_snapshot to 'false'.
			EXEC @retcode = sys.sp_changearticle	@publication				= @publication,
							@article					= @article,
							@property					= 'fire_triggers_on_snapshot',
							@value						= 'false',
							@force_invalidate_snapshot	= 1,
							@force_reinit_subscription	= 1,
							@publisher					= @publisher

			IF	@retcode != 0 OR @@ERROR != 0
			BEGIN
			    CLOSE		hArticles
			    DEALLOCATE	hArticles
			    RETURN (1)
			END
			
			-- TRAN specific options
			IF @pubtype = 0
			BEGIN
			    -- Set ins_cmd for articles
			    EXEC @retcode = sys.sp_changearticle	@publication				= @publication,
													    @article					= @article,
													    @property					= 'ins_cmd',
													    @value						= 'SQL',
													    @force_invalidate_snapshot	= 1,
													    @force_reinit_subscription	= 1,
													    @publisher					= @publisher

			    IF @retcode != 0 OR @@ERROR != 0
			    BEGIN
				    CLOSE		hArticles
				    DEALLOCATE	hArticles
				    RETURN (1)
			    END

			    -- Set upd_cmd for articles
			    EXEC @retcode = sys.sp_changearticle	@publication				= @publication,
													    @article					= @article,
													    @property					= 'upd_cmd',
													    @value						= 'SQL',
													    @force_invalidate_snapshot	= 1,
													    @force_reinit_subscription	= 1,
													    @publisher					= @publisher

			    IF @retcode != 0 OR @@ERROR != 0
			    BEGIN
				    CLOSE		hArticles
				    DEALLOCATE	hArticles
				    RETURN (1)
			    END

			    -- Set del_cmd for articles
			    EXEC @retcode = sys.sp_changearticle	@publication				= @publication,
													    @article					= @article,
													    @property					= 'del_cmd',
													    @value						= 'SQL',
													    @force_invalidate_snapshot	= 1,
													    @force_reinit_subscription	= 1,
													    @publisher					= @publisher

			    IF @retcode != 0 OR @@ERROR != 0
			    BEGIN
				    CLOSE		hArticles
				    DEALLOCATE	hArticles
				    RETURN (1)
			    END
			END

			IF @publisher_type = N'MSSQLSERVER'
			BEGIN
			    	-- Turn on create table, clustered index, convert to base types, non-clustered index, unique keys
			    	SELECT  @schema_option = sys.fn_varbintohexstr(CAST(fn_replgetbinary8lodword(fn_replprepadbinary8(0x4071)) AS varbinary(8)))
			END
			ELSE
			BEGIN
			     	-- Turn on create table, non-clustered index, unique keys
			    	SELECT  @schema_option = sys.fn_varbintohexstr(CAST(fn_replgetbinary8lodword(fn_replprepadbinary8(0x4041)) AS varbinary(8)))
			END

			EXEC @retcode = sys.sp_changearticle    @publication                = @publication,
			                                        @article                    = @article,
			                                        @property                   = 'schema_option',
			                                        @value                      = @schema_option,
			                                        @force_invalidate_snapshot  = 1,
			                                        @force_reinit_subscription  = 1,
			                                        @publisher                  = @publisher
			IF @retcode != 0 OR @@ERROR != 0
			BEGIN
			    	CLOSE       hArticles
			    	DEALLOCATE  hArticles
			    	RETURN (1)
			END
		end
		
		IF @publisher_type = N'MSSQLSERVER'
		BEGIN
			--this sets the nonsqlsub flag for columns in this article, so engine can prevent SQL only syntax to go through (e.g. updatetext)	    	
			declare @action nvarchar(5)
			select @action = case @mode when @ENABLE then N'add'
												else N'drop'
											end
			exec sys.sp_MSarticlecol @artid, NULL,N'nonsqlsub', @action
		END
	
	    -- Fetch next article
	    FETCH	hArticles INTO @article, @dest_owner, @artid
	END

	CLOSE		hArticles
	DEALLOCATE	hArticles
	
	RETURN (0)
END

 
Last revision 2008RTM
See also

  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