Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_changemergepublication

  No additional text.


Syntax

-- Name: sp_changemergepublication

-- Descriptions:

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

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

create procedure sys.sp_changemergepublication (
    @publication sysname,               /* Publication name */
    @property sysname = NULL,           /* The property to change */
    @value nvarchar(255) = NULL,        /* The new property value */
    @force_invalidate_snapshot bit = 0, /* Force invalidate existing snapshot */
    @force_reinit_subscription bit = 0  /* Force reinit subscription */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @max_concurrent_merge   int
    declare @cmd                nvarchar(255)
    declare @pubid              uniqueidentifier
    declare @pubidstr           nvarchar(38)
    declare @retcode            int
    declare @statusid           tinyint
    declare @sync_modeid        tinyint
    declare @distributor        sysname
    declare @distproc           nvarchar(300)
    declare @value_bit          bit
    declare @snapshot_ready     tinyint
    declare @subscribed         int
    declare @dbname             sysname
    declare @distribdb          sysname
    declare @alt_snapshot_folder nvarchar(255)
    declare @enabled_for_internet bit
    declare @ftp_address        sysname
    declare @enc_ftp_password   nvarchar(524)
    declare @snapshot_in_defaultfolder bit
    declare @dynamic_filters_id int
    declare @schemaversion      int
    declare @schemaguid         uniqueidentifier
    declare @schematype         int
    declare @schematext         nvarchar(2000)
    declare @artid              uniqueidentifier
    declare @in_ActiveD         bit
    declare @ad_guidname        sysname
    declare @db_name            sysname
    declare @compress_snapshot  bit
    declare @numeric_value      int
    declare @dynamic_filters    bit
    declare @publication_number smallint
    declare @REPOLEVersion_70RTM        int
    declare @REPOLEVersion_70SP1        int
    declare @REPOLEVersion_70SP2        int
    declare @REPOLEVersion_80RTM        int
    declare @REPOLEVersion_90           int
    declare @REPOLEVersion_100           int

    declare @REPOLEVersion_70RTM_string     nvarchar(5)
    declare @REPOLEVersion_70SP1_string     nvarchar(5)
    declare @REPOLEVersion_70SP2_string     nvarchar(5)
    declare @REPOLEVersion_70SP3_string     nvarchar(5)
    declare @REPOLEVersion_70SP4_string     nvarchar(5)
    declare @REPOLEVersion_80RTM_string     nvarchar(5)
    declare @REPOLEVersion_80SP1_string     nvarchar(5)
    declare @REPOLEVersion_80SP2_string     nvarchar(5)
    declare @REPOLEVersion_80SP3_string     nvarchar(5)
    declare @REPOLEVersion_90RTM_string     nvarchar(5)
    declare @REPOLEVersion_100RTM_string   nvarchar(6)

    declare @compatlevel int
    declare @SCHEMA_TYPE_DROPARTICLE int
    declare @SCHEMA_TYPE_RETENTIONCHANGE int
    declare @SCHEMA_TYPE_RETENTIONUNITCHANGE int
    declare @SCHEMA_TYPE_BACKWARD_COMPAT_LEVEL int
    declare @SCHEMA_TYPE_VALIDATE_SUBSCRIBERINFO int
    declare @SCHEMA_TYPE_CHANGE_MERGE_PUBLICATION_90RTM int
    declare @previousnumericvalue       int
    declare @previousbitvalue           bit
    declare @eventtype                  tinyint
    declare @retention_period_unit  tinyint
    declare @retention              int
    declare @buildnumber nvarchar(128)
    declare @upload_options tinyint
    declare @automatic_reinitialization_policy bit
    			,@publishingservername sysname
    declare @got_merge_admin_applock bit

    select @got_merge_admin_applock = 0

    set @property= LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)

    select @subscribed = 1
    select @snapshot_ready = 1
    select @db_name=db_name()
    		,@publishingservername = publishingservername()

    set @REPOLEVersion_70RTM= 10
    set @REPOLEVersion_70SP1= 20
    set @REPOLEVersion_70SP2= 30
    set @REPOLEVersion_80RTM= 40
    set @REPOLEVersion_90= 90
    set @REPOLEVersion_100=100

    set @REPOLEVersion_70RTM_string = N'70RTM'
    set @REPOLEVersion_70SP1_string = N'70SP1'
    set @REPOLEVersion_70SP2_string = N'70SP2'
    set @REPOLEVersion_70SP3_string = N'70SP3'
    set @REPOLEVersion_70SP4_string = N'70SP4'
    set @REPOLEVersion_80RTM_string = N'80RTM'
    set @REPOLEVersion_80SP1_string = N'80SP1'
    set @REPOLEVersion_80SP2_string = N'80SP2'
    set @REPOLEVersion_80SP3_string = N'80SP3'
    set @REPOLEVersion_90RTM_string = N'90RTM'
    set @REPOLEVersion_100RTM_string = N'100RTM'

    set @SCHEMA_TYPE_RETENTIONCHANGE= 9
    set @SCHEMA_TYPE_BACKWARD_COMPAT_LEVEL= 18
    set @SCHEMA_TYPE_VALIDATE_SUBSCRIBERINFO= 19
    set @SCHEMA_TYPE_RETENTIONUNITCHANGE = 29
    set @SCHEMA_TYPE_DROPARTICLE = 28
    set @SCHEMA_TYPE_CHANGE_MERGE_PUBLICATION_90RTM = 302

    -- Security Check
    EXEC @retcode = sys.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    -- make sure current database is enabled for merge replication
    exec @retcode=sys.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
        return (1)

    -- build table for subsequent checks of @property parameter
    CREATE TABLE #tab1 (property sysname collate database_default, allownull bit default(0))
    INSERT INTO #tab1 (property,allownull) VALUES ('description',1)
    INSERT INTO #tab1 (property) VALUES ('status')
    INSERT INTO #tab1 (property) VALUES ('retention')
    INSERT INTO #tab1 (property) VALUES ('retention_period_unit')
    INSERT INTO #tab1 (property) VALUES ('sync_mode')
    INSERT INTO #tab1 (property) VALUES ('allow_push')
    INSERT INTO #tab1 (property) VALUES ('allow_pull')
    INSERT INTO #tab1 (property) VALUES ('allow_anonymous')
    INSERT INTO #tab1 (property) VALUES ('enabled_for_internet')
    INSERT INTO #tab1 (property) VALUES ('centralized_conflicts')
    INSERT INTO #tab1 (property) VALUES ('conflict_logging')
    INSERT INTO #tab1 (property) VALUES ('snapshot_ready')
    INSERT INTO #tab1 (property) VALUES ('snapshot_in_defaultfolder')
    INSERT INTO #tab1 (property,allownull) VALUES ('alt_snapshot_folder',1)
    INSERT INTO #tab1 (property,allownull) VALUES ('pre_snapshot_script',1)
    INSERT INTO #tab1 (property,allownull) VALUES ('post_snapshot_script',1)
    INSERT INTO #tab1 (property) VALUES ('compress_snapshot')
    INSERT INTO #tab1 (property,allownull) VALUES ('ftp_address',1)
    INSERT INTO #tab1 (property) VALUES ('ftp_port')
    INSERT INTO #tab1 (property,allownull) VALUES ('ftp_subdirectory',1)
    INSERT INTO #tab1 (property,allownull) VALUES ('ftp_login',1)
    INSERT INTO #tab1 (property,allownull) VALUES ('ftp_password',1)
    INSERT INTO #tab1 (property) VALUES ('conflict_retention')
    INSERT INTO #tab1 (property) VALUES ('allow_subscription_copy')
    INSERT INTO #tab1 (property) VALUES ('allow_synctoalternate')
    INSERT INTO #tab1 (property) VALUES ('validate_subscriber_info')
    INSERT INTO #tab1 (property) VALUES ('publish_to_ActiveDirectory')
    INSERT INTO #tab1 (property) VALUES ('dynamic_filters')
    INSERT INTO #tab1 (property,allownull) VALUES ('max_concurrent_merge',1)
    INSERT INTO #tab1 (property) VALUES ('max_concurrent_dynamic_snapshots')
    INSERT INTO #tab1 (property,allownull) VALUES ('publication_compatibility_level',1)
    INSERT INTO #tab1 (property) VALUES ('use_partition_groups')
    INSERT INTO #tab1 (property) VALUES ('allow_subscriber_initiated_snapshot')
    INSERT INTO #tab1 (property) VALUES ('keep_partition_changes')
    INSERT INTO #tab1 (property) VALUES ('replicate_ddl')
    INSERT INTO #tab1 (property) VALUES ('allow_web_synchronization')
    INSERT INTO #tab1 (property) VALUES ('web_synchronization_url')
    INSERT INTO #tab1 (property) VALUES ('allow_partition_realignment')
    INSERT INTO #tab1 (property) VALUES ('generation_leveling_threshold')
    INSERT INTO #tab1 (property) VALUES ('automatic_reinitialization_policy')

    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */
    if @property IS NULL
    BEGIN
        select property FROM #tab1 order by property
        RETURN (0)
    END

    if @value is NULL and @property in (select property from #tab1 where allownull=0)
    begin
        RAISERROR (20081, 16, -1, @property)
        RETURN (1)
    end

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

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

    select @pubid = pubid,
           @ad_guidname=ad_guidname, --with value NULL if this publication is not in AD.
           @snapshot_ready=snapshot_ready,
           @dynamic_filters_id=dynamic_filters,
           @sync_modeid = sync_mode,
           @alt_snapshot_folder = alt_snapshot_folder,
           @enabled_for_internet = enabled_for_internet,
           @ftp_address = ftp_address,
           @snapshot_in_defaultfolder = snapshot_in_defaultfolder,
           @compress_snapshot = compress_snapshot,
           @in_ActiveD = case when ad_guidname is NULL then 0 else 1 end,
           @compatlevel = backward_comp_level,
           @retention = retention,
           @retention_period_unit = retention_period_unit,
           @automatic_reinitialization_policy = automatic_reinitialization_policy
      FROM dbo.sysmergepublications
     WHERE name = @publication  and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

    if @pubid IS NULL
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END
    else

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** dbo.sysmergepublications.
    */
    if @property NOT IN (select property from #tab1)
    BEGIN
        RAISERROR (21053, 16, -1, @property)
        RETURN (1)
    END

    /*
    ** Parameter Check:
    ** If sync_method of the publication is character mode (an indication that it supports
    ** third party Subscribers), pre/post-snapshot setting must be null
    **
    */
    IF @sync_modeid = 1
    BEGIN
        IF (@property = 'pre_snapshot_script' OR @property = 'post_snapshot_script')
            AND
            @value IS NOT NULL
            AND
            @value <> ''
        BEGIN
            RAISERROR (21151, 16, -1)
            RETURN (1)
        END
    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

    /*
    ** .. and ftp_address cannot be null if the publication is enabled for
    ** internet.
    */
/*
    IF @enabled_for_internet = 1 AND
      (@property = N'ftp_address'
        AND (@value IS NULL OR @value = N''))
    BEGIN
       RAISERROR(21158, 16, -1)
        RETURN (1)
    END
*/

/*
    IF @enabled_for_internet = 1 AND
       (@property = N'alt_snapshot_folder' AND
        (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IS NULL OR LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N''))
    BEGIN
        RAISERROR(21159, 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

    BEGIN TRAN changemergepublication
    save tran changemergepublication

    exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
                                                  @lockowner = N'Transaction'
    if @retcode<>0 or @@error<>0
    begin
        raiserror(20713, 16, -1, 'sp_changemergepublication', @publication)
        goto UNDO
    end

    select @got_merge_admin_applock = 1

    /*
    ** Changing of the following properties would require a snapshot rerun, if snapshot is ready
    */
    if (@property like 'ftp%' OR @property in ('sync_mode', 'snapshot_in_defaultfolder',
            'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script','compress_snapshot','keep_partition_changes',
            'use_partition_groups', 'publication_compatibility_level'))
    and @snapshot_ready = 1
    begin
        if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
        begin
            raiserror(20607, 16, -1)
            goto UNDO
        end
        update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1
        if @@ERROR<>0   GOTO UNDO
    end

    -- Parameter check: @publication_compatibility_level
    if @property = N'publication_compatibility_level'
    begin
        declare @pubcompat_level_id int
        declare @effectivecomplevel int
        declare @feature nvarchar(4000)

        if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70RTM_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_70RTM
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP1_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_70SP1
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP2_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_70SP2
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP3_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_70SP2
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP4_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_70SP2
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80RTM_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_80RTM
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP1_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_80RTM
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP2_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_80RTM
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP3_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_80RTM
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_90RTM_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_90
        end
        else if UPPER(@value collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_100RTM_string
        begin
            select @pubcompat_level_id = @REPOLEVersion_100
        end
        else
        begin
            raiserror(21402, 16, -1, '@publication_compatibility_level')
            goto UNDO
        end

        if @@error<> 0 or @pubcompat_level_id not in
            (
                @REPOLEVersion_70RTM,
                @REPOLEVersion_70SP1,
                @REPOLEVersion_70SP2,
                @REPOLEVersion_80RTM,
                @REPOLEVersion_90,
                @REPOLEVersion_100
            )
        begin
            raiserror(21402, 16, -1, '@publication_compatibility_level')
            goto UNDO
        end

        /* Get the previous value */
        select @previousnumericvalue = backward_comp_level from  dbo.sysmergepublications
        WHERE pubid = @pubid

        if @previousnumericvalue > @pubcompat_level_id
        begin
            raiserror(22562, 16, -1)
            goto UNDO
        end

        exec @retcode = sys.sp_MSget_effective_pub_compat_level @pubid, @effectivecomplevel output, @feature output, @buildnumber output
        if @retcode <>0 or @@error <> 0
        begin
            raiserror(21402, 16, -1, '@publication_compatibility_level')
            goto UNDO
        end

        if @buildnumber is not null and
           @pubcompat_level_id = @effectivecomplevel
        begin
            raiserror(20004, 10, -1, @publication, @feature, @buildnumber)
        end

        if @pubcompat_level_id < @effectivecomplevel
        begin
            raiserror(20651, 16, -1, @publication, @feature, @effectivecomplevel)
            goto UNDO
        end

        UPDATE dbo.sysmergepublications  SET backward_comp_level = @pubcompat_level_id WHERE pubid = @pubid
        if @@ERROR <> 0
            GOTO UNDO

        /*  Insert event in the MSmerge_settingshistory table if the
        new value is different from the old value
        Look in replsys.sql where the table is created for more
        details about what is in this table. */

        if (@previousnumericvalue <> @pubcompat_level_id)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousnumericvalue,@pubcompat_level_id,NULL)
        end

        select @schemaversion = schemaversion from dbo.sysmergeschemachange
        if (@schemaversion is NULL)
            set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

        set @schemaguid = newid()
        set @artid = null
        set @schematype= @SCHEMA_TYPE_BACKWARD_COMPAT_LEVEL
        select @schematext = 'update dbo.sysmergepublications set backward_comp_level=' + cast(@pubcompat_level_id as nvarchar(5)) + ' where pubid=''' + cast(@pubid as nchar(36)) + ''''
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 goto UNDO
    end


    if @property='dynamic_filters'
    begin
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            select @dynamic_filters_id = 1
        else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false'
            select @dynamic_filters_id = 0
            else
            begin
                raiserror(14137, 16, -1)
                goto UNDO
            end

        declare @previousDynFiltBit int
        select @previousDynFiltBit = dynamic_filters from dbo.sysmergepublications where pubid = @pubid

        if @snapshot_ready>0
        begin
            if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
            begin
                raiserror(20607, 16, -1)
                goto UNDO
            end
            if @force_reinit_subscription = 0
            begin
                raiserror(20608, 16, -1)
                goto UNDO
            end

            -- we should also delete all existing schemachanges for this publication in sysmergeschemachange
            -- this is in order to avoid a number of the problems we have been having with schema processing
            delete from dbo.sysmergeschemachange where pubid = @pubid and schematype not in (@SCHEMA_TYPE_DROPARTICLE)

            update dbo.sysmergepublications set dynamic_filters=@dynamic_filters_id, snapshot_ready=2 where pubid=@pubid
            if @@ERROR<>0
                goto UNDO
            exec @retcode = sys.sp_MSreinitmergepublication
                                    @publication = @publication,
                                    @upload_first = @automatic_reinitialization_policy
            if @retcode<>0 or @@ERROR<>0
                goto UNDO
        end
        else
        begin
            update dbo.sysmergepublications set dynamic_filters=@dynamic_filters_id where pubid=@pubid
            if @@ERROR<>0
                goto UNDO
        end

        -- Insert event into MSmerge_settingshistory
        if (@previousDynFiltBit <> @dynamic_filters_id)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousDynFiltBit,@dynamic_filters_id,NULL)
        end
    end

    if @property='validate_subscriber_info'
    begin
        if @value is not NULL
        begin
            if @dynamic_filters_id = 0
            begin
                raiserror(21313, 16, -1)
                GOTO UNDO
            end
            if exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>@pubid and status<>0)
            begin
                if @force_reinit_subscription = 0
                begin
                    raiserror(20643, 16, -1, @publication)
                    GOTO UNDO
                end
                else
                begin
                    exec @retcode = sys.sp_MSreinitmergepublication
                                            @publication = @publication,
                                            @upload_first = @automatic_reinitialization_policy
                    if @retcode<>0 or @@ERROR<>0 goto UNDO
                end
            end
        end

        declare @previousValSubInfo nvarchar(500)
        SELECT @previousValSubInfo = validate_subscriber_info from dbo.sysmergepublications WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications  SET validate_subscriber_info = @value WHERE pubid = @pubid
        if @@ERROR <> 0 GOTO UNDO

        -- Although a reinit will occur, we add the schemachange so that we can tell
        -- SSCE about the publication property being changed.
        select @schemaversion = schemaversion from dbo.sysmergeschemachange
        if (@schemaversion is NULL)
            set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

        set @schemaguid = newid()
        set @artid = null
        set @schematype= @SCHEMA_TYPE_VALIDATE_SUBSCRIBERINFO
        select @schematext = 'update dbo.sysmergepublications set validate_subscriber_info=''' + replace(@value, '''', '''''') + ''' where pubid=''' + cast(@pubid as nchar(36)) + ''''
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 goto UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousValSubInfo <> @value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousValSubInfo,@value,NULL)
        end

    end


    if @property IN ('description')
        BEGIN
            declare @previousDesc nvarchar(255)
            SELECT @previousDesc = description FROM dbo.sysmergepublications WHERE pubid = @pubid
            UPDATE dbo.sysmergepublications  SET description = @value WHERE pubid = @pubid
            if @@ERROR <> 0 GOTO UNDO

            -- Insert event into MSmerge_settingshistory
            if (@previousDesc <> @value)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousDesc,@value,NULL)
            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 the status is being set to 1, then get the publication ready enough to be able to add merge partitions irrespective of whether
		-- or not the snapshot has been run.
		if @statusid = 1
		begin
			exec @retcode = sys.sp_MSpublicationview @publication
			if @@error <> 0 or @retcode <> 0 goto UNDO
			
			exec @retcode = sys.sp_MSset_dynamic_filter_options @publication
			if @@error <> 0 goto UNDO
			
			if exists (select * from dbo.sysmergepublications where pubid = @pubid and use_partition_groups = 2)
			begin
				exec @retcode = sys.sp_MSsetup_use_partition_groups @publication
				if @@error <> 0 goto UNDO
			end
		end
		
        /*
        ** Update the publication with the new status.
        */
        declare @previousStatus tinyint
        SELECT @previousStatus = status FROM dbo.sysmergepublications WHERE pubid = @pubid
        UPDATE dbo.sysmergepublications SET status = @statusid WHERE pubid = @pubid

        if @@ERROR <> 0 GOTO UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousStatus <> @statusid)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousStatus,@statusid,NULL)
        end

    END


    if @property = 'retention'
    BEGIN
        /*
        ** Update the publication with the new replication frequency.
        */

        select @retention = CONVERT(int, @value)
        if @@error <>0 goto UNDO

        if @retention is NULL
        begin
            select @retention = 0
        end
        else if @retention < 0 or @retention > sys.fn_units_until_maxdate(@retention_period_unit, getdate())
        begin
            raiserror(20050, 16, -1, 0)
            GOTO UNDO
        end

        declare @oldRetentionValue int
        SELECT @oldRetentionValue = retention from dbo.sysmergepublications WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications set retention = @retention WHERE pubid = @pubid
        if @@ERROR <> 0
            GOTO UNDO

        select @schemaversion = schemaversion from dbo.sysmergeschemachange
        if (@schemaversion is NULL)
            set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

        set @schemaguid = newid()
        set @artid = null
        set @schematype = @SCHEMA_TYPE_RETENTIONCHANGE
        select @schematext = 'exec dbo.sp_MSchange_retention '+ '''' + convert(nchar(36),@pubid) + '''' + ',' + '''' + @value + ''''
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 goto UNDO

        if @oldRetentionValue <> @retention
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@oldRetentionValue,@retention,NULL)
        end
    END

    if @property = 'retention_period_unit'
    BEGIN

        select @pubcompat_level_id = backward_comp_level from dbo.sysmergepublications
        where pubid = @pubid

        if @pubcompat_level_id < @REPOLEVersion_90
        begin
            raiserror(22529, 16, -1, @publication)
            goto UNDO
        end

        if @value is null
        or lower(@value collate SQL_Latin1_General_CP1_CS_AS) not in ('day', 'days', 'dd', 'year', 'years', 'yy', 'yyyy', 'month', 'months', 'mm', 'week', 'weeks', 'wk', 'hour', 'hours', 'hh', 'minute', 'minutes', 'mi')
        begin
            raiserror(22527, 16, -1, @value)
            goto UNDO
        end

        select @retention_period_unit =
            case    when lower(@value collate SQL_Latin1_General_CP1_CS_AS) in ('day', 'days', 'dd')
                        then 0
                    when lower(@value collate SQL_Latin1_General_CP1_CS_AS) in ('week', 'weeks', 'wk')
                        then 1
                    when lower(@value collate SQL_Latin1_General_CP1_CS_AS) in ('month', 'months', 'mm')
                        then 2
                    when lower(@value collate SQL_Latin1_General_CP1_CS_AS) in ('year', 'years', 'yy', 'yyyy')
                        then 3
                    when lower(@value collate SQL_Latin1_General_CP1_CS_AS) in ('hour', 'hours', 'hh')
                        then 4
                    when lower(@value collate SQL_Latin1_General_CP1_CS_AS) in ('minute', 'minutes', 'mi')
                        then 5
            end

        if @retention_period_unit <> 0 and @pubcompat_level_id < @REPOLEVersion_90
        begin
            raiserror(22528, 16, -1, @publication)
            goto UNDO
        end

        if @retention_period_unit <> 0 and @sync_modeid = 1
        begin
            raiserror(22555, 16, -1, @publication)
            goto UNDO
        end

        if @retention > sys.fn_units_until_maxdate(@retention_period_unit, getdate())
        begin
            raiserror(20050, 16, -1, 0)
            GOTO UNDO
        end

        declare @previousRPU tinyint
        SELECT @previousRPU = retention_period_unit FROM dbo.sysmergepublications WHERE pubid = @pubid
        UPDATE dbo.sysmergepublications set retention_period_unit = @retention_period_unit WHERE pubid = @pubid
        if @@ERROR <> 0
            GOTO UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousRPU <> @retention_period_unit)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousRPU,@retention_period_unit,NULL)
        end

        select @schemaversion = schemaversion from dbo.sysmergeschemachange
        if (@schemaversion is NULL)
            set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

        set @schemaguid = newid()
        set @artid = null
        set @schematype = @SCHEMA_TYPE_RETENTIONUNITCHANGE
        select @schematext = 'exec dbo.sp_MSchange_retention_period_unit '+ '''' + convert(nchar(36),@pubid) + '''' + ',' + convert(nvarchar, @retention_period_unit)
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 goto UNDO
    END

    if @property = 'conflict_retention'
    BEGIN
        /*
        ** Update the publication with the new conflict_retention value
        */

        select @retention = CONVERT(int, @value)
        if @@ERROR<>0
            GOTO UNDO

        if @value is NULL
            select @retention = 0

        if @retention < 0
            begin
                raiserror(20050, 16, -1, 0)
                GOTO UNDO
            end

        declare @previousConflictRet int
        SELECT @previousConflictRet = conflict_retention FROM dbo.sysmergepublications where pubid = @pubid

        UPDATE dbo.sysmergepublications
            SET conflict_retention = @retention
            WHERE pubid = @pubid

        if @@ERROR <> 0
            GOTO UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousConflictRet <> @retention)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousConflictRet,@retention,NULL)
        end

    END

    if @property = 'max_concurrent_merge'
    BEGIN
        /*
        ** Update the publication property  with a new value
        */

        select @max_concurrent_merge = CONVERT(int, @value)
        if @max_concurrent_merge < 0
        begin
            raiserror(21402, 16, -1, '@value')
            GOTO UNDO
        end

        declare @previousMaxConcMrg int

        SELECT @previousMaxConcMrg = max_concurrent_merge FROM dbo.sysmergepublications
            WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
            SET max_concurrent_merge = @max_concurrent_merge
            WHERE pubid = @pubid

        if @@ERROR <> 0
            GOTO UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousMaxConcMrg <> @max_concurrent_merge)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousMaxConcMrg,@max_concurrent_merge,NULL)
        end
    END


    if @property = 'sync_mode'
    BEGIN
        /*
        ** Check for a valid synchronization method.
        */
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)='portable' select @value='character'

        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('native', 'character', 'bcp native', 'bcp character')
        begin
            raiserror (20076, 16, -1)
            GOTO UNDO
        end

        /*
        ** Determine the integer value for the sync_mode.
        */
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('native', 'bcp native')
           select @sync_modeid = 0
        else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('character', 'bcp character')
            select @sync_modeid = 1

        -- logical records not supported with char mode bcp (CE subscribers)
        if @sync_modeid = 1 and (exists (select * from dbo.sysmergesubsetfilters where pubid = @pubid
                                        and filter_type & 2 = 2)
                                or exists (select * from dbo.sysmergepartitioninfo where pubid = @pubid
                                            and (logical_record_level_conflict_detection = 1 or logical_record_level_conflict_resolution = 1)))
        begin
            raiserror(22540, 16, -1, @publication)
            goto UNDO
        end

        if @sync_modeid = 1 and exists (select * from dbo.sysmergepublications where pubid = @pubid
                                        and retention_period_unit <> 0)
        begin
            raiserror(22554, 16, -1, @publication)
            goto UNDO
        end

        /*
        ** Update the publication with the new synchronization method.
        */
        declare @previousSyncMode tinyint
        SELECT @previousSyncMode = sync_mode FROM dbo.sysmergepublications
         WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
           SET sync_mode = @sync_modeid
         WHERE pubid = @pubid

        if @@ERROR <> 0 GOTO UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousSyncMode <> @sync_modeid)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousSyncMode,@sync_modeid,NULL)
        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

        declare @previousAltSnapFolder nvarchar(255)
        SELECT @previousAltSnapFolder = alt_snapshot_folder FROM dbo.sysmergepublications
             WHERE pubid = @pubid

        IF @value IS NULL OR @value = N''
        BEGIN
            UPDATE dbo.sysmergepublications
               SET alt_snapshot_folder = @value,
                   compress_snapshot = 0,
                   enabled_for_internet = 0
             WHERE pubid = @pubid
        END
        ELSE
        BEGIN
            UPDATE dbo.sysmergepublications
               SET alt_snapshot_folder = @value
             WHERE pubid = @pubid
        END
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END
        exec @retcode=sys.sp_MSrefreshdynamicsnapshotlocations @pubid
        if @@error<>0 or @retcode<>0
            goto UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousAltSnapFolder <> @value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousAltSnapFolder,@value,NULL)
        end

    END

    IF @property = N'pre_snapshot_script'
    BEGIN
        declare @previousPreSnapScript nvarchar(255)
        SELECT @previousPreSnapScript = pre_snapshot_script FROM dbo.sysmergepublications
         WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
           SET pre_snapshot_script = @value
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END

        -- Insert event into MSmerge_settingshistory
        if (@previousPreSnapScript <> @value)
  begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousPreSnapScript,@value_bit,NULL)
        end

    END

    IF @property = N'post_snapshot_script'
    BEGIN
        declare @previousPostSnapScript nvarchar(255)

        SELECT @previousPostSnapScript = post_snapshot_script FROM dbo.sysmergepublications
         WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
           SET post_snapshot_script = @value
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END

        -- Insert event into MSmerge_settingshistory
        if (@previousPostSnapScript <> @value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousPostSnapScript,@value_bit,NULL)
        end
    END

    IF @property = N'ftp_address'
    BEGIN
        declare @previousFtpAddr sysname
        SELECT @previousFtpAddr = ftp_address FROM dbo.sysmergepublications
             WHERE pubid = @pubid

        IF @value IS NULL OR @value = N''
        BEGIN
            UPDATE dbo.sysmergepublications
               SET ftp_address = @value,
                   enabled_for_internet = 0
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
            exec @retcode=sys.sp_MSrefreshdynamicsnapshotlocations @pubid
            if @@error<>0 or @retcode<>0
                goto UNDO
        END
        ELSE
        BEGIN
            UPDATE dbo.sysmergepublications
               SET ftp_address = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END

        -- Insert event into MSmerge_settingshistory
        if (@previousFtpAddr <> @value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousFtpAddr,@value,NULL)
        end
    END

    IF @property = N'ftp_port'
    BEGIN
        declare @previousFtpPort int
        SELECT @previousFtpPort = ftp_port FROM dbo.sysmergepublications
              WHERE pubid = @pubid

        SET @numeric_value = CONVERT(int, @value)

        UPDATE dbo.sysmergepublications
           SET ftp_port = @numeric_value
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END

        -- Insert event into MSmerge_settingshistory
        if (@previousFtpPort <> @numeric_value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousFtpPort,@value,NULL)
        end

    END

    IF @property = N'ftp_subdirectory'
    BEGIN

        declare @previousFtpSubdir nvarchar(255)
        SELECT @previousFtpSubdir = ftp_subdirectory FROM dbo.sysmergepublications
               WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
           SET ftp_subdirectory = @value
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END
        exec @retcode=sys.sp_MSrefreshdynamicsnapshotlocations @pubid
        if @@error<>0 or @retcode<>0
            goto UNDO

        -- Insert event into MSmerge_settingshistory
        if (@previousFtpSubdir <> @value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousFtpSubdir,@value,NULL)
        end
    END

    IF @property = N'ftp_login'
    BEGIN
        declare @previousFtpLogin sysname
        SELECT @previousFtpLogin = ftp_login FROM dbo.sysmergepublications WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
           SET ftp_login = @value
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END

        -- Insert event into MSmerge_settingshistory
        if (@previousFtpLogin <> @value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousFtpLogin,@value,NULL)
        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 dbo.sysmergepublications
           SET ftp_password = @enc_ftp_password
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
           GOTO UNDO
        END
    END

    IF @property = N'max_concurrent_dynamic_snapshots'
    BEGIN
        SELECT @numeric_value = CONVERT(int, @value)
        IF @@ERROR<>0 OR @numeric_value < 0 OR @numeric_value IS NULL
        BEGIN
            RAISERROR(21403, 16, -1)
            GOTO UNDO
        END

        declare @previousMaxConcDynSnap int
        SELECT @previousMaxConcDynSnap = max_concurrent_dynamic_snapshots FROM dbo.sysmergepublications WHERE pubid = @pubid

        UPDATE dbo.sysmergepublications
           SET max_concurrent_dynamic_snapshots = @numeric_value
         WHERE pubid = @pubid
        IF @@error <> 0
        BEGIN
            GOTO UNDO
        END

        -- Insert event into MSmerge_settingshistory
        if (@previousMaxConcDynSnap <> @numeric_value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousMaxConcDynSnap,@numeric_value,NULL)
        end
    END

    IF @property = N'replicate_ddl'
    BEGIN
        SELECT @numeric_value = CONVERT(int, @value)
        IF @@ERROR<>0 OR (@numeric_value NOT in (0, 1) ) OR @numeric_value IS NULL
        BEGIN
            RAISERROR(21544, 16, -1)
            GOTO UNDO
        END

        if @numeric_value = 1 and @compatlevel < 90
        begin
            RAISERROR(20650, 16, -1, @publication)
            GOTO UNDO
        end

        /* Get the previous value */
        select @previousnumericvalue = replicate_ddl from  dbo.sysmergepublications
        WHERE pubid = @pubid

        /* Update the replicate_ddl column */
        UPDATE dbo.sysmergepublications
            SET replicate_ddl = @numeric_value
            WHERE pubid = @pubid
        if @@error <> 0
        BEGIN
           GOTO UNDO
        END

        /*  Insert event in the MSmerge_settingshistory table if the
    new value is different from the old value
        Look in replsys.sql where the table is created for more
        details about what is in this table. */

        if (@previousnumericvalue <> @numeric_value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousnumericvalue,@numeric_value,NULL)
        end

    END

   if @property = 'publish_to_activedirectory'
   BEGIN
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14137, 16, -1)
            GOTO UNDO
        END
    END
    else if @property in ('conflict_logging', 'centralized_conflicts')
    begin
        set @value= LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)

        if @property = 'conflict_logging'
        begin
            set @numeric_value=
                    case @value
                        when 'publisher' then 1
                        when 'subscriber' then 0
                        when 'both' then 2
                        else null
                    end

            if @numeric_value is null
            begin
                RAISERROR (14103, 16, -1, 'conflict_logging')
                goto UNDO
            end
        end
        else
        begin
            raiserror (21767, 10, 1, 'centralized_conflicts', 'conflict_logging')

            set @numeric_value=
                    case @value
                        when 'true' then 1
                        when 'false' then 0
                        else null
                    end

            if @numeric_value is null
            begin
                RAISERROR (14148, 16, -1, 'decentralized_conflicts')
                goto UNDO
            end
        end

        declare @old_centralized_conflicts int
        declare @old_decentralized_conflicts int
        declare @new_centralized_conflicts int
        declare @new_decentralized_conflicts int

        -- conflict_logging of 'both' is allowed for 90RTM and higher compat levels only.
        if @numeric_value = 2 and @compatlevel < @REPOLEVersion_90
        begin
            RAISERROR (20705, 16, -1, @publication)
            goto UNDO
        end

        if @numeric_value = 0 and @compatlevel < 30
        begin
            EXEC @retcode = sys.sp_MSBumpupCompLevel @pubid, 30
            IF @@ERROR<>0 OR @retcode<>0 GOTO UNDO
        end

        select @old_centralized_conflicts = centralized_conflicts,
               @old_decentralized_conflicts = decentralized_conflicts
           from dbo.sysmergepublications where pubid=@pubid

        if @numeric_value = 0
        begin
            set @new_centralized_conflicts = 0
            set @new_decentralized_conflicts = 1
        end

        if @numeric_value = 1
        begin
            set @new_centralized_conflicts = 1
            set @new_decentralized_conflicts = 0
        end

        if @numeric_value = 2
        begin
            set @new_centralized_conflicts = 1
            set @new_decentralized_conflicts = 1
        end

        -- changing from conflict logging type of publisher to both or subscriber will require a reinit
        if @snapshot_ready>0 and @old_decentralized_conflicts = 0 and @new_decentralized_conflicts = 1
        begin
            if @force_reinit_subscription = 0
            begin
                raiserror(20608, 16, -1)
                goto UNDO
            end
            exec @retcode = sp_MSreinitmergepublication @publication
            if @@ERROR<>0 or @retcode<>0
                goto UNDO
        end

        update dbo.sysmergepublications
            set centralized_conflicts = @new_centralized_conflicts,
                decentralized_conflicts = @new_decentralized_conflicts
            where pubid = @pubid

        if (@old_centralized_conflicts <> @new_centralized_conflicts)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,N'centralized_conflicts',@old_centralized_conflicts,@new_centralized_conflicts,NULL)

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,N'decentralized_conflicts',@old_decentralized_conflicts,@new_decentralized_conflicts,NULL)
        end

        select @schemaversion = schemaversion from dbo.sysmergeschemachange
        if (@schemaversion is NULL)
            set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

        set @schemaguid = newid()
        set @artid = null
        set @schematype = @SCHEMA_TYPE_CHANGE_MERGE_PUBLICATION_90RTM
        select @schematext = 'exec dbo.sp_MSchange_mergepublication @pubid = ''' + convert(nchar(36), @pubid) + ''', @property = ''' + @property + ''', @value = ''' + cast(@numeric_value as nvarchar) + ''''
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 goto UNDO
    end
    else if @property = 'generation_leveling_threshold'
    begin

        set @numeric_value= convert(int, @value)

        if @numeric_value is null
            select @numeric_value = 0

        if @numeric_value < 0
        begin
            raiserror(21119, 16, -1, @numeric_value, 'generation_leveling_threshold')
            goto UNDO
        end

        declare @previousGenLevelThresh int
        SELECT @previousGenLevelThresh = generation_leveling_threshold FROM dbo.sysmergepublications WHERE pubid = @pubid

        update dbo.sysmergepublications
            set generation_leveling_threshold = @numeric_value
            where pubid = @pubid

        -- Insert event into MSmerge_settingshistory
        if (@previousGenLevelThresh <> @numeric_value)
        begin
            select @eventtype       = 2 -- Change in publication level property

            insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
            values (default,@pubid,NULL,@eventtype,@property,@previousGenLevelThresh,@numeric_value,NULL)
        end

        select @schemaversion = schemaversion from dbo.sysmergeschemachange
        if (@schemaversion is NULL)
            set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

        set @schemaguid = newid()
        set @artid = null
        set @schematype = @SCHEMA_TYPE_CHANGE_MERGE_PUBLICATION_90RTM
        select @schematext = 'exec dbo.sp_MSchange_mergepublication @pubid = ''' + convert(nchar(36), @pubid) + ''', @property = ''' + @property + ''', @value = ''' + cast(@numeric_value as nvarchar) + ''''
        exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 goto UNDO
    end
    else if @property IN ('allow_push', 'allow_pull', 'allow_anonymous', 'enabled_for_internet',
        'snapshot_ready', 'snapshot_in_defaultfolder', 'compress_snapshot',
        'allow_subscription_copy', 'allow_synctoalternate', 'allow_web_synchronization','use_partition_groups', 'allow_subscriber_initiated_snapshot', 'keep_partition_changes',
        'allow_partition_realignment')
    BEGIN

        /*
        ** Check for a valid  value.
        */

        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14137, 16, -1)
            GOTO UNDO
        END

        /*
        ** set value bit
        */
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            select @value_bit = 1
        else
            select @value_bit = 0

        if @property = 'allow_anonymous'
        BEGIN
            declare @previousAllowAnon int
            SELECT @previousAllowAnon = allow_anonymous FROM dbo.sysmergepublications WHERE pubid = @pubid

            /* Update the allow_anonymous column */
            UPDATE dbo.sysmergepublications
                SET allow_anonymous = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END
            -- Insert event into MSmerge_settingshistory
            if (@previousAllowAnon <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousAllowAnon,@value_bit,NULL)
            end
        END

        if @property = 'allow_push'
        BEGIN
            declare @previousAllowPush int
            SELECT @previousAllowPush = allow_push FROM dbo.sysmergepublications WHERE pubid = @pubid

            /* Update the allow_push column */
            UPDATE dbo.sysmergepublications
                SET allow_push = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END
            -- Insert event into MSmerge_settingshistory
            if (@previousAllowPush <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousAllowPush,@value_bit,NULL)
            end
        END

        if @property = 'allow_pull'
        BEGIN
            declare @previousAllowPull int
            SELECT @previousAllowPull = allow_pull FROM dbo.sysmergepublications WHERE pubid = @pubid

            /* Update the allow_pull column */
            UPDATE dbo.sysmergepublications
                SET allow_pull = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END

            -- Insert event into MSmerge_settingshistory
            if (@previousAllowPull <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousAllowPull,@value_bit,NULL)
            end

        END

        if @property = 'enabled_for_internet'
        BEGIN
            declare @previousEnableForInternet int
            SELECT @previousEnableForInternet = enabled_for_internet FROM dbo.sysmergepublications WHERE pubid = @pubid

            UPDATE dbo.sysmergepublications
               SET enabled_for_internet = @value_bit
             WHERE pubid = @pubid

            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
            exec @retcode=sys.sp_MSrefreshdynamicsnapshotlocations @pubid
            if @@error<>0 or @retcode<>0
     goto UNDO

            -- Insert event into MSmerge_settingshistory
            if (@previousEnableForInternet <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousEnableForInternet,@value_bit,NULL)
            end

        END

        if @property = 'snapshot_ready'
        BEGIN
            declare @previousSnapshotReadyValue tinyint
            SELECT @previousSnapshotReadyValue = snapshot_ready from dbo.sysmergepublications WHERE pubid = @pubid

            declare @newSnapshotReadyValue tinyint
            SET @newSnapshotReadyValue = case @value_bit when 1 then 1 else 2 end

            -- snapshot_ready should not be set to 0. If snapshot_ready was 1 then
            -- set it to 2 to invalidate it.
            UPDATE dbo.sysmergepublications
                SET snapshot_ready = @newSnapshotReadyValue
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END

            -- Insert event in MSmerge_settingshistory
            if (@previousSnapshotReadyValue <> @newSnapshotReadyValue)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousSnapshotReadyValue,@newSnapshotReadyValue,NULL)
            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

            declare @previousSnapInDefaultFolder int
            SELECT @previousSnapInDefaultFolder = snapshot_in_defaultfolder FROM dbo.sysmergepublications WHERE pubid = @pubid

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

            -- Insert event into MSmerge_settingshistory
            if (@previousSnapInDefaultFolder <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousSnapInDefaultFolder,@value_bit,NULL)
            end

            -- Bump up the compatibility level if we are
            -- setting snapshot_in_defaultfolder to 0
            -- and compression is enabled. i.e. only
            -- a compressed snapshot will be generated
            -- Note that alt_snapshot_folder is implicitly
            -- specified for the publication
            IF @value_bit = 0 and @compress_snapshot = 1 and @compatlevel < 40
            BEGIN
                EXEC @retcode = sys.sp_MSBumpupCompLevel @pubid, 40
                IF @@ERROR<>0 OR @retcode<>0
                    GOTO UNDO
            END

        END

        IF @property = 'compress_snapshot'
        BEGIN
            declare @previousCompressSnap int
            SELECT @previousCompressSnap = compress_snapshot FROM dbo.sysmergepublications WHERE pubid = @pubid

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

            -- Insert event into MSmerge_settingshistory
            if (@previousCompressSnap <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousCompressSnap,@value_bit,NULL)
            end

            -- See comment for 'snapshot_in_defaultfolder'
            IF @value_bit = 1 and @snapshot_in_defaultfolder = 0 and @compatlevel < 40
            BEGIN
                EXEC @retcode = sys.sp_MSBumpupCompLevel @pubid, 40
                IF @@ERROR<>0 OR @retcode<>0
                    GOTO UNDO
            END
        END

        if @property = 'allow_subscription_copy'
        BEGIN

            declare @previousAllowSubCopy int
            SELECT @previousAllowSubCopy = allow_subscription_copy FROM dbo.sysmergepublications WHERE pubid = @pubid

            /* Update the allow_subscription_copy column */
            UPDATE dbo.sysmergepublications
                SET allow_subscription_copy = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END

            -- Insert event into MSmerge_settingshistory
            if (@previousAllowSubCopy <> @value_bit)
            begin
                select @eventtype       = 2 -- Change in publication level property

                insert into dbo.MSmerge_settingshistory (eventtime,pubid,artid,eventtype,propertyname,previousvalue,newvalue,eventtext)
                values (default,@pubid,NULL,@eventtype,@property,@previousAllowSubCopy,@value_bit,NULL)
            end

            select @schemaversion = schemaversion from dbo.sysmergeschemachange
            if (@schemaversion is NULL)
                set @schemaversion = 1
            else
                select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

            set @schemaguid = newi
 
Last revision 2008RTM
See also

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