Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_changearticle

  No additional text.


Syntax
create procedure sys.sp_MSrepl_changearticle
(
    @publication				sysname,
    @article					sysname,
    @property					nvarchar(100),
    @value						nvarchar(386),
    @force_invalidate_snapshot	bit,
    @force_reinit_subscription	bit,
    @publisher					sysname,
    @publisher_type				sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @artid int
            ,@cmd1 nvarchar(512)
            ,@cmd2 nvarchar(512)
            ,@db sysname
            ,@filter int
            ,@object sysname
            ,@owner sysname
            ,@pubid int
            ,@retcode int
            ,@site sysname
            ,@sync_objid int
            ,@typeid tinyint
            ,@old_typeid tinyint
            ,@precmdid tinyint
            ,@active tinyint
            ,@virtual_id smallint
            ,@article_type tinyint
            ,@repl_freq int
            ,@colname sysname
            ,@objid    int
            ,@objtype  nchar(2)
            ,@old_filter_name sysname

            ,@distributor sysname
            ,@distribdb sysname
            ,@dbname sysname
            ,@distproc nvarchar (255)
            ,@dts_part nvarchar(50)
            ,@no_dts_part nvarchar(50)
            ,@backward_comp_level int
            ,@allow_dts bit
            ,@allow_queued_tran bit
            ,@allow_sync_tran bit
            ,@allow_initialize_from_backup bit
            ,@increment_min_autonosync_lsn bit
            ,@srvname sysname
            ,@pubstatus tinyint
            ,@options int
            ,@for_p2p_ddl int
            ,@auto_gen int
            ,@fire_triggers_on_snapshot bit
            ,@customprocmaxlen_minus_ccs_appendix smallint
            ,@timestampcolumn sysname

	DECLARE @OPT_ENABLED_FOR_P2P int	
	SELECT @OPT_ENABLED_FOR_P2P = 0x1
	
    select @backward_comp_level = 10 -- default to sphinx
            ,@dts_part = N'dts horizontal partitions'
            ,@no_dts_part = N'no dts horizontal partitions'
            ,@active = 2
            ,@virtual_id = -1
            ,@increment_min_autonosync_lsn = 0
            ,@srvname = publishingservername()
            ,@customprocmaxlen_minus_ccs_appendix = 117 -- (128-11)


	IF (@publisher_type != N'MSSQLSERVER')
	BEGIN
		-- Set servername to publisher when HREPL
		SET @srvname = @publisher
	END

    /*
    ** 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:  @publication.
    ** Make sure that the publication exists.
    */

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

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

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

	SELECT	@pubstatus						= status,
			@repl_freq						= repl_freq,
			@allow_dts						= allow_dts,
			@allow_sync_tran				= allow_sync_tran,
			@allow_queued_tran				= allow_queued_tran,
			@allow_initialize_from_backup	= allow_initialize_from_backup,
			@options						= options
	FROM	syspublications
	WHERE	pubid = @pubid

    /*
    ** Check to see that the article exists in sysextendedarticlesview.
    ** Fetch the article identification number.
    */

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

	-- NOTE: HREPL doesn't support autogen/schema replication
	--       @auto_gen should *always* be 0 for HREPL
 SELECT	@artid			= artid,
			@article_type	= type,
			@objid			= objid,
			@auto_gen		= CASE
								WHEN @publisher_type = N'MSSQLSERVER' THEN (convert(int, schema_option) & 0x2)
								ELSE 0
							  END
    FROM	sysextendedarticlesview
    WHERE	name = @article
	  AND	pubid = @pubid

    IF @artid IS NULL
    BEGIN
        RAISERROR (20027, 11, -1, @article)
        RETURN (1)
    END


	IF (@publisher_type = N'MSSQLSERVER')
	BEGIN
		-- Get the object type
		SELECT	@objtype = type
		FROM	sys.objects
		WHERE	object_id = @objid

		IF @objtype IS NULL
		BEGIN
			RAISERROR( 20027, 11, -1, @article )
			RETURN( 1 )
		END
	END
	ELSE
	BEGIN
		-- All HREPL objects are treated as user tables
		SET @objtype = 'U'
	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 ('sync_object (log based article only)')
            INSERT INTO #tab1 VALUES ('type')
            INSERT INTO #tab1 VALUES ('ins_cmd (log based article only)')
            INSERT INTO #tab1 VALUES ('del_cmd (log based article only)')
            INSERT INTO #tab1 VALUES ('upd_cmd (log based article only)')
            INSERT INTO #tab1 VALUES ('filter (log based article only)')
            INSERT INTO #tab1 VALUES ('dest_table (log based article only)')
            INSERT INTO #tab1 VALUES ('dest_object')
            INSERT INTO #tab1 VALUES ('creation_script')
            INSERT INTO #tab1 VALUES ('pre_creation_cmd')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('schema_option')
            INSERT INTO #tab1 VALUES ('destination_owner')
            INSERT INTO #tab1 VALUES ('pub_identity_range (log based article only)')
            INSERT INTO #tab1 VALUES ('identity_range (log based article only)')
            INSERT INTO #tab1 VALUES ('threshold (log based article only)')
            INSERT INTO #tab1 VALUES ('tablespace')
            INSERT INTO #tab1 VALUES ('fire_triggers_on_snapshot (log based article only)')
            PRINT ''
            SELECT * FROM #tab1
            RETURN (0)
        END

    -- Pre-lower @property so we don't have to lower it dynamically below
    SELECT @property = LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)

    /*
    ** (!!All of the following restrictions are temporarily lifted)
    ** Enforce automated nosync subscription restrictions
    ** i) No horizontal partitioning
    ** ii) Dest. object name cannot be different from source
    ** iv) Dest. owner name cannot be different from source
    */
--    IF @allow_initialize_from_backup = 1
--    BEGIN

--        -- i)
--        IF @property = N'type' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN
--                                        ('logbased manualfilter',
--                                         'logbased manualview',
--                                         'logbased manualboth') OR
--           @property = N'filter' AND @value IS NOT NULL AND RTRIM(@value) <> N'' OR
--           @property = N'sync_object' AND OBJECT_ID(@value) <> @objid
--        BEGIN
--            RAISERROR (18792, 16, -1)
--            RETURN (1)
--        END
--        -- ii)
--        IF (@property = N'dest_table' OR @property = N'dest_object') AND @value <> OBJECT_NAME(@objid)
--        BEGIN
--            RAISERROR (18791, 16, -1)
--            RETURN (1)
--        END

--        -- iii)
--        IF @property = N'destination_owner' AND @value <> schema_name(OBJECTPROPERTY(@objid, 'SchemaId'))
--        BEGIN
--            RAISERROR (18791, 16, -1)
--            RETURN (1)
--        END
--    END

	/*
		Only allow the call format to be changed as it does not require a reinit.
	*/
	SELECT @for_p2p_ddl = @options & @OPT_ENABLED_FOR_P2P
	IF @for_p2p_ddl = @OPT_ENABLED_FOR_P2P
	BEGIN
		IF @property NOT IN ('description',
								'ins_cmd',
								'del_cmd',
								'upd_cmd')
		BEGIN
			-- The property '@property' cannot be modified for Peer-To-Peer publications.
			RAISERROR(20647, 16, -1, @property)
			RETURN (1)
		END

		IF @property IN ('ins_cmd',
							'del_cmd',
							'upd_cmd')
			AND LEFT(UPPER(LTRIM(@value)), 3) IN (N'SQL')
		BEGIN
			-- Peer-To-Peer publications do not support 'SQL' command type. Please change the '@value' parameter value.
			RAISERROR(20646, 16, -1, '''SQL'' Command Type',  '@value')
			RETURN (1)
		END
	END
	
    /*
    ** Increment the publication's min. autonosync lsn if filtering properties
    ** are changed or the destination object/owner name is changed
    */
    IF @allow_initialize_from_backup = 1
    BEGIN
        IF @property IN ('ins_cmd',
                         'del_cmd',
                         'upd_cmd',
                         'filter',
                         'type',
                         'sync_object',
                         'dest_table',
                         'dest_object',
                         'destination_owner'
                         )
        BEGIN
            SELECT @increment_min_autonosync_lsn = 1
        END
    END

    /*
    ** At this point, we have completed all the validations and
    ** preprocessings common to both regular and schema only articles
    ** so we call a different proceudre here to handle the schema only
    ** articles differently.
    */

    IF @article_type in (0x20, 0x40, 0x60, 0xA0, 0x80)
    BEGIN
        EXEC @retcode = sys.sp_MSchangeschemaarticle
                @pubid = @pubid,
                @artid = @artid,
                @property = @property,
                @value = @value
        RETURN @retcode
    END

    IF @objtype = 'U' AND @property NOT IN
                                        ('description',
                                         'sync_object',
                                         'type',
                                         'ins_cmd',
                                         'del_cmd',
                                         'upd_cmd',
                                         'filter',
                                         'dest_table',
                                         'dest_object',
                                         'creation_script',
                                         'pre_creation_cmd',
                                         'status',
                                         'schema_option',
                                         'destination_owner',
                                         'pub_identity_range',
                                         'identity_range',
                                         'threshold',
                                         'tablespace',
                                         'fire_triggers_on_snapshot')
	BEGIN
		RAISERROR (21183, 16, -1, @property)
		RETURN (1)
	END

    IF @objtype = 'P' AND @property NOT IN
                            ('description',
                             'dest_object',
                             'dest_table',
                             'creation_script',
                             'pre_creation_cmd',
                             'schema_option',
                             'destination_owner')
        BEGIN
            RAISERROR (14110, 16, -1)
            RETURN (1)
        END

    /* dest_object and 'dest_table' are same */
    IF @property = 'dest_object'
        SELECT @property = 'dest_table'

	-- Validate HREPL-only properties
	IF @publisher_type NOT LIKE N'ORACLE%'
	BEGIN
		IF @property = 'tablespace'
		BEGIN
			RAISERROR(21737, 16, -1, @property, @publisher_type)
			RETURN (1)
		END
	END
	
	-- Validate property combinations for HREPL
	IF @publisher_type != N'MSSQLSERVER'
	BEGIN
		IF (@property IN ('dest_table', 'schema_option', 'destination_owner', 'status', 'pre_creation_cmd')
			OR (@property IN ('ins_cmd', 'del_cmd', 'upd_cmd', 'filter') AND (@pubstatus = 1)))
			AND (@force_invalidate_snapshot = 0 OR @force_reinit_subscription = 0)
		BEGIN
			RAISERROR(21772, 16, -1, @property)
			RETURN (1)
		END
		
		IF (@property IN ('tablespace') AND @repl_freq = 1)
		BEGIN
			RAISERROR(21738, 16, -1, @property, 'snapshot')
			RETURN (1)
		END
		
		-- Verify that sp_articleview has been called to insure that
		-- the log table has been created and exists to be moved
		IF (@property IN ('tablespace') AND
			NOT EXISTS (SELECT * FROM dbo.IHarticles WHERE article_id = @artid AND publisher_status = 1)
			)
		BEGIN
			RAISERROR(21739, 16, -1, @property)
			RETURN (1)
		END
	END
		
    
    -- Processing for custom stored procedures
    
    IF @property IN ('ins_cmd', 'del_cmd', 'upd_cmd' )
    begin
        if (@allow_dts = 1)
        begin
            
            -- For DTS enabled publications the custom procedure are autogenerated
            -- and cannot be changed
            
            raiserror(21175, 16, -1)
            return (1)
        end
        else if (@allow_queued_tran = 1 or @allow_sync_tran = 1)
        begin
            
            -- Updating subscribers
            -- INS/UPD/DEL format is CALL/VCALL/VCALL
            -- we will allow XCALL for UPD/DEL for backwards compatibility
            
            declare @newcmd nvarchar(255)

            
            -- validate custom command
            
            select @newcmd = case
                            when (@value is not null and @property = 'ins_cmd' and (upper(left(@value, 5)) = N'CALL') ) then @value
                            when (@value is not null and @property = 'upd_cmd' and (upper(left(@value, 5)) in (N'XCALL', N'VCALL')) ) then @value
                            when (@value is not null and @property = 'del_cmd' and (upper(left(@value, 5)) in (N'XCALL', N'VCALL')) ) then @value
                            else NULL end
            if (@newcmd is null)
            begin
                
                -- specified invalid value for the property for updating subscribers
                
                raiserror(21344, 16, 1, @property)
                return (1)
            end
        end -- if (@allow_queued_tran = 1 or @allow_sync_tran = 1)
    end -- if @property IN ('ins_cmd', 'del_cmd', 'upd_cmd' )

    /*
    ** Check to make sure that we have a valid type for status
    */
    IF @property = 'status'
    BEGIN
        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('not owner qualified', 'owner qualified')
        BEGIN
            RAISERROR (21023, 16, -1,@value)
            RETURN (1)
        END

        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'no column names', 'include column names', 'string literals', 'parameters',
            @dts_part, @no_dts_part )
        BEGIN
            RAISERROR (14097, 16, -1)
            RETURN (1)
        END

        IF  LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in (@dts_part,@no_dts_part)
        begin
            if @allow_dts = 0
            begin
                -- Invalid status for non dts pub
                raiserror(20592, 16, -1)
                RETURN (1)
            end
        end
        else
        begin
            if @allow_dts = 1
            begin
                -- Invalid status for dts pub
                raiserror(20591, 16, -1)
                RETURN (1)
            end
        end
    END

    IF @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )	
		AND @publisher_type != N'MSSQLSERVER'
    BEGIN
        /*
      ** Parameter Check:  @ins_cmd, @del_cmd, @upd_cmd
        ** Check to see that the cmd is valid
        */
        select @value = rtrim(ltrim(@value))
        if @property = 'ins_cmd' and @value is not null
			and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ')
        begin
            -- Invalid '@ins_cmd' specified. It must be NONE, SQL, CALL sp_MSins_article or CALL custom_stored_procedure_name.
            raiserror (15251, 16, 1, '@ins_cmd', 'NONE, SQL, CALL sp_MSins_article or CALL custom_stored_procedure_name')
            return 1
        end
        else if @property = 'del_cmd' and @value is not null
        	and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL')
        begin
            -- Invalid '@del_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or CALL custom_stored_procedure_name.
            raiserror (15251, 16, 1, '@del_cmd', 'NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or CALL custom_stored_procedure_name')
            return 1
        end
        else if @property = 'upd_cmd' and @value is not null
        	and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL', N'MCALL', N'SCALL')
        begin
            -- Invalid '@upd_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or MCALL sp_MSdel_article.
            raiserror (15251, 16, 1, '@upd_cmd', 'NONE, SQL, CALL sp_MSupd_article, XCALL sp_MSupd_article, MCALL sp_MSupd_article or SCALL sp_MSupd_article')
            return 1
        end
    END

    IF @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
    		AND @publisher_type = N'MSSQLSERVER'
    BEGIN
        /*
        ** Parameter Check:  @ins_cmd, @del_cmd, @upd_cmd
        ** Check to see that the cmd is valid
        */
        select @value = rtrim(ltrim(@value))
        if @property = 'ins_cmd' and @value is not null
			and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'SCALL')
        begin
            -- Invalid '@ins_cmd' specified. It must be NONE, SQL, CALL sp_MSins_article, SCALL sp_MSins_article or CALL custom_stored_procedure_name.
            raiserror (15251, 16, 1, '@ins_cmd', 'NONE, SQL, CALL sp_MSins_article, SCALL sp_MSins_article or CALL custom_stored_procedure_name')
            return 1
        end
        else if @property = 'del_cmd' and @value is not null
        	and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL', N'VCALL')
        begin
            -- Invalid '@del_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or CALL custom_stored_procedure_name.
            raiserror (15251, 16, 1, '@del_cmd', 'NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article, VCALL sp_MSdel_article or CALL custom_stored_procedure_name')
            return 1
        end
        else if @property = 'upd_cmd' and @value is not null
        	and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL', N'MCALL', N'VCALL', N'SCALL')
        begin
            -- Invalid '@upd_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or MCALL sp_MSdel_article.
            raiserror (15251, 16, 1, '@upd_cmd', 'NONE, SQL, CALL sp_MSupd_article, XCALL sp_MSupd_article, MCALL sp_MSupd_article, VCALL sp_MSupd_article or SCALL sp_MSupd_article')
            return 1
        end
        
        -- concurrent snapshot articles should use procs cuz we need to customize the compensating del/ins
        -- it should be OK to allow SQL/None for upd though
        
        if exists (select * from syspublications
                where pubid = @pubid and sync_method in (3, 4))
		BEGIN
			if len(@value) > @customprocmaxlen_minus_ccs_appendix
			begin
				RAISERROR( 21155, 16, -1, @article,  @customprocmaxlen_minus_ccs_appendix)
				return 1
			end
        END
        
        -- for VCALL - msrepl_tran_version column should exist and be of type uniqueidentifier
        
        if @property in ( 'del_cmd', 'upd_cmd' ) and (upper(left(@value,5)) = N'VCALL')
        begin
            declare @msrepl_tran_version_datatype sysname
            select @msrepl_tran_version_datatype = type_name(system_type_id)
            FROM sys.columns
            WHERE object_id = @objid AND name = 'msrepl_tran_version'
            if (@msrepl_tran_version_datatype != 'uniqueidentifier')
            begin
                raiserror(21567, 16, -1)
                return 1
            end
        end
        
        -- allow custom procedure to have spaces and special characters
        -- we will quote the custom procedure as follows:
        -- XXXX sp_MSxxx_custom_proc_name  or  XXXX x_GUID will become
        -- XXXX [dbo].[sp_MSxxx_custom_proc_name]  or XXXX [dbo].[x_GUID]
        -- if the custom procedure is already quoted in some form - user
        -- specified custom procedure name - then we will not change it
        
        if (@property = 'ins_cmd' and (upper(left(@value, 5)) in (N'CALL ', N'SCALL'))) or
                (@property = 'del_cmd' and (upper(left(@value, 5)) in (N'CALL ', N'XCALL', N'VCALL'))) or
                (@property = 'upd_cmd' and (upper(left(@value, 5)) in (N'CALL ', N'XCALL', N'MCALL', N'VCALL', N'SCALL')))
        begin
            declare @tmp_value nvarchar(386) = @value
            select @value = sys.fn_repltranquotecustomproc(@value)
            if @value = N''
            begin
              raiserror (22829, 16, -1, @tmp_value, N'dbo', 255)
              return 1
	     end
        end
    END -- IF @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )

    declare @need_new_snapshot bit
        ,@need_reinit_subscription bit

    select @need_new_snapshot = 0
             ,@need_reinit_subscription = 0

    if @property in ( N'dest_table', N'destination_owner' ,N'type',N'filter',
        N'pre_creation_cmd', N'schema_option') or
        (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status' and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in (@dts_part,
        @no_dts_part))
    begin
        select @need_new_snapshot = 1
                  ,@need_reinit_subscription = 1
    end
    else if @property in ( 'ins_cmd', 'del_cmd', 'upd_cmd' ) and
    		(@auto_gen = 0 )
    begin
    	 
        -- If autogeneration is not supported
        
        select @need_new_snapshot = 1
                  ,@need_reinit_subscription = 1
    end
    else if @property in ('sync_object', 'creation_script')
    begin
        select @need_new_snapshot = 1
    end

    -- Have to call this stored procedure to invalidate existing snapshot or reint
    -- subscriptions if needed
    EXECUTE @retcode  = sys.sp_MSreinit_article
						@publication				= @publication,
						@article					= @article,
						@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.
    */

    
    -- HREPL properties (can't use the transaction)
    
	IF @property IN ('tablespace')
	BEGIN
		EXEC @retcode = sys.sp_ORAChangeTableSpace	@publisher		= @publisher,
													@publication_id	= @pubid,
													@article_id		= @artid,
													@tablespace		= @value
		
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO UNDO

	END -- tablespace PROPERTY

    begin tran
	save TRAN sp_changearticle

	IF @property IN ( 'description', 'ins_cmd', 'del_cmd', 'upd_cmd', 'dest_table', 'creation_script', 'dest_object')
	BEGIN
		if @publisher_type = N'MSSQLSERVER' AND @property in ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
		begin
			declare @schema_start_lsn binary(10)
						,@schema_end_lsn binary(10)

			if (@property = 'ins_cmd' and upper(@value) = 'SQL' and OBJECTPROPERTY(@objid, 'tablehasidentity') = 1)
			begin
				
				-- special processing for ins_cmd when value = 'sql'
				-- set status bit 0x8 for colname specification in commands
				-- this enables proper identity NFR processing in Shiloh server
				
				update sysarticles set status = status | 8
				where artid = @artid and objid = @objid and (status & 8 != 8)
			end
			
			exec @retcode = sys.sp_replincrementlsn_internal @schema_start_lsn OUTPUT
			if @@ERROR<>0 or @retcode <> 0
				GOTO UNDO

    		exec @retcode = sys.sp_replpostcmd 0, @objid, @artid, 51, @objid
			if(@retcode <> 0) or (@@error <> 0)
				GOTO UNDO

			exec @retcode = sys.sp_replincrementlsn_internal @schema_end_lsn OUTPUT
			if @@ERROR<>0 or @retcode <> 0
				GOTO UNDO

			if (@schema_start_lsn is not null) and (@schema_end_lsn is not null)
			begin
				insert systranschemas (tabid, startlsn, endlsn, typeid) values (@objid, @schema_start_lsn, @schema_end_lsn, 51)
				if @@ERROR<>0
					GOTO UNDO
			end
		end
		
        /*
		** Check the validity of the destination object.  NULL should
		** get converted to the source object name.  Destination object
		** names can be owner qualified, but not database qualified.
		*/
		IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_table' OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_object'
		BEGIN
			IF @value IS NULL
				SELECT @value = object_name(objid)
				FROM sysarticles
				WHERE artid = @artid
					AND pubid = @pubid
		END
		SELECT @cmd1 = 'UPDATE sysarticles '
		IF @value IS NULL
		BEGIN
			SELECT @cmd1 = @cmd1 + '   SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + ' = NULL'
			SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
			SELECT @cmd2 = @cmd2 + '   AND pubid = ' + STR(@pubid)
			EXECUTE (@cmd1 + @cmd2)
		END
		ELSE
		BEGIN
			SELECT @cmd1 = @cmd1 + '   SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + ' = ' + sys.fn_replquotename(@value, N'''') collate SQL_Latin1_General_CP1_CS_AS
			SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
			SELECT @cmd2 = @cmd2 + '   AND pubid = ' + STR(@pubid)
			EXECUTE (@cmd1 + @cmd2)
		END

		IF @publisher_type = N'MSSQLSERVER'
			AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'upd_cmd'
		BEGIN
			IF ( 0 <> ( SELECT PATINDEX( '%[789].[0-9]%', @@version ) ) ) OR
				( 0 <> ( SELECT PATINDEX( '%[1-9][0-9].[0-9]%', @@version ) ) )
			BEGIN
				exec sys.sp_MSsetfilteredstatus @objid
			END
		END

		IF @@ERROR <> 0
			GOTO UNDO
		
		-- For command changes - post regenerated custom procedures
		-- if autogeneration is enabled and it is not SQL format
		
		if @property in ( 'ins_cmd', 'del_cmd', 'upd_cmd' ) and (@auto_gen > 0) and
			(upper(left(ltrim(@value), 3)) != N'SQL')
		begin
			declare @temp_id int

			select @temp_id = case @property 	when 'ins_cmd' then 1
												when 'del_cmd' then 2
												when 'upd_cmd' then 3
											end

			exec @retcode = sys.sp_MSpost_auto_proc @pubid = @pubid, @artid = @artid, @procmapid = @temp_id, @for_p2p_ddl = @for_p2p_ddl
			if (@retcode <> 0) or (@@error <> 0)
				GOTO UNDO

			
			-- For XCALL - set proper status for logreader
			
			exec @retcode = sys.sp_MSsetfilteredstatus @objid
			if(@retcode <> 0) or (@@error <> 0)
				GOTO UNDO
			
			-- Flush the article cache for logreader to pick the change
			
			exec @retcode = sys.sp_replflush
			if(@retcode <> 0) or (@@error <> 0)
				GOTO UNDO
		end
	END -- if property in ('description',....)

	IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_object'
	BEGIN
		/*
		** Check for a valid synchronization object.
		*/

		IF (@publisher_type != N'MSSQLSERVER')
		BEGIN
			RAISERROR (21632, 16, -1, '@sync_object', 'NULL')
			GOTO UNDO
		END
		
		IF @value IS NULL
		BEGIN
			RAISERROR (14043, 16, -1, '@value', 'sp_MSrepl_changearticle')
			GOTO UNDO
		END

		IF @value LIKE '%.%.%' OR @value LIKE '%.%'
		BEGIN
			select @object = PARSENAME( @value, 1 )
			select @owner = PARSENAME(  @value, 2 )
			select @db = PARSENAME(  @value, 3 )
			select @site = PARSENAME(  @value, 4 )

			if @object IS NULL
				GOTO UNDO
		END

		SELECT @sync_objid = OBJECT_ID(@value)
		IF @sync_objid IS NULL
		BEGIN
			RAISERROR (15001, 11, -1, @value)
			GOTO UNDO
		END

		IF NOT EXISTS
		(
			SELECT	*
			FROM	sys.objects
			WHERE	type IN ('U', 'V')
			AND		object_id = @sync_objid
		)
		BEGIN
			RAISERROR (14031, 16, -1)
			GOTO UNDO
		END

		/*
		** Update the article with the new synchronization object.
		*/

		UPDATE	sysarticles
		SET		sync_objid = @sync_objid
		WHERE	artid = @artid
		AND		pubid = @pubid

		IF @@ERROR <> 0
			GOTO UNDO
	END	-- sync object property

	IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'type'
	BEGIN
		/*
		** Check to make sure that we have a valid type.
		*/

		SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
	
		IF (@publisher_type = N'MSSQLSERVER')
		BEGIN
			IF @value NOT IN (
				'logbased',
				'logbased manualfilter',
				'logbased manualview',
				'logbased manualboth',
				'indexed view logbased',
				'indexed view logbased manualfilter',
				'indexed view logbased manualview',
				'indexed view logbased manualboth')
			BEGIN
				RAISERROR (14023, 16, -1)
				GOTO UNDO
			END
		END
		ELSE
		BEGIN
			IF @value != 'logbased'
			BEGIN
				RAISERROR(21601, 16, 1)
				RETURN (1)
			END
		END

		IF @objtype = 'V' AND @value NOT IN (
			'indexed view logbased',
			'indexed view logbased manualfilter',
			'indexed view logbased manualview',
			'indexed view logbased manualboth')
		BEGIN
			RAISERROR (18795, 16, -1)
			GOTO UNDO
		END
		ELSE IF @value NOT IN (
			'logbased',
			'logbased manualfilter',
			'logbased manualview',
			'logbased manualboth')
		BEGIN
			RAISERROR (18796, 16, -1)
			GOTO UNDO
		END

		/*
		** Determine the integer value for the type.
		*/
		IF @value IN ('logbased', 'indexed view logbased')
			SELECT @typeid = 1
		ELSE IF @value IN ('logbased manualfilter', 'indexed view logbased manualfilter')
			SELECT @typeid = 3
		ELSE IF @value IN ('logbased manualview', 'indexed view logbased manualview')
			SELECT @typeid = 5
		ELSE IF @value IN ('logbased manualboth', 'indexed view logbased manualboth')
			SELECT @typeid = 7

		/*
		** Update the article with the new type.
		*/

		UPDATE	sysarticles
		SET		type = @typeid
		WHERE	artid = @artid
		AND		pubid = @pubid

		IF @@ERROR <> 0
			GOTO UNDO
	END	-- type property

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'filter'
	BEGIN
        /*
        ** Check for a valid filter value.
        */

        IF NOT @publisher_type = N'MSSQLSERVER'
        BEGIN
            RAISERROR(21632, 16, -1, '@filter', 'NULL')
            GOTO UNDO
        END

		IF @value IS NOT NULL
		BEGIN

			IF @value LIKE '%.%.%' OR @value LIKE '%.%'
			BEGIN
				select @object = PARSENAME( @value, 1 )
				select @owner = PARSENAME(  @value, 2 )
				select @db = PARSENAME(  @value, 3 )
				select @site = PARSENAME(  @value, 4 )

				if @object IS NULL
				begin
        			raiserror (21344, 16, -1, '@value')
        			GOTO UNDO
                		end
			END
		END

		SELECT @filter = OBJECT_ID(@value)

		IF @value IS NOT NULL
		BEGIN
			IF @filter IS NULL
			BEGIN
				RAISERROR (15001, 11, -1, @value)
        			GOTO UNDO
			END

			IF NOT EXISTS
			(
				SELECT	*
				FROM	sys.objects
				WHERE	type = 'RF'
				AND		object_id = @filter
			)
			BEGIN
				RAISERROR (14049, 16, -1)
        			GOTO UNDO
			END
		END

		IF @value IS NULL SELECT @filter = 0

        
        -- save off the old filter
        

        SELECT @old_filter_name = object_name( filter )
        FROM sysarticles WHERE artid = @artid
        AND pubid = @pubid

        IF @@ERROR <> 0
		GOTO UNDO

        
        -- Update the article with the new filter.
        

        UPDATE sysarticles
            SET filter = @filter
            WHERE artid = @artid
            AND pubid = @pubid

        IF @@ERROR <> 0
		GOTO UNDO

        -- SQL SERVER > 7.x ONLY  disassociate old filter with table
        -- and associate new one

        IF ( 0 <> ( SELECT PATINDEX( '%[789].[0-9]%', @@version ) ) ) OR
            ( 0 <> ( SELECT PATINDEX( '%[1-9][0-9].[0-9]%', @@version ) ) )
        BEGIN

            
            -- disassociate table from old filter proc
            

            EXEC sys.sp_MSsetfilterparent @old_filter_name, 0

            IF @@ERROR <> 0
		GOTO UNDO

            
            -- set the parent of the filter proc to this object_id
            

            EXEC sys.sp_MSsetfilterparent @value, @objid

            IF @@ERROR <> 0
		GOTO UNDO
        END
	END	-- filter property

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pre_creation_cmd'
    BEGIN

        /*
        ** Check to make sure that we have a valid pre_creation_cmd.
        */
    IF @objtype = 'P' and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop')
        BEGIN
            RAISERROR ( 14111, 16, -1 )
            GOTO UNDO
        END

        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop', 'delete', 'truncate')
            BEGIN
                RAISERROR (14061, 16, -1)
                GOTO UNDO
            END

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

        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'none'
            SELECT @precmdid = 0
        ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
            SELECT @precmdid = 1
        ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'delete'
            SELECT @precmdid = 2
        ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'truncate'
            SELECT @precmdid = 3

        /*
        ** Update the article with the new pre_creation_cmd.
        */
        UPDATE sysarticles
            SET pre_creation_cmd = @precmdid
            WHERE artid = @artid
            AND pubid = @pubid

        IF @@ERROR <> 0
		GOTO UNDO

    END	-- pre_creation_cmd property

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status'
    BEGIN

        /*
        ** Determine the integer value for the type.
        */
		IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'none'
		BEGIN
			UPDATE	sysarticles
			SET		status = 0
			WHERE	artid = @artid
			  AND	pubid = @pubid
		END
		ELSE
		BEGIN
			IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'not owner qualified'
				UPDATE sysarticles
				SET status = status & ~4
				WHERE artid = @artid
				  AND pubid = @pubid

			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'owner qualified'
				UPDATE sysarticles
				SET status = status | 4
				WHERE artid = @artid
				  AND pubid = @pubid

			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'no column names'
				UPDATE sysarticles
				SET status = status & ~8
				WHERE artid = @artid
				  AND pubid = @pubid

			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'include column names'
				UPDATE sysarticles
				SET status = status | 8
				WHERE artid = @artid
				  AND pubid = @pubid

			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'string literals'
				UPDATE sysarticles
				SET status = status & ~16
				WHERE artid = @artid
				  AND pubid = @pubid

			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'parameters'
				UPDATE sysarticles
				SET status = status | 16
				WHERE artid = @artid
				  AND pubid = @pubid
			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = @dts_part
			begin
				IF EXISTS
				(
					SELECT	*
					FROM	sysarticles
					WHERE	artid = @artid
					  AND	status & 64 = 0
				)
				BEGIN
					UPDATE	sysarticles
					SET		status = status | 64,
							upd_cmd = N'XCALL sp_MSXpd_' + @article
					WHERE	artid = @artid
				END
			END
			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = @no_dts_part
			BEGIN
				IF EXISTS
				(
					SELECT	*
					FROM	sysarticles
					WHERE	artid = @artid
					  AND	status & 64 <> 0
				)
				BEGIN
					UPDATE	sysarticles
					SET		status = status & ~64,
							upd_cmd = N'CALL sp_MSupd_' + @article
					WHERE	artid = @artid
				END
			END
			
			IF @@ERROR <> 0
				GOTO UNDO
		END
    END	-- status property

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'schema_option'
    BEGIN
        IF @value IS NULL
        BEGIN
            RAISERROR(14146, 16,1)
            GOTO UNDO
        END

		IF sys.fn_isvalidhexstr(@value) = 0
			AND ISNUMERIC(@value) = 0
		BEGIN
			-- '@value' is not a valid value for the '@value' parameter. The value must be an integer.
			RAISERROR(21805, 16, -1, @value, '@value')
			GOTO UNDO
		END
		
        CREATE TABLE #tab_changearticle (value varbinary(8) NULL)

        IF @@ERROR <> 0
            GOTO UNDO

        EXEC ('insert #tab_changearticle values (' + @value +')' )

        IF @@ERROR <> 0
            GOTO UNDO

		-- Cache the schema option in int form for further checking
        declare @schema_option_hidword int

		declare	@schema_option_lodword int

        select	@schema_option_lodword = sys.fn_replgetbinary8lodword(fn_replprepadbinary8(value)),
                @schema_option_hidword = sys.fn_replgetbinary8hidword(fn_replprepadbinary8(value))
        from	#tab_changearticle

        DROP TABLE #tab_changearticle
	    DECLARE @valid_schema_options	int
		IF @publisher_type != N'MSSQLSERVER'
		BEGIN

			-- Restrict HREPL schema options
            SELECT @valid_schema_options = 0xD0D3
            IF (@schema_option_lodword & ~@valid_schema_options) <> 0 OR
                @schema_option_hidword <> 0
            BEGIN
                RAISERROR (21635, 16, -1)
                GOTO UNDO
            END
		END

        IF @objtype in ('P', 'PC')
        BEGIN
            SELECT @valid_schema_options = 0xFAC03021
            IF (@schema_option_lodword & ~@valid_schema_options) <> 0
            BEGIN
                RAISERROR ( 20014, 16, -1 )
                GOTO UNDO
            END
        END

        
        -- For updating subscriber publications
        -- DRI option has to be included
        
        if ((@allow_queued_tran = 1 or @allow_sync_tran = 1) and ((@schema_option_lodword & 0x80) = 0))
        BEGIN
            RAISERROR (21394, 16, 2)
            GOTO UNDO
        END

        if @publisher_type = N'MSSQLSERVER'
        begin
            
            -- Check schema option for timestamp
            
            if (@schema_option_lodword & 0x8 = 0)
            begin
                
                -- Timestamp scripting disabled
                -- For Queued this cannot be allowed
                
                if (@allow_queued_tran = 1)
                begin
                    raiserror (29003, 16, 1)
                    goto UNDO
                end

                
                -- Try to find out if there is a timestamp
                -- column that needs to be included in sysarticlecolumns
                -- now that it is replicated as binary(16)
                -- Note: There can only be one timestamp column per table.
                
                set @timestampcolumn = null

                select @timestampcolumn = columns.name
                  from sys.columns columns
            inner join dbo.sysarticles arts
                    on columns.object_id = arts.objid
                 where arts.artid = @artid
                   and (arts.status & 32 != 0)
                   and columns.system_type_id = 189

                
                -- status bit 32 should be reset as well
                
                update sysarticles set status = (status & ~32)
                where artid = @artid and (status & 32 != 0)
                if @@error != 0
                    goto UNDO

                if @timestampcolumn is not null
                begin
                    exec @retcode = sys.sp_articlecolumn
                        @publication = @publication ,
                        @article = @article,
                        @column = @timestampcolumn,
                        @operation = N'add',
                        @force_invalidate_snapshot = @force_invalidate_snapshot,
                        @force_reinit_subscription = @force_reinit_subscription
                    if @@error != 0 or @retcode != 0
                        goto UNDO
                end
            end
            else
            begin
                -- If the timestamp column is part of the primary key, we
                -- can't really replicate the timestamp column as a timestamp
                -- column as that will violate the equivalency of primary key
                -- values that transactional replication is based on. As such,
                -- we will implicitly turn off the KeepTimestamp(0x08) schema
                -- option if the primary key contains a timestamp column.
                
                if @repl_freq = 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 = @objid
                              and c.system_type_id = 189)
                begin
                    set @schema_option_lodword = @schema_option_lodword & ~8
                end
                else
                begin

                
                -- Try to find out if there is a timestamp
                -- column that is currently replicated as a binary column.
                -- If so, it should be removed from sysarticlecolumns
                -- once the 0x8 schema option is enabled
                
                select @timestampcolumn = columns.name
                  from sys.columns columns
            inner join dbo.sysarticlecolumns artcols
                    on columns.column_id = artcols.colid
            inner join dbo.sysarticles arts
                    on columns.object_id = arts.objid
                   and artcols.artid = arts.artid
                 where arts.artid = @artid
                   and (arts.status & 32 = 0)
                   and columns.system_type_id = 189

                
                -- Timestamp scripting enabled
                -- status bit 32 should be set as well
                
                update sysarticles set status = (status |32)
                where artid = @artid and (status & 32 = 0)
                if @@error != 0
                    goto UNDO
                if @timestampcolumn is not null
                begin
                       exec @retcode = sys.sp_articlecolumn
                            @publication = @publication ,
                            @article = @article,
                            @column = @timestampcolumn,
                            @operation = N'add',
                            @force_invalidate_snapshot = @force_invalidate_snapshot,
                            @force_reinit_subscription = @force_reinit_subscription
                        if @@error != 0 or @retcode != 0
                            goto UNDO
                end
            end
            end
			
			-- Check schema option for identity scripting
			
			if (@schema_option_lodword & 0x4 = 0)
			begin
				
				-- identity scripting disabled
				-- If the identity range management is AUTO
				-- then this cannot be allowed
				
				if exists (select * from sysarticleupdates where artid = @artid and identity_support = 1)
				begin
					raiserror (29003, 16, 2)
					goto UNDO
				end
				
				-- If the identity range management is MANUAL
				-- then this cannot be allowed for Queued and P2P
				
				if exists (select name from sys.columns where object_id = @objid and is_identity = 1
                            and ColumnProperty(object_id, name, 'IsIdNotForRepl') = 1 )
				begin
					if (@for_p2p_ddl = @OPT_ENABLED_FOR_P2P) or (@allow_queued_tran = 1)
					begin
						raiserror (29003, 16, 3)
						goto UNDO
					end
				end
			end
			else
			begin
				
				-- identity scripting enabled
				-- Need to mark the identity column as NFR
				
				select @colname = name
				from sys.columns
				where object_id = @objid and
					is_identity = 1 and -- is identity
					ColumnProperty(object_id, name, 'IsIdNotForRepl') = 0
				if @colname is not null
				begin
					-- Mark 'not for repl'
					EXEC %%ColumnEx(ObjectID = @objid, Name = @colname).SetIdentityNotForRepl(Value = 1)
					IF @@ERROR <> 0
						GOTO UNDO
				end
			end
		end
		
        -- Seems to be a good place to check and see if using
        -- collation 0x00001000 or extended property 0x00002000
        if
        (
            (@schema_option_lodword & 0x000001000 <> 0) or
            (@schema_option_lodword & 0x000002000 <> 0)
        )
        BEGIN
            select @backward_comp_level = 40
        END

        declare @schema_option bigint
        set @schema_option = convert(bigint,sys.fn_replcombinehilodwordintobinary8(@schema_option_hidword, @schema_option_lodword))
        --once we are here, article partition has been updated up to current point, now check on UDT dependency
		if	((@schema_option & 0x000000B000000020) <> 0) and
		exists (select * from sys.columns where object_id = @objid and system_type_id = 240)
		begin
			if (sys.fn_MSrepl_dependUDT(@objid, @schema_option, @artid) = 1 )
			begin
			    raiserror (21839, 16, 1, @article)
			    GOTO UNDO
			end
		end
        UPDATE sysarticles
        SET schema_option = convert(binary(8),@schema_option)
        WHERE artid = @artid
          AND pubid = @pubid


        IF @@ERROR <> 0
            GOTO UNDO
    END -- schema option property

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'destination_owner'
    BEGIN
        IF LEN(@value) = 0
BEGIN
            SET @value = NULL
        END

        IF @value IS NOT NULL
        BEGIN
            EXECUTE @retcode = sys.sp_validname @value

            IF @retcode <> 0
                GOTO UNDO
        END

        UPDATE sysarticles
        SET dest_owner = @value
        FROM sysarticles
        WHERE artid = @artid
          AND pubid = @pubid

        IF @@ERROR <> 0
            GOTO UNDO
    END


    if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pub_identity_range'
    begin
        if not exists (select * from sysarticleupdates where artid = @artid and
            identity_support = 1)
        begin
            raiserror(21235, 16, -1, @property)
            goto UNDO
        end

        declare @pub_range bigint
        select @pub_range = convert(bigint, @value)
        if @pub_range < 0
        begin
            raiserror(21232, 16, -1)
            goto UNDO
        end

        if exists (select * from MSpub_identity_range where objid = @objid and
            pub_range < 0)
            select @pub_range = @pub_range * -1

        update MSpub_identity_range set
            pub_range = @pub_range
            where objid=@objid
        if @@error < 0
            goto UNDO
    end

    if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'identity_range'
    begin
        if not exists (select * from sysarticleupdates where artid = @artid and
            identity_support = 1)
        begin
            raiserror(21235, 16, -1, @property)
            goto UNDO
        end

        declare @range bigint
        select @range = convert(bigint, @value)
        if @range < 0
        begin
            raiserror(21232, 16, -1)
            goto UNDO
        end

        if exists (select * from MSpub_identity_range where objid = @objid and
            range < 0)
            select @range = @range * -1

        update MSpub_identity_range set
            range = @range
            where objid=@objid
        if @@error < 0
            goto UNDO

        -- Distributor side data will be changed later by sp_MSchange_article.
    end

    -- Check to see if the range is too big.
    -- Must be down after the change. If the check fails, the transaction
    -- will be rolled back.
    if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('pub_identity_range', 'identity_range')
    begin
        declare @pub_identity_range bigint, @identity_range int
        select @pub_identity_range = pub_range,
            @identity_range = range from MSpub_identity_range where
            objid = @objid

        declare @xtype int, @xprec int, @max_range bigint
        select @xtype=system_type_id, @xprec=precision from sys.columns where object_id=@objid and
            is_identity=1
        select @max_range =
                case @xtype when 52 then power((convert(bigint,2)), 8*2-1) - 1 --smallint
                    when 48 then power((convert(bigint,2)), 8-1) - 1         --tinyint
                    when 56 then power((convert(bigint,2)), 8*4-1) - 1       --int
                    when 127 then power((convert(bigint,2)), 62) - 1 + power((convert(bigint,2)), 62)   --bigint
                    when 108 then power((convert(bigint,10)), @xprec)    --numeric
                    when 106 then power((convert(bigint,10)), @xprec)    --decimal
                else
                    power((convert(bigint,2)), 62) + power((convert(bigint,2)), 62) - 1  -- defaulted to bigint
                end

        declare @source_table nvarchar (517)
        exec @retcode = sys.sp_MSget_qualified_name @objid, @source_table output
        if @retcode <> 0 or @@error <> 0
            goto  UNDO
        if @pub_identity_range * 2 + @identity_range > (@max_range - ISNULL(IDENT_CURRENT(@source_table), IDENT_SEED(@source_table)))
begin
                raiserror(21290, 16, -1)
                goto  UNDO
            end
    end

    if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'threshold'
    begin
        if not exists (select * from sysarticleupdates where artid = @artid and
            identity_support = 1)
        begin
            raiserror(21235, 16, -1, @property)
            goto  UNDO
        end

        declare @threshold bigint
        select @threshold = convert(int, @value)
        if @threshold < 1 or @threshold > 100
        begin
            raiserror(21233, 16, -1)
            goto  UNDO
        end

        update MSpub_identity_range set
            threshold = @threshold
            where objid=@objid
        if @@error < 0
            goto  UNDO
        -- Distributor side data will be changed later by sp_MSchange_article.
    end

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'fire_triggers_on_snapshot'
    begin
        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

        if @value = 'true'
            select @fire_triggers_on_snapshot = 1
        else
            select @fire_triggers_on_snapshot = 0

        update dbo.sysarticles
           set fire_triggers_on_snapshot = @fire_triggers_on_snapshot
         where artid = @artid
           and pubid = @pubid
        if @@error <> 0
            goto UNDO

    end

    
    -- some info on articles is also stored at the distributor.
    -- update info at distributor if these properties change
    

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ( N'description', N'dest_table', N'dest_object',
    		'identity_range', 'threshold', 'destination_owner', 'dest_owner' )
    BEGIN
        /*
        ** 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
            RAISERROR (14071, 16, -1)
            GOTO UNDO
        END

        SELECT @dbname =  DB_NAME()

        SELECT	@distproc =	QUOTENAME(RTRIM(@distributor)) + '.' +
        					QUOTENAME(@distribdb) + '.dbo.sp_MSchange_article'

        EXECUTE	@retcode =	@distproc	@publisher		= @srvname,
										@publisher_db	= @dbname,
										@publication	= @publication,
										@article		= @article,
										@article_id		= @artid,
										@property		= @property,
										@value			= @value


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

    -- Have to call this stored procedure to invalidate existing snapshot or reint
    -- subscriptions if needed
    EXECUTE @retcode  = sys.sp_MSreinit_article
						@publication				= @publication,
						@article					= @article,
						@need_new_snapshot			= @need_new_snapshot,
						@need_reinit_subscription	= @need_reinit_subscription,
						@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

    if @backward_comp_level > 10
        update syspublications set backward_comp_level = @backward_comp_level where pubid = @pubid

    IF @increment_min_autonosync_lsn = 1
    BEGIN
        EXEC @retcode = sys.sp_MSincrementpublicationminautonosynclsn
                @publication = @publication
        IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
    END

   	-- If the change impacts how the heterogeneous log reader processes change commands
	-- call sp_IHreplflush to force a reload of the log reader's article cache.
	-- This update can be performed within the transaction.
	IF @publisher_type != N'MSSQLSERVER'
		AND @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd', 'dest_table', 'destination_owner', 'status', 'dest_object')
	BEGIN
        EXEC @retcode = sys.sp_IHreplflush @publisher
        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
		 	GOTO UNDO
        END
	END

    COMMIT TRAN

    /*
    ** Force the article cache to be refreshed with the new definition.
    */
    EXECUTE sys.sp_replflush

    /*
    ** Return succeed.
    */

    RAISERROR (14025, 10, -1)
    RETURN (0)

UNDO:
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN sp_changearticle
        COMMIT TRAN
    end
    return(1)
END

 
Last revision 2008RTM
See also

  sp_changearticle (Procedure)
sp_changearticlecolumndatatype (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_changearticlecolumndatatype (Procedure)
sp_MSrepl_changepublication (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