Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_changepublication

  No additional text.


Syntax

create procedure sys.sp_MSrepl_changepublication
(
    @publication				sysname,
    @property					nvarchar(50),
    @value						nvarchar(255),
    @force_invalidate_snapshot	bit,
    @force_reinit_subscription	bit,
    @publisher					sysname,
    @publisher_type				sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @cmd nvarchar(255)
            ,@cmd2 nvarchar(255)
            ,@pubid int
            ,@replfreqid tinyint
            ,@retcode int
            ,@statusid tinyint
            ,@syncmethodid tinyint
            ,@db_name   sysname
            ,@distributor sysname
            ,@distproc nvarchar (255)
            ,@subscribed int
            ,@virtual_id smallint
            ,@prev_value_bit bit
            ,@value_bit bit
            ,@allow_anonymous bit
            ,@push int
            ,@pull int
            ,@independent_agent bit
            ,@immediate_sync bit
            ,@distribdb sysname
            ,@dbname sysname
            ,@taskid int
            ,@add_virtual_back bit
            ,@alt_snapshot_folder nvarchar(255)
            ,@enabled_for_internet bit
            ,@ftp_address sysname
            ,@snapshot_in_defaultfolder bit
            ,@allow_dts bit
            ,@in_ActiveD    bit
            ,@ad_guidname   sysname
            ,@enc_ftp_password nvarchar(524)
            ,@conflict_policy_id int
            ,@centralized_conflicts_bit bit
            ,@conflict_retention int
            ,@queue_type int
            ,@allow_sync_tran bit
            ,@allow_queued_tran bit
            ,@allow_initialize_from_backup bit
            ,@replicate_ddl int
            ,@options int
            ,@is_enabled_for_het_sub bit
            ,@repl_freq tinyint
            ,@sync_method int
            ,@publisher_local sysname
            ,@publisher_engine_edition int
            ,@OPT_ENABLED_FOR_P2P int
            ,@OPT_ENABLED_FOR_HET_SUB int
            ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
            ,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT int
			,@OPT_ALLOW_PARTITION_SWITCH int
			,@OPT_REPLICATE_PARTITION_SWITCH int
            ,@p2p_originator_id int
            ,@qualifiedname nvarchar(570)
			,@allow_partition_switch bit
			,@replicate_partition_switch bit
			,@fValue bit

    SELECT @OPT_ENABLED_FOR_P2P = 0x1
            ,@OPT_ENABLED_FOR_HET_SUB = 0x4
            ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8
            ,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT = 0x10
			,@OPT_ALLOW_PARTITION_SWITCH = 0x20
			,@OPT_REPLICATE_PARTITION_SWITCH = 0x40

            ,@add_virtual_back = 0
            ,@push = 0
            ,@pull = 1
            ,@alt_snapshot_folder = NULL
            ,@subscribed = 1
            ,@virtual_id = -1
            ,@db_name=db_name()
            ,@publisher_engine_edition = sys.fn_MSrepl_editionid()
			,@fValue = 0

    /*
    ** Security Check
    */

    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF sys.fn_MSrepl_istranpublished(@db_name,1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default not null)
            INSERT INTO #tab1 VALUES ('description')
            --INSERT INTO #tab1 VALUES ('taskid')
            INSERT INTO #tab1 VALUES ('sync_method')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('repl_freq')
            INSERT INTO #tab1 VALUES ('independent_agent')
            INSERT INTO #tab1 VALUES ('immediate_sync')
            INSERT INTO #tab1 VALUES ('enabled_for_internet')
            INSERT INTO #tab1 VALUES ('allow_push')
            INSERT INTO #tab1 VALUES ('allow_pull')
            INSERT INTO #tab1 VALUES ('allow_anonymous')
            INSERT INTO #tab1 VALUES ('retention')
            INSERT INTO #tab1 VALUES ('snapshot_in_defaultfolder')
            INSERT INTO #tab1 VALUES ('alt_snapshot_folder')
            INSERT INTO #tab1 VALUES ('pre_snapshot_script')
            INSERT INTO #tab1 VALUES ('post_snapshot_script')
            INSERT INTO #tab1 VALUES ('compress_snapshot')
            INSERT INTO #tab1 VALUES ('ftp_address')
            INSERT INTO #tab1 VALUES ('ftp_port')
            INSERT INTO #tab1 VALUES ('ftp_subdirectory')
            INSERT INTO #tab1 VALUES ('ftp_login')
            INSERT INTO #tab1 VALUES ('ftp_password')
            INSERT INTO #tab1 VALUES ('allow_subscription_copy')
            INSERT INTO #tab1 VALUES ('conflict_policy')
            INSERT INTO #tab1 VALUES ('centralized_conflicts')
            INSERT INTO #tab1 VALUES ('conflict_retention')
            INSERT INTO #tab1 VALUES ('queue_type')
            INSERT INTO #tab1 VALUES ('publish_to_ActiveDirectory')
            INSERT INTO #tab1 VALUES ('allow_initialize_from_backup')
            INSERT INTO #tab1 VALUES ('replicate_ddl')
            INSERT INTO #tab1 VALUES ('enabled_for_p2p')
            INSERT INTO #tab1 VALUES ('enabled_for_het_sub')
            INSERT INTO #tab1 VALUES (N'p2p_originator_id')
            INSERT INTO #tab1 VALUES (N'p2p_continue_onconflict')
			INSERT INTO #tab1 VALUES (N'allow_parition_switch')
			INSERT INTO #tab1 VALUES (N'replicate_partition_switch')

            PRINT ''
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists.
    */

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

    EXECUTE @retcode = sys.sp_validname @publication

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)


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

	SELECT	@allow_anonymous			= allow_anonymous,
			@ad_guidname				= ad_guidname,	--with NULL value if this publication is not in AD
			@replfreqid					= repl_freq,
			@immediate_sync				= immediate_sync,
			@independent_agent			= independent_agent,
			@syncmethodid				= sync_method,
			@alt_snapshot_folder		= alt_snapshot_folder,
			@enabled_for_internet		= enabled_for_internet,
			@ftp_address				= ftp_address,
			@allow_dts					= allow_dts,
			@queue_type					= queue_type,
			@snapshot_in_defaultfolder	= snapshot_in_defaultfolder,
			@in_ActiveD					= case
											when ad_guidname is NULL then 0
											else 1
										  end,
			@allow_sync_tran			= allow_sync_tran,
			@allow_queued_tran			= allow_queued_tran,
			@allow_initialize_from_backup		= allow_initialize_from_backup        ,
			@replicate_ddl				= replicate_ddl,
			@options					= options,
			@repl_freq					= repl_freq,
			@p2p_originator_id                     = originator_id
	FROM	syspublications
	WHERE	pubid = @pubid

	SELECT @is_enabled_for_het_sub = case
										when ISNULL(@options, 0) & @OPT_ENABLED_FOR_HET_SUB = @OPT_ENABLED_FOR_HET_SUB then 1
										else 0
									 end	

	SELECT @allow_partition_switch = case when
											isnull(@options,0) & @OPT_ALLOW_PARTITION_SWITCH = @OPT_ALLOW_PARTITION_SWITCH then 1
											else 0 end
	SELECT @replicate_partition_switch = case when
											isnull(@options,0) & @OPT_REPLICATE_PARTITION_SWITCH = @OPT_REPLICATE_PARTITION_SWITCH then 1
											else 0 end

    IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    SELECT @property = LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)
    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** syspublications.
    */

    IF @property NOT IN
    	(
    		'taskid',
    		'description',
    		'sync_method',
    		'status',
    		'repl_freq',
    		'immediate_sync',
    		'independent_agent',
    		'enabled_for_internet',
    		'allow_push',
    		'allow_pull',
    		'allow_anonymous',
    		'retention',
    		'snapshot_in_defaultfolder',
    		'alt_snapshot_folder',
    		'pre_snapshot_script',
    		'post_snapshot_script',
    		'compress_snapshot',
    		'ftp_address',
    		'ftp_port',
    		'ftp_subdirectory',
    		'ftp_login',
    		'ftp_password',
    		'allow_subscription_copy',
    		'conflict_policy',
    		'centralized_conflicts',
    		'conflict_retention',
    		'queue_type',
    		'publish_to_activedirectory',
    		'allow_initialize_from_backup',
    		'replicate_ddl',
    		'enabled_for_p2p',
    		'enabled_for_het_sub',
    		N'p2p_originator_id',
    		N'p2p_continue_onconflict',
			N'allow_partition_switch',
			N'replicate_partition_switch'
    	)
        BEGIN
            RAISERROR (21183, 16, -1, @property)
            RETURN (1)
        END
	
    /*
    ** Parameter Check:
    ** If the Publication's alt_snapshot_folder setting is null,
    ** snapshot compression cannot be enabled
    */
    IF (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = '')
        AND @property = 'compress_snapshot'
        AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        RAISERROR (21157, 16, -1)
        RETURN(1)
    END

    /*
    ** Parameter Check:
    ** If enabled_for_internet is set to true, the publication must have a
    ** non-null ftp_address.
    */
    IF @property = N'enabled_for_internet' AND
       LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND
       (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR(21158, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:
    ** 'ftp_port' cannot be null or negative
    */
    IF @property = N'ftp_port' AND (@value IS NULL or convert(int, @value) < 0 or convert(int, @value) > 65535)
    BEGIN
        RAISERROR (21160, 16, -1)
        RETURN (1)
    END

    /*
    ** Enforce @allow_initialize_from_backup restrictions
    ** i) immediate_sync = 1 (temporarily lifted)
    ** ii) allow_dts = 0 (temporarily lifted)
    ** iii) repl_freq = 0 (transactional/continuous)
    **      (auto-reset of the allow_initialize_from_backup property)
    **
    */
--    IF @allow_initialize_from_backup = 1
--    BEGIN
--        IF @property = N'immediate_sync' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'false'
--        BEGIN
--            RAISERROR (18788, 16, -1)
--            RETURN (1)
--        END
--        ELSE IF @property = N'allow_dts' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true'
--        BEGIN
--            RAISERROR (18789, 16, -1)
--            RETURN (1)
--        END
--        IF @property = N'repl_freq' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) <> N'continuous'
--        BEGIN
--            RAISERROR (18787, 16, -1)
--            RETURN (1)
--        END
--    END

	/*
		Enforce PeerToPeer restrictions
	*/
	IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
	BEGIN
		IF @property IN ('taskid',
							'sync_method',
							'repl_freq',
							'independent_agent',
							'enabled_for_internet',
							'allow_anonymous',
							'snapshot_in_defaultfolder',
							'alt_snapshot_folder',
							'pre_snapshot_script',
							'post_snapshot_script',
							'compress_snapshot',
							'ftp_address',
							'ftp_port',
							'ftp_subdirectory',
							'ftp_login',
							'ftp_password',
							'allow_subscription_copy',
							'conflict_policy',
							'centralized_conflicts',
							'queue_type',
							'publish_to_activedirectory',
							'allow_initialize_from_backup',
							'replicate_ddl',
							'enabled_for_het_sub')
        BEGIN
        	-- The property '%s' cannot be modified for Peer-To-Peer publications.
            RAISERROR (20647, 16, -1, @property)
            RETURN (1)
        END

	if (@options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) <> @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION
	begin
		if @property in (N'p2p_originator_id', N'conflict_retention', N'p2p_continue_onconflict')
		begin
        		-- The publication property '%s' cannot be modified because the peer-to-peer publication '%s' is not enabled for conflict detection. To enable the publication for conflict detection, use sp_configure_peerconflictdetection.
			raiserror (22807, 16, -1, @property, @publication)
			return (1)
		end
	end

    END

	
    -- Enforce restrictions for publications enabled for heterogeneous subscriptions
    IF @is_enabled_for_het_sub = 1
    BEGIN
        IF @property IN (   'enabled_for_internet', 'allow_pull', 'allow_sync_tran',
                                'autogen_sync_procs', 'allow_queued_tran', 'pre_snapshot_script',
                                'post_snapshot_script', 'ftp_address', 'ftp_port',
                                'ftp_subdirectory', 'ftp_login', 'ftp_password',
                                'allow_subscription_copy', 'conflict_policy', 'centralized_conflicts',
                                'conflict_retention', 'queue_type', 'allow_initialize_from_backup',
                                'replicate_ddl', 'enabled_for_p2p')
        BEGIN
            -- The property '%s' cannot be modified for publications enabled for heterogeneous subscriptions.
            RAISERROR (20645, 16, -1, @property)
            RETURN (1)
        END
        -- Not supported on Workgroup edition
        IF(@publisher_engine_edition = 21)
        BEGIN
            RAISERROR(14173, 16, -1,'@enabled_for_het_sub')
            RETURN (1)
        END
    END
    IF (@publisher IS NULL) AND (@publisher_type = N'MSSQLSERVER')
    BEGIN
        SELECT @publisher_local = publishingservername()
    END
    ELSE
    BEGIN
        SELECT @publisher_local = @publisher
    END

    -- If @enabled_for_het_sub is the property being set, several addtional
    -- restrictions apply.
    IF @property = 'enabled_for_het_sub'
    BEGIN
        -- If we are enabling this property
        IF LOWER(@value) = N'true'
        begin
            
            -- Heterogenous subscriptions are not allowed on workgroup edition
            
            IF(@publisher_engine_edition = 21)
            BEGIN
                RAISERROR(14173, 16, -1,'@enabled_for_het_sub')
                RETURN (1)
            END
        end
        -- If the publication is already enabled for heterogeneous subscriptions
        -- and we are enabling the property simply return
        IF @is_enabled_for_het_sub = 1 and
                LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            return(0)

        -- If the publication is not enabled for heterogeneous subscriptions
        -- and we are disabling the property simply return
        IF @is_enabled_for_het_sub = 0 and
                LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false'
            return(0)

        -- Cannot change enabled for heterogeneous subscriptions property
        -- if either 'allow_sync_tran' or 'allow_queued_tran' properties are
        -- set for the publication
        IF @allow_sync_tran = 1 OR @allow_queued_tran = 1
        BEGIN
            -- Publications enabled for updatable subscriptions cannot be changed to support heterogeneous subscriptions. Drop the publication and recreate without enabling updatable subscriptions.
            RAISERROR (20639, 16, 1)
            RETURN (1)
        END

        -- Cannot change enabled for heterogeneous subscriptions property
        -- once we have subscriptions to the publication
        IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                        WHERE ss.artid = sa.artid
                        AND sa.pubid = @pubid
                        AND ss.srvid >= 0)     -- skip virtual subscriptions
        BEGIN
            -- Cannot change enabled for heterogeneous subscriptions property while there are subscriptions to the publication.
            RAISERROR (20640, 16, 1)
            RETURN (1)
        END
    END

    -- Check to see if there are snapshot and subscription needs to be reinited.
    declare @need_new_snapshot bit
        ,@need_reinit_subscription bit
        ,@active tinyint

    select @active = 2
    select @need_new_snapshot = 0
    select @need_reinit_subscription = 0

    IF @property IN
    	(
    		'snapshot_in_defaultfolder',
    		'alt_snapshot_folder',
    		'pre_snapshot_script',
    		'post_snapshot_script',
    		'compress_snapshot',
    		'ftp_address',
    		'ftp_port',
    		'ftp_subdirectory',
    		'ftp_login',
    		'ftp_password',
    		'enabled_for_internet'
    	)
    BEGIN
        select @need_new_snapshot = 1
    end
    else if @property = 'sync_method'
    begin
        -- If changing to or from concurrent, must reinit subscription.
        if EXISTS( select * from syspublications sp
            where sp.pubid = @pubid and
            (sp.sync_method in (3,4) or LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in ('concurrent', 'concurrent_c')))
        BEGIN
            select @need_new_snapshot = 1
            select @need_reinit_subscription = 1
        END
        else
            select @need_new_snapshot = 1
    end
    else if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'repl_freq' OR
            LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'enabled_for_het_sub'
    BEGIN
        select @need_new_snapshot = 1
        select @need_reinit_subscription = 1
    END

	if @property = 'allow_partition_switch'
	begin
		select @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
		IF @value IS NULL OR @value NOT IN (N'true', N'false')
		BEGIN
			raiserror (21865, 16, -1, @property)
        	RETURN (1)
    	END
		else
		begin
			-- Enabling/disabling allow_parittion_switch infers the same action for replicate_partition_switch
			if @value = N'true'
			begin
				UPDATE syspublications SET options = options | (@OPT_ALLOW_PARTITION_SWITCH | @OPT_REPLICATE_PARTITION_SWITCH)
	            	WHERE pubid = @pubid
				IF @@ERROR <> 0 GOTO UNDO
			end
			else
			begin
				UPDATE syspublications SET options = options & ~(@OPT_ALLOW_PARTITION_SWITCH | @OPT_REPLICATE_PARTITION_SWITCH)
	            	WHERE pubid = @pubid
				IF @@ERROR <> 0 GOTO UNDO
			end

		end
	end

	if @property = 'replicate_partition_switch'
	begin
		select @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
		IF @value IS NULL OR @value NOT IN (N'true', N'false')
		BEGIN
			raiserror (21865, 16, -1, @property)
        	RETURN (1)
    	END
		else if @allow_partition_switch = 0 and @value = N'true'
		begin
			raiserror (21866, 16, -1, @property, N'true', 'allow_partition_switch', 'true')
			RETURN (1)
		end
		else
		begin
			if @value = N'true'
			begin
				UPDATE syspublications SET options = options | @OPT_REPLICATE_PARTITION_SWITCH
	            	WHERE pubid = @pubid
				IF @@ERROR <> 0 GOTO UNDO
			end
			else
			begin
				UPDATE syspublications SET options = options & ~@OPT_REPLICATE_PARTITION_SWITCH
	            	WHERE pubid = @pubid
				IF @@ERROR <> 0 GOTO UNDO
			end
		end
	end

    -- Have to call this stored procedure to invalidate existing snapshot or reint
    -- subscriptions if needed
    EXECUTE @retcode  = sys.sp_MSreinit_article
						@publication				= @publication,
						@need_new_snapshot			= @need_new_snapshot,
						@need_reinit_subscription	= @need_reinit_subscription,
						@force_invalidate_snapshot	= @force_invalidate_snapshot,
						@force_reinit_subscription	= @force_reinit_subscription,
						@check_only					= 1,
						@publisher					= @publisher,
						@publisher_type				= @publisher_type

    IF @@ERROR <> 0 OR @retcode <> 0
        return (1)

    /*
    ** Change the property.
    */
    begin tran
    save TRAN sp_changepublication

    IF @property = 'description'
    BEGIN
        UPDATE syspublications SET description = @value
            WHERE pubid = @pubid
        IF @@ERROR <> 0 GOTO UNDO
    END

    IF @property ='retention'
    BEGIN
        if @value is NULL
        BEGIN
            RAISERROR(20081, 16, -1, @property)
            GOTO UNDO
        END

        UPDATE syspublications SET retention = convert(int, @value)
            WHERE pubid = @pubid
        IF @@ERROR <> 0 GOTO UNDO
    END

    IF @property ='replicate_ddl'
    BEGIN
        if @value is NULL
        BEGIN
            RAISERROR(20081, 16, -1, @property)
            GOTO UNDO
        END

		if (@publisher_type <> N'MSSQLSERVER')
		begin
			RAISERROR(21649, 16, -1, @property)
	            GOTO UNDO
		end
		else if(@value not in (0, 1))
		begin
            			raiserror (21544, 16, -1)
	            GOTO UNDO
		end
		else
		begin
		        UPDATE syspublications SET replicate_ddl = convert(int, @value)
	    	        WHERE pubid = @pubid
        	IF @@ERROR <> 0 GOTO UNDO
		end
    END

    IF @property = 'taskid'
    BEGIN
        -- No longer supported
        RAISERROR (21023, 16, -1,'@taskid')
        goto UNDO
    END

    IF @property = 'sync_method'
    BEGIN
        /*
        ** Check for a valid synchronization method.
        */

        SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)

        IF @value NOT IN ('native', 'character', 'bcp native', 'bcp character', 'concurrent', 'concurrent_c', 'database snapshot', 'database snapshot character')
        BEGIN
            RAISERROR (14014, 16, -1)
            GOTO UNDO
        END

        /*
        ** Determine the integer value for the sync_method.
        */

        IF @value IN ('native', 'bcp native')
            SELECT @syncmethodid = 0
        ELSE IF @value IN ('character', 'bcp character')
            SELECT @syncmethodid = 1
        ELSE IF @value IN ( 'concurrent' )
            SELECT @syncmethodid = 3
        ELSE IF @value IN ( 'concurrent_c' )
            SELECT @syncmethodid = 4
        ELSE IF @value IN ( 'database snapshot' )
            SELECT @syncmethodid = 5
        ELSE IF @value IN ( 'database snapshot character' )
            SELECT @syncmethodid = 6


        IF @replfreqid = 1 AND @syncmethodid IN (3,4)
        BEGIN
            RAISERROR(22547, 16, -1)
            GOTO UNDO
        END

		-- Verify HREPL sync method
		IF @publisher_type != N'MSSQLSERVER'
		BEGIN
			IF NOT (@syncmethodid = 1 OR @syncmethodid = 4)
			BEGIN
				-- HREPL: Must be character mode sync method
				RAISERROR(21634, 16, -1, 'sync_method', @value, '''character'' or ''concurrent_c''')
				GOTO UNDO
			END

			-- Verify HREPL repl frequency/sync method combinations

			-- SNAPSHOT requires CHARACTER
			IF @replfreqid = 1 AND NOT @syncmethodid = 1
			BEGIN
				RAISERROR(21638, 16, -1, 'snapshot', '''character''')
				GOTO UNDO
			END
			-- TRANSACTIONAL requires CONCURRENT_C
			ELSE IF @replfreqid = 0 AND NOT @syncmethodid = 4
			BEGIN
				RAISERROR(21638, 16, -1, 'continuous', '''concurrent_c''')
				GOTO UNDO
			END
		END


        -- Publication with sync_method of 'database snapshot [character]'
        -- cannot contain filestream columns
     IF @syncmethodid in (5,6) AND
            EXISTS (SELECT sc.name
                      FROM sys.columns sc
                INNER JOIN dbo.sysarticlecolumns sac
                        ON sc.column_id = sac.colid
                       AND sc.is_filestream = 1
                INNER JOIN dbo.sysarticles sa
                        ON sa.artid = sac.artid
                       AND sa.objid = sc.object_id
                       AND sa.pubid = @pubid)
        BEGIN
            RAISERROR(21862, 16, -1)
            GOTO UNDO
        END

        if @syncmethodid NOT IN (1,4,6) and @allow_dts = 1
        begin
            raiserror(21172, 16, -1)
            GOTO UNDO
        end

        -- Non sql subscribers can only use char bcp (not concurrent)
        -- sward  If a publication is enabled for heterogeneous subsrcibers
        --        both sync method 'character' and 'concurrent_c' are
        --        now supported:  'native' and 'concurrent' are not.
        IF @syncmethodid IN (0, 3)
        begin
            IF EXISTS( select * from syspublications sp, syssubscriptions ss,
                        sysarticles sa
                        where sp.pubid = @pubid
                        and sp.pubid = sa.pubid
                        and sa.artid = ss.artid
							and (ss.srvname is not null and len(ss.srvname)> 0)
							and @is_enabled_for_het_sub = 1)
            BEGIN
                RAISERROR(20593, 16, -1, @publication )
                GOTO UNDO
            END
        end

        if exists (select * from syspublications where
            pubid = @pubid and
            sync_method <> @syncmethodid)
        begin
            /*
            ** Update the publication with the new synchronization method.
            */

            /*
            ** If we switch to character mode bcp (an indication that this
            ** publication may support non-SQL Server subscribers) for this
            ** publication, the pre/post snapshot commands settings should be
            ** nullified
			**
			*/
			-- sward We no longer need to infer whether heterogeneous subscribers are
			--       supported from the sync method.  The @OPT_ENABLED_FOR_HET_SUB bit of the
			--       publication options value is used to determine this. @pre_snapshot_script,
			--       @post_snapshot_script and @replicate_ddl cannot be set for
			--       for publications enabled for heterogeneous subscriptions, so need not
			--       be explicitly cleared here.
            -- IF @syncmethodid = 1
            -- BEGIN
            --    UPDATE syspublications
            --    SET sync_method = @syncmethodid, pre_snapshot_script = NULL,
            --        post_snapshot_script = NULL, replicate_ddl = 0
            --    WHERE pubid = @pubid

            -- END
            -- ELSE
            -- BEGIN
                UPDATE syspublications
                    SET sync_method = @syncmethodid
                    WHERE pubid = @pubid
            -- END

            IF @@ERROR <> 0 GOTO UNDO
        end
    END

    IF @property = 'status'
        BEGIN

            /*
            ** Check to make sure that we have a valid status.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'inactive')
                BEGIN
                    RAISERROR (14012, 16, -1)
                    GOTO UNDO
                END

            /*
            ** Determine the integer value for the status.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'active'
                SELECT @statusid = 1
            ELSE
                SELECT @statusid = 0

            /* If status changed */
            IF EXISTS (SELECT * FROM syspublications
                WHERE  pubid = @pubid  AND
                 status <> @statusid)
            BEGIN

                /*
                ** If change the status of the publication,
                ** virtual anonymous subscription have to be recreated.
                **
                */
                IF @allow_anonymous = 1
                BEGIN
                    /* Drop virtual subscriptions */
                    EXEC @retcode = sys.sp_dropsubscription	@publication	= @publication,
															@article		= 'all',
															@subscriber		= NULL,
															@reserved		= 'internal',
															@publisher		= @publisher
                    IF @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO UNDO
                    END
                END

                /*
                ** Update the publication with the new status.
                */

                UPDATE syspublications
                   SET status = @statusid
                 WHERE pubid = @pubid

                IF @@ERROR <> 0
                BEGIN
                    GOTO UNDO
                END

                IF @allow_anonymous = 1
                    SELECT @add_virtual_back = 1
            END
        END

    IF @property = 'repl_freq'
        BEGIN
            /*
            ** Check for a valid replication frequency value.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('continuous', 'snapshot')
                BEGIN
                    RAISERROR (14015, 16, -1)
                    GOTO UNDO
                END

            /*
            ** Determine the integer value for the replication frequency.
            */
            DECLARE @original_replfreqid int
            SET @original_replfreqid = @replfreqid

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'continuous'
                SELECT @replfreqid = 0
            ELSE
                SELECT @replfreqid = 1

            IF @original_replfreqid <> @replfreqid
            BEGIN

			-- Verify HREPL repl frequency/sync method combinations
			IF (@publisher_type != N'MSSQLSERVER')
			BEGIN
				-- SNAPSHOT requires CHARACTER
				IF @replfreqid = 1 AND NOT @syncmethodid = 1
				BEGIN
					RAISERROR(21638, 16, -1, 'snapshot', '''character''')
					GOTO UNDO
				END
				-- TRANSACTIONAL requires CONCURRENT_C
				ELSE IF @replfreqid = 0 AND NOT @syncmethodid = 4
				BEGIN
					RAISERROR(21638, 16, -1, 'continuous', '''concurrent_c''')
					GOTO UNDO
				END
			END

            /*
            ** Only unsubscribed publications may have this modified.
            */
            IF EXISTS (SELECT * FROM syssubscriptions s
                       INNER JOIN sysextendedarticlesview a on s.artid = a.artid
                        WHERE s.status <> @subscribed
                          AND s.srvid >= 0
                          AND a.pubid = @pubid)
            BEGIN
                RAISERROR (14033, 11, -1)
                GOTO UNDO
            END

            -- Reset the autonosync setting if repl_freq is changing into
            -- non-continuous type
            IF @allow_initialize_from_backup = 1 AND @replfreqid <> 0
            BEGIN
                EXEC @retcode = sys.sp_MSdisableautonosync @publication = @publication
                IF @@error <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
            END

/*

            IF EXISTS (SELECT * FROM syssubscriptions
            WHERE
                status <> @subscribed AND
                srvid >= 0 AND
                artid IN (SELECT artid FROM sysextendedarticlesview where pubid
               = @pubid))
            BEGIN
                RAISERROR (14033, 11, -1)
                GOTO UNDO
            END

*/
            IF @immediate_sync = 1
            BEGIN
                /* Drop virtual subscriptions */
                EXEC @retcode = sys.sp_dropsubscription	@publication	= @publication,
														@article		= 'all',
														@subscriber		= NULL,
														@reserved		= 'internal',
														@publisher		= @publisher
                IF @@ERROR <> 0 OR @retcode <> 0
                BEGIN
                    GOTO UNDO
                END
            END
            /*
            ** Update the publication with the new replication frequency.
            */
            -- If we are changing into a snapshot publication, change
            -- concurrent sync methods to their non-concurrent equivalents.
            UPDATE syspublications
               SET repl_freq = @replfreqid,
                   sync_method = CASE
                                    WHEN @replfreqid = 1 THEN
                                        CASE
                                            WHEN @syncmethodid = 3 THEN 0
                                            WHEN @syncmethodid = 4 THEN 1
                                            ELSE sync_method
                                        END
                                    ELSE sync_method
                                 END
             WHERE pubid = @pubid

            IF @@ERROR <> 0
            BEGIN
                GOTO UNDO
            END

                IF (@publisher_type = N'MSSQLSERVER')
                BEGIN
                    -- If we are changing into @replfreqid = 0 (continuous),
                    -- we need to reset the KeepTimestamp (0x08) for all
                    -- articles with primary keys containing timestamp
                    -- columns as that is an unsupportable scenario.
                    DECLARE @article sysname
                    DECLARE @schema_option varbinary(8)
                    DECLARE @schema_option_string nvarchar(20)

                    DECLARE hArticle CURSOR LOCAL FAST_FORWARD FOR
                    SELECT art.name, art.schema_option
                      FROM dbo.sysarticles art
                     WHERE pubid = @pubid
                       AND (fn_replgetbinary8lodword(art.schema_option) & 0x08) <> 0
                       AND EXISTS (SELECT c.name
                                     FROM sys.columns c
                               INNER JOIN sys.index_columns ic
                                       ON c.column_id = ic.column_id
                                      AND c.object_id = ic.object_id
                                      AND ic.is_included_column = 0
                               INNER JOIN sys.indexes i
                                       ON i.index_id = ic.index_id
                                      AND i.object_id = ic.object_id
                                      AND i.is_primary_key = 1
                                    WHERE c.object_id = art.objid
                                      AND c.system_type_id = 189)

                    OPEN hArticle

                    FETCH hArticle INTO @article, @schema_option
                    WHILE @@FETCH_STATUS <> -1
                    BEGIN
                        SET @schema_option_string = sys.fn_varbintohexstr(fn_replprepadbinary8(fn_replgetbinary8lodword(@schema_option) & ~8))

                        EXEC @retcode = sys.sp_changearticle @publication = @publication, @article = @article, @property = N'schema_option', @value = @schema_option_string
                        IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                            CLOSE hArticle
                            DEALLOCATE hArticle
                            GOTO UNDO
                        END
                        FETCH hArticle INTO @article, @schema_option
                    END
                    CLOSE hArticle
                    DEALLOCATE hArticle

                END

            IF @immediate_sync = 1
                SELECT @add_virtual_back = 1
        END
        END

    IF @property = N'alt_snapshot_folder'
        BEGIN
            -- If the alt_snapshot_folder is set to '' or NULL,
            -- set the compress_snapshot bit to 0 and disable
            -- internet support
            IF @value IS NULL OR @value = N''
            BEGIN
                UPDATE syspublications
                   SET alt_snapshot_folder = @value,
                       compress_snapshot = 0,
                       enabled_for_internet = 0
                 WHERE pubid = @pubid
            END
            ELSE
            BEGIN
                UPDATE syspublications
                   SET alt_snapshot_folder = @value
                 WHERE pubid = @pubid

            END
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END

        END

    IF @property = N'pre_snapshot_script'
        BEGIN
            UPDATE syspublications
               SET pre_snapshot_script = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF @property = N'post_snapshot_script'
        BEGIN
            UPDATE syspublications
               SET post_snapshot_script = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF @property = N'ftp_address'
        BEGIN

            IF @value IS NULL OR @value = N''
            BEGIN
                UPDATE syspublications
                   SET ftp_address = @value,
                       enabled_for_internet = 0
                 WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END
            ELSE
            BEGIN
                UPDATE syspublications
                   SET ftp_address = @value
                 WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END
        END

    IF @property = N'ftp_port'
        BEGIN
            UPDATE syspublications
               SET ftp_port = CONVERT(int, @value)
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF @property = N'ftp_subdirectory'
        BEGIN
            UPDATE syspublications
               SET ftp_subdirectory = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF @property = N'ftp_login'
        BEGIN
            UPDATE syspublications
               SET ftp_login = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF @property = N'ftp_password'
        BEGIN
            SELECT @enc_ftp_password = NULL
            IF @value IS NOT NULL
            BEGIN
                SELECT @enc_ftp_password = @value
                EXEC @retcode = sys.sp_MSreplencrypt @enc_ftp_password OUTPUT
                IF @retcode <> 0
                BEGIN
                    GOTO UNDO
                END
            END
            UPDATE syspublications
               SET ftp_password = @enc_ftp_password
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF @property IN ('independent_agent', 'immediate_sync',
            'enabled_for_internet', 'allow_push', 'allow_pull',
            'allow_anonymous', 'snapshot_in_defaultfolder',
            'compress_snapshot', 'allow_subscription_copy',
            'allow_initialize_from_backup')
    BEGIN


        /*
        ** Check for a valid  value.
        */
        SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
        IF @value NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14137, 16, -1)
            GOTO UNDO
        END

        /*
        ** set value bit
        */
        IF @value = 'true'
            SELECT @value_bit = 1
        ELSE
            SELECT @value_bit = 0


        IF @property = 'independent_agent'
        BEGIN
			SELECT	@prev_value_bit = independent_agent
			FROM	syspublications
			WHERE	pubid = @pubid
			
            IF @prev_value_bit <> @value_bit
            BEGIN

                IF @immediate_sync = 1 AND @value_bit = 0
                BEGIN
                    RAISERROR (21022, 16, -1)
                    GOTO UNDO
                END

            /*
            ** no subscriptions are allowed
            */
            IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                    WHERE ss.artid = sa.artid
                        AND   sa.pubid = @pubid
                        AND   ss.srvid <> @virtual_id )
            BEGIN
                RAISERROR (20013, 16, -1, @property)
                GOTO UNDO
            END

            
            -- No share agents for DTS/Updating publications
            
            if (@value_bit = 0 and
                (@allow_dts = 1 or @allow_sync_tran = 1 or @allow_queued_tran = 1))
            begin
                raiserror(21173, 16, -1)
                return(1)
            end

            /* Update the publication type */
            UPDATE syspublications
            SET independent_agent = @value_bit
            WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
            GOTO UNDO
            END
            END
        END

        IF @property = 'immediate_sync'
        BEGIN
			SELECT	@prev_value_bit = immediate_sync
			FROM	syspublications
			WHERE	pubid = @pubid

            IF @prev_value_bit <> @value_bit
            BEGIN

               IF @independent_agent = 0 AND @value_bit = 1
               BEGIN
                    RAISERROR (21022, 16, -1)
                    GOTO UNDO
               END

               /*
               ** The publication has to be immediate_sync type to
               ** allow anonymous subscriptions
               */
                IF @value_bit = 0 AND
                    EXISTS (SELECT * FROM syspublications
                        WHERE pubid = @pubid
                        AND   allow_anonymous = 1 )
                BEGIN
                    RAISERROR (20011, 16, -1, @property)
                    GOTO UNDO
                END


                /*
                ** If turn on immediate_sync, we need to add virtual subscriptions,
                ** Otherwise, we need to drop them
                ** When adding, we need to change publication bit first
                ** When dropping, we need to change publication bit second
                */
                IF @value_bit = 0
                BEGIN
                    -- Drop virtual subscriptions
                    EXEC @retcode = sys.sp_dropsubscription	@publication	= @publication,
															@article		= 'all',
															@subscriber		= NULL,
															@reserved		= 'internal',
															@publisher		= @publisher
                    IF @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO UNDO
                    END

                    -- Reset the immediate_sync ready bit
                    UPDATE syspublications
                        SET immediate_sync_ready = 0
                        WHERE pubid = @pubid

                END

                /* Update the publication type */
                UPDATE syspublications
                    SET immediate_sync = @value_bit
                    WHERE pubid = @pubid
                IF @@error <> 0
 BEGIN
                    GOTO UNDO
                END


                IF @value_bit = 1
                    SELECT @add_virtual_back = 1
            END
        END

        IF @property = 'allow_anonymous'
        BEGIN
			SELECT	@prev_value_bit = allow_anonymous
			FROM	syspublications
			WHERE	pubid = @pubid

            IF @prev_value_bit <> @value_bit
            BEGIN
                /*
                ** The publication has to be immediate_sync type to
                ** allow anonymous subscriptions
                */
                IF @value_bit = 1 AND
                    NOT EXISTS (SELECT * FROM syspublications
                        WHERE pubid = @pubid
                        AND   immediate_sync = 1 )
                BEGIN
                    RAISERROR (20011, 16, -1, @property)
                    GOTO UNDO
                END



                /* Drop virtual subscriptions */
                EXEC @retcode = sys.sp_dropsubscription	@publication	= @publication,
														@article		= 'all',
														@subscriber		= NULL,
														@reserved		= 'internal',
														@publisher		= @publisher
                IF @@ERROR <> 0 OR @retcode <> 0
                BEGIN
                    GOTO UNDO
                END

                /* Update the publication type */
                UPDATE syspublications
                    SET allow_anonymous = @value_bit
                    WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                   GOTO UNDO
                END

                /*
                ** add virtual subscriptions back again to enable
                ** anonymous subscription.
                */
                SELECT @add_virtual_back = 1

            END

        END

        IF @property = 'enabled_for_internet'
        BEGIN

            UPDATE syspublications
               SET enabled_for_internet = @value_bit
             WHERE pubid = @pubid

            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        IF @property = 'allow_push'
        BEGIN

           /*
           ** If turn it off, make sure there's no push subscriptions left
           */
           IF @value_bit = 0 AND
            EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                    WHERE ss.artid = sa.artid
                    AND   sa.pubid = @pubid
                    AND      ss.subscription_type = @push
                    AND   ss.srvid <> @virtual_id )
            BEGIN
                RAISERROR (20012, 16, -1)
                GOTO UNDO
            END


            /* Update the publication type */
            UPDATE syspublications
                SET allow_push = @value_bit
                WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        IF @property = 'allow_pull'
        BEGIN
           /*
           ** If turn it off, make sure there's no pull subscriptions left
           */
           IF @value_bit = 0 AND
            EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                    WHERE ss.artid = sa.artid
                    AND   sa.pubid = @pubid
                    AND      ss.subscription_type = @pull
                    AND   ss.srvid <> @virtual_id )
            BEGIN
                RAISERROR (20013, 16, -1, @property)
                GOTO UNDO
            END
            /* Update the publication type */
            UPDATE syspublications
                SET allow_pull = @value_bit
                WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        IF @property = 'snapshot_in_defaultfolder'
        BEGIN
   -- snapshot_in_defaultfolder = 1 is only meaningful when
            -- alt_snapshot_folder is non-null, otherwise
            -- a copy of the snapshot files is always kept
            -- at the publisher's working directory

            UPDATE syspublications
               SET snapshot_in_defaultfolder = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END

        IF @property = 'compress_snapshot'
        BEGIN

            UPDATE syspublications
               SET compress_snapshot = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END

        IF @property = 'allow_subscription_copy'
        BEGIN
            if @value_bit = 1 and @immediate_sync = 0
            begin
                raiserror(21210, 16, -1)
                goto UNDO
            end

            UPDATE syspublications
               SET allow_subscription_copy = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END

        IF @property = 'allow_initialize_from_backup'
        BEGIN

            IF NOT @publisher_type = N'MSSQLSERVER' and @value_bit = 1
            BEGIN
                RAISERROR(18790, 16, -1)
                GOTO UNDO
            END

            -- If there is a change in the setting of
            -- 'allow_initialize_from_backup'...
            IF @allow_initialize_from_backup <> @value_bit
            BEGIN
                IF @value_bit = 0
                BEGIN
                    EXEC @retcode = sys.sp_MSdisableautonosync
                            @publication = @publication
                END
                ELSE
                BEGIN
                    -- Make sure that the current publication is not a snapshot
                    -- publication
                    IF @replfreqid <> 0
                    BEGIN
                        SELECT @retcode = 1
                        RAISERROR (18787, 16, -1)
                        GOTO UNDO
                    END
                    EXEC @retcode = sys.sp_MSenableautonosync
                            @publication = @publication
                END
                IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
            END
        END

    END

    /* Update publication property at the distributor side if necessary */
    IF @property IN ('sync_method', 'description', 'repl_freq', 'independent_agent',
        'immediate_sync', 'allow_push', 'allow_pull', 'allow_anonymous',
        'retention', 'allow_subscription_copy')
    BEGIN
		DECLARE @loc_publisher	sysname

		IF @publisher IS NULL
		BEGIN
			SELECT @loc_publisher = publishingservername()
		END
		ELSE
		BEGIN
			select @loc_publisher = @publisher
		END

        /* Translate the property names and values  */
        IF @property = 'repl_freq'
        BEGIN
            SELECT @property = 'publication_type'
            SELECT @value = STR(@replfreqid)
        END
        else if @property = 'sync_method'
        begin
            select @value = str(@syncmethodid)
        end

        /* Translate values */
        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            SELECT @value = '1'
        ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false'
            SELECT @value = '0'

        /*
        ** Get distribution server information for remote RPC call.
        */
        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher  = @publisher,
															@rpcsrvname = @distributor OUTPUT,
															@distribdb  = @distribdb OUTPUT
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                GOTO UNDO
            END

        SELECT @dbname = DB_NAME()
        SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.'
							+ QUOTENAME(@distribdb) + '.dbo.sp_MSchange_publication'

		EXECUTE @retcode = @distproc
            @publisher = @loc_publisher,
            @publisher_db = @dbname,
            @publication = @publication,
            @property = @property,
            @value = @value

        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            GOTO UNDO
        END
    END

    IF @add_virtual_back = 1
    BEGIN
		/* Add virtual subscriptions back*/
        EXEC @retcode = sys.sp_addsubscription
            @publication = @publication,
            @article = 'all',
            @subscriber = NULL,
            @destination_db = 'virtual',
            @sync_type = 'automatic',
            @status = NULL,
            @reserved = 'internal',
			@publisher = @publisher
			
        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            GOTO UNDO
        END
    END

    
    -- Queued properties
    
    IF (@property IN ('conflict_policy', 'centralized_conflicts', 'conflict_retention', 'queue_type'))
    BEGIN
        
        -- we will consider changes only if the publication supports queued operations
        
        if exists (select * from syspublications
                where pubid = @pubid and allow_queued_tran = 1)
        BEGIN
            IF (@property = 'conflict_policy')
            BEGIN
                if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'sub reinit')
                    select @conflict_policy_id = 3
                else if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'pub wins')
                    select @conflict_policy_id = 1
                else if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'sub wins')
                    select @conflict_policy_id = 2
                else
                BEGIN
                    raiserror (21184, 16, 3, 'conflict_policy', 'sub reinit', 'pub wins', 'sub wins')
                    GOTO UNDO
                END

                
                -- cannot change this parameter once we have subscriptions
                
                IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                                WHERE ss.artid = sa.artid
                                AND sa.pubid = @pubid
                                AND ss.srvid >= 0)     -- skip virtual subscriptions
                BEGIN
                    RAISERROR (21268, 16, 1, @property)
                    GOTO UNDO
                END

                
                -- Check snapshot permissible values
                
                if ((@replfreqid = 1) and (@conflict_policy_id = 1))
                begin
                    raiserror (21270, 16, 1, '@conflict_policy', @value)
                    GOTO UNDO
                end

                UPDATE syspublications
                SET conflict_policy = @conflict_policy_id
                WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END

            IF (@property = 'centralized_conflicts')
            BEGIN
                if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true')
                    select @centralized_conflicts_bit = 1
                else if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false')
                    select @centralized_conflicts_bit = 0
                else
                begin
                    raiserror (14148, 16, 3, 'centralized_conflicts')
                    GOTO UNDO
                end

                
                -- Check snapshot permissible values
                
                if ((@replfreqid = 1) and (@centralized_conflicts_bit = 0))
                begin
                    raiserror (21270, 16, 1, '@centralized_conflicts', @value)
                    GOTO UNDO
                end

                
                -- cannot change this parameter once we have subscriptions
                
                IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                                WHERE ss.artid = sa.artid
                                AND sa.pubid = @pubid
                                AND ss.srvid >= 0)     -- skip virtual subscriptions
                BEGIN
                    RAISERROR (21268, 16, 2, @property)
                    GOTO UNDO
                END

                UPDATE syspublications
                SET centralized_conflicts = @centralized_conflicts_bit
                WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END

            IF (@property = 'conflict_retention')
            BEGIN
                select @conflict_retention = CAST(@value as integer)
                if (@@error != 0) or (@conflict_retention < 0)
                BEGIN
                    raiserror(20050, 16, -1, 0)
                    GOTO UNDO
                END
                if (@conflict_retention IS NULL)
                    select @conflict_retention = 60

                UPDATE syspublications
                SET conflict_retention = @conflict_retention
                WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END

            IF (@property = 'queue_type')
            BEGIN
                if (lower(@value collate SQL_Latin1_General_CP1_CS_AS) not in (N'msmq',N'sql'))
                begin
                    raiserror(21267, 16, 1, N'msmq, sql')
                    GOTO UNDO
                end
                IF (@queue_type = 1 and lower(@value collate SQL_Latin1_General_CP1_CS_AS) != N'msmq') or
                    (@queue_type = 2 and lower(@value collate SQL_Latin1_General_CP1_CS_AS) != N'sql')
                BEGIN
                    IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                                    WHERE ss.artid = sa.artid
                                    AND   sa.pubid = @pubid)
                    BEGIN
                        RAISERROR (21268, 16, 3, @property)
                        GOTO UNDO
                    END
                    if (@queue_type = 2 and (lower(@value collate SQL_Latin1_General_CP1_CS_AS) = N'msmq'))
                    begin
                        
                        -- MSMQ queue type will not be used for Yukon publications(deprecated)
                        -- We will not support changing of queue type from 'sql' to 'msmq'
                        
                        raiserror(21767, 16, 2, '@queue_type = ''msmq''', '@queue_type = ''sql''')
                        GOTO UNDO
                    end
                    else
                    begin
                        
                        -- For upgrade purposes - we will support changing of queue from 'msmq' to 'sql'
                        
                        select @queue_type = 2
                        UPDATE syspublications
                        SET queue_type = @queue_type
                        WHERE pubid = @pubid
                        IF @@error <> 0
                        BEGIN
                            GOTO UNDO
                        END
                        
                        -- On the distributor drop the MSMQ queue created
                        
                        EXECUTE @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
                             @distribdb   = @distribdb OUTPUT
                        IF @@ERROR <> 0 or @retcode <> 0
                        BEGIN
                            GOTO UNDO
                        END

                        SELECT @dbname =  DB_NAME()
                                ,@distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
                                            N'.dbo.sp_MSchange_publication'

                        EXECUTE @retcode = @distproc
                                        @publisher = @publisher_local,
                                        @publisher_db = @dbname,
                                        @publication = @publication,
                                        @property = @property,
                                        @value = @queue_type

                        IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                            GOTO UNDO
                        END
                    end
                END
            END
        END
    END

	IF @property IN (N'enabled_for_p2p')
    BEGIN
    	DECLARE @current_db_version int,
    			@art_name sysname,
    			@art_objid int,
    			@art_schema_option binary(8),
    			@art_colname sysname,
    			@art_status int,
    			@prop_change bit

		-- Verify SQL Server edition
		-- Peer-To-Peer is restricted to Enterprise and Developer editions
		IF (@publisher_engine_edition not in (30,31))
		BEGIN
			DECLARE @edition sysname
			SELECT @edition = CONVERT(sysname, SERVERPROPERTY('Edition'))

			RAISERROR(20816, 16, -1, @edition)
			GOTO UNDO
		END
    	
    	-- lower value
    	SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)

		IF NOT @publisher_type = N'MSSQLSERVER'
    	BEGIN
    		-- enabled_for_p2p is not supported for heterogeneous publications, must be false.
    		RAISERROR (21632, 16, -1, '@enabled_for_p2p', 'false')
    		GOTO UNDO
    	END
    	
		IF @value NOT IN (N'true',
							N'false')
		BEGIN
			-- The @value parameter value must be either 'true' or 'false'.
		    RAISERROR (14137, 16, -1)
		    GOTO UNDO
		END
    	
		-- Only unsubscribed publications may have this modified.
        IF EXISTS (SELECT *
        			FROM syssubscriptions s
                   		INNER JOIN sysextendedarticlesview a
                   		ON s.artid = a.artid
                    WHERE s.srvid >= 0
                      AND a.pubid = @pubid)
        BEGIN
			-- The publication property 'enabled_for_p2p' cannot be changed when there are subscriptions on it.
            RAISERROR (20013, 16, -1, @property)
            GOTO UNDO
        END

		IF @value = N'false'
		BEGIN
    		-- if it is set to true and you are attempting to set it back to false raise error
    		IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
			BEGIN
				-- The property '%s' cannot be changed to '%s' once the value has already been set to '%s'.
				RAISERROR(21850, 16, 1, 'enabled_for_p2p', 'false', 'true')
				GOTO UNDO
			END
			
			-- we are setting it to false but it's already false
			GOTO COMMIT_TRAN_SUCCESS
    	END

		
		-- When we get here then this means that @value = N'true'
		

		-- if it is already set to true then there is nothing to do
		IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
		BEGIN
			GOTO COMMIT_TRAN_SUCCESS
		END

		-- set the dbname and the p2p bit to on
		SELECT @dbname = DB_NAME(),
				 @options = @options | @OPT_ENABLED_FOR_P2P
				
		-- retrieve the current database version
    	EXEC @retcode = sys.sp_MSgetdbversion @current_version = @current_db_version output
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			GOTO UNDO
		END

		IF @replicate_ddl != 1
		BEGIN
			 -- Parameter N'enabled_for_p2p' can only be set to N'true' when 'replicate_ddl' is set to '1'.
	        RAISERROR(21678, 16, -1, N'enabled_for_p2p', N'true', N'replicate_ddl', '1')
	        GOTO UNDO
		END
		
		IF @syncmethodid != 0
		BEGIN
			EXEC @retcode = sys.sp_MSrepl_changepublication @publication = @publication,
															@property = 'sync_method',
															@value = 'native',
															@force_invalidate_snapshot = @force_invalidate_snapshot,
															@force_reinit_subscription = @force_reinit_subscription,
															@publisher = @publisher,
															@publisher_type = @publisher_type
			IF @@ERROR <> 0 or @retcode <> 0
				GOTO UNDO
			
			SELECT @syncmethodid = 0

			-- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
			RAISERROR(21853, 10, -1, 'sync_method', 'publication', @publication, 'native', 'Peer-To-Peer')
		END
		
		IF @allow_anonymous = 1
		BEGIN
			EXEC @retcode = sys.sp_MSrepl_changepublication @publication = @publication,
															@property = 'allow_anonymous',
															@value = 'false',
															@force_invalidate_snapshot = @force_invalidate_snapshot,
															@force_reinit_subscription = @force_reinit_subscription,
															@publisher = @publisher,
															@publisher_type = @publisher_type
			IF @@ERROR <> 0 or @retcode <> 0
				GOTO UNDO
			
			SELECT @allow_anonymous = 0

			-- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
			RAISERROR(21853, 10, -1, 'allow_anonymous', 'publication', @publication, 'false', 'Peer-To-Peer')
		END

		IF @immediate_sync = 0
		BEGIN
			EXEC @retcode = sys.sp_MSrepl_changepublication @publication = @publication,
															@property = 'immediate_sync',
															@value = 'true',
															@force_invalidate_snapshot = @force_invalidate_snapshot,
															@force_reinit_subscription = @force_reinit_subscription,
															@publisher = @publisher,
															@publisher_type = @publisher_type
			IF @@ERROR <> 0 or @retcode <> 0
				GOTO UNDO

			SELECT @immediate_sync = 1

			-- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
			RAISERROR(21853, 10, -1, 'immediate_sync', 'publication', @pub
 
Last revision 2008RTM
See also

  sp_changepublication (Procedure)
sp_changepublication_snapshot (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_changepublication_snapshot (Procedure)
sp_MSrepl_enable_heterogeneous_subscription (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