Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmerge_altertable

  No additional text.


Syntax

create  procedure sys.sp_MSmerge_altertable
(
    @qual_object_name nvarchar(512) --qualified 3-part-name
    ,@objid int
    ,@pass_through_scripts nvarchar(max)
)
AS
    set nocount on
    declare @sync_objid     int

    declare @guidstr nchar(32)
    declare @instrigname        sysname
    declare @updtrigname        sysname
    declare @deltrigname        sysname

    declare @pubname sysname
    declare @cmd nvarchar(2000)
    declare @is_publisher bit
    declare @artname sysname
    declare @sync_mode int
    declare @conflict_logging int
    declare @schemaguid    uniqueidentifier
    declare @schematype    int
    declare @schemaversion int
    declare @schematext    nvarchar(max)
    declare @schemasubtype int

    declare @schemaguid_bitmask    uniqueidentifier
    declare @schematype_bitmask    int
    declare @schemaversion_bitmask int
    declare @schematext_bitmask    nvarchar(2000)
    declare @schemasubtype_bitmask int

    declare @colid int
    declare @statement_id int
    declare @max_colid int
    declare @retcode int
    declare @in_partition int
    declare @columnsbm varbinary(128)
    declare @columnsbm_after_drop varbinary(128)
    declare @excluded_cols varbinary(128)
    declare @excluded_col_count int
    declare @missing_cols varbinary(128)
    declare @is_republisher bit
    declare @tablename sysname
    declare @artid    uniqueidentifier
    declare @pubid uniqueidentifier
    declare @column sysname
    declare @snapshot_ready int
    declare @subtype sysname
    declare @replicate_ddl    int
    declare @publisher sysname
    declare @publisher_db sysname
    declare @islightweight bit
    declare @ready tinyint, @snapshotobsolete tinyint
    declare @notready tinyint
    declare @active tinyint
    declare @cCols int -- number of columns in the table
    declare @cMissing int -- number of missing cols
    declare @BinVarStr nvarchar(128)
    declare @num_columns int
    declare @NeedSchemaChange int
    declare @missingORexcluded varbinary(128)
    declare @missingORexcludedStr varchar(1000)
    declare @column_id int
    declare @abs_column_id int
    declare @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP int
    declare @SCHEMA_TYPE_DDL_ACTIONS int
    declare @artnick int
    declare @status int
    declare @qual_check_object sysname


    declare @dest_table sysname
            ,@dest_owner sysname
            ,@qual_dest_object nvarchar(512)
            ,@temp_ddlcmds nvarchar(max)
            ,@debug_print bit

   declare @should_fail bit
            ,@is_not_for_replication bit
            ,@delete_referential_action tinyint
            ,@update_referential_action tinyint
            ,@colname nvarchar(512)

    declare @schema_option binary(8)
    declare @vertical_partition int
    declare @schematype_existing int
    declare @schemasubtype_existing int

    declare @got_merge_admin_applock bit

    select @got_merge_admin_applock = 0

    select @ready= 1
    select @snapshotobsolete=2
    select @notready= 0
    select @active= 1
    set @subtype=NULL
    set @schemasubtype = 0
    set @schemaversion = NULL
    set @vertical_partition = 0

    set @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP = 301
    set @SCHEMA_TYPE_DDL_ACTIONS = 300

    --enum of constants
    declare  @schemasubtype_addcolumn int
            ,@schemasubtype_dropcolumn int
            ,@schemasubtype_altercolumn int
            ,@schemasubtype_disabletrigger int
            ,@schemasubtype_enabletrigger int
            ,@schemasubtype_disabletriggerall int
            ,@schemasubtype_enabletriggerall int
            ,@schemasubtype_dropconstraint int
            ,@schemasubtype_enableconstraint int
            ,@schemasubtype_disableconstraint int
            ,@schemasubtype_enableconstraintall int
            ,@schemasubtype_disableconstraintall int
          ,@schemasubtype_addfk int
            ,@schemasubtype_addun int
            ,@schemasubtype_addchk int
            ,@schemasubtype_adddf int
            ,@schemasubtype_addpk int

    select @schemasubtype_addcolumn = 1 					    --N'ADDCOLUMN'
        ,@schemasubtype_dropcolumn =  2 					    --N'DROPCOLUMN'
        ,@schemasubtype_altercolumn = 3 					    --N'ALTERCOLUMN'
        ,@schemasubtype_addpk = 4							    --N'ADDPRIMARYKEY'
        ,@schemasubtype_addun = 5							    --N'ADDUNIQUE'
        ,@schemasubtype_addfk = 6				        --N'ADDREFERENCE'
        ,@schemasubtype_dropconstraint = 7  			        --N'DROPCONSTRAINT'
        ,@schemasubtype_adddf = 8       						--N'ADDDEFAULT'
        ,@schemasubtype_addchk = 9  							--N'ADDCHECK'
        ,@schemasubtype_disabletrigger = 10 				    --N'DISABLETRIGGER'
        ,@schemasubtype_enabletrigger = 11		    		    --N'ENABLETRIGGER'
        ,@schemasubtype_disabletriggerall = 12		            --N'DISABLETRIGGER'
        ,@schemasubtype_enabletriggerall = 13       	        --N'ENABLETRIGGER'
        ,@schemasubtype_enableconstraint = 14   		        --N'ENABLECONSTRAINT'
        ,@schemasubtype_disableconstraint = 15  		        --N'DISABLECONSTRAINT'
        ,@schemasubtype_enableconstraintall = 16    	        --N'ENABLECONSTRAINT'
        ,@schemasubtype_disableconstraintall = 17   	        --N'DISABLECONSTRAINT'


    if object_id('MSrepl_debug_DDL') is not null
        set @debug_print = 1
    else
        set @debug_print = 0


    --sp_repladd/dropcolumn sets session context info as such so we
    --know if we came from there, nothing need to be done
    --in that case since sp_repladd/dropcolumn already handle all the
    --logic similiar to here.
    declare @bypasswholeddleventbit bit
    exec @retcode = sys.sp_MScheckcontext_bypasswholeddleventbit @bypasswholeddleventbit OUTPUT
    if @retcode <>0 or @@ERROR<>0
        return 1
    -- we should by pass
    if @bypasswholeddleventbit=1
        return 0

    declare @bypassreplicatedll bit
    exec @retcode = sys.sp_MScheckcontext_bypassreplicateddleventbit @bypassreplicatedll OUTPUT
    if @retcode <>0 or @@ERROR<>0
        return 1

    if is_member('db_owner') <> 1
    begin
        raiserror (21050, 16, -1)
        return (1)
    end

    
    -- prepare parsing event
    create table #merge_altertable(
        id int identity,
        DDLsubtype sysname,
        TableOwner sysname,
        TableName sysname,
        ColumnName sysname NULL,
        ColumnAttr sysname NULL,
        schemasubtype int NULL
    )
    select @tablename=object_name(@objid)

    set @temp_ddlcmds = N'ALTER table '
                                    + @qual_object_name + N' '
                                    + @pass_through_scripts

    insert #merge_altertable(DDLsubtype, TableOwner, TableName,
        ColumnName, ColumnAttr)
        exec sys.sp_replddlparser @ddlCmd=@temp_ddlcmds

    if @@ERROR <> 0
    begin
        goto FAILURE
    end

    --set the numeric field to represent subtype, hopefully this makes future comparasion faster
    update #merge_altertable set schemasubtype = case DDLsubtype when N'ADDCOLUMN' then @schemasubtype_addcolumn
                                                            when N'DROPCOLUMN' then @schemasubtype_dropcolumn
                                                            when N'ALTERCOLUMN' then @schemasubtype_altercolumn
                                                            when N'ADDREFERENCE' then @schemasubtype_addfk
                                                            when N'ADDUNIQUE' then @schemasubtype_addun
                                                            when N'ADDCHECK' then @schemasubtype_addchk
                                                            when N'ADDDEFAULT' then @schemasubtype_adddf
                                                            when N'ADDPRIMARYKEY' then @schemasubtype_addpk
                                                            when N'DISABLETRIGGER' then
                                                                case len(isnull(ColumnName, '') )
                                                                    when 0 then @schemasubtype_disabletriggerall
                                                                    else @schemasubtype_disabletrigger
                                                                end
                                                            when N'ENABLETRIGGER' then
                                                                case len(isnull(ColumnName, '') )
                                                                    when 0 then @schemasubtype_enabletriggerall
                                                                    else @schemasubtype_enabletrigger
                                                                end
                                                            when N'DROPCONSTRAINT' then @schemasubtype_dropconstraint
                                                            when N'ENABLECONSTRAINT' then
                                                                case len(isnull(ColumnName, '') )
                                                                    when 0 then @schemasubtype_enableconstraintall
                                                                    else @schemasubtype_enableconstraint
                                                                end
                                                            when N'DISABLECONSTRAINT' then
                                                                case len(isnull(ColumnName, '') )
                                                                    when 0 then @schemasubtype_disableconstraintall
                                                                    else @schemasubtype_disableconstraint
                                                                end
                                                    end

    if (@@ERROR <> 0)
    begin
        return 1
    end

    
    -- consistency check

    -- Check whether the table is a heavy- or lightweight article.
    -- If it is neither, return success.
    select top 1 @islightweight= lightweight, @artnick= nickname
        from dbo.sysmergearticles where objid=@objid

    if @islightweight is null
    begin
        return 0
    end

    -- If the article is about to be cleaned up, a replication-generated rowguidcol
    -- is removed. In that case, we don't need to do anything here.
    if not exists (select * from dbo.sysmergearticles where objid=@objid and status<>7)
    begin
        return 0
    end

    declare @is_context_merge_identityrangeddl bit
              declare @at_publisher    bit
              declare @is_mergeagent    bit
    declare @constraintname sysname

    exec @retcode = sys.sp_MScheckcontext_merge_identityrangeddlbit  @is_context_merge_identityrangeddl OUTPUT
    if @retcode <>0 or @@ERROR<>0
        return 1


    exec @retcode = sys.sp_MSisreplmergeagent @is_mergeagent output, @at_publisher output
    if @@error <> 0 or @retcode <> 0
        return 1


    --if user does alter table drop constraint for identity range - then disallow this
    --the user must explictly call sp_changemergearticle @publication, @article, 'identityrangemanagementoption', 'none'
    select top 1 @artid=artid from dbo.sysmergearticles where objid=@objid
    select @constraintname = 'repl_identity_range_%' + convert(nvarchar(36), @artid)
    select @constraintname = REPLACE(@constraintname, '-', '_')
    select @num_columns = count(*) from #merge_altertable ma
            where ma.DDLsubtype in (N'DROPCONSTRAINT') and
            lower(ma.ColumnName) like lower(@constraintname)
    if (@num_columns = 1 and OBJECTPROPERTY(@objid, 'tablehasidentity') = 1)
    begin
        -- this is not identity range related dll operation or a merge agent propagating ddl changes, invalid user action - raise error
        if @is_context_merge_identityrangeddl=0 and @is_mergeagent=0
        begin
            RAISERROR (25010, 16, -1, @constraintname, @artname)
            return 1
        end
        -- else if the bit is not set, propagate the drop constraint as a DDL change.
        if @is_context_merge_identityrangeddl=1
            return 0
    end

    --if sp_MSrefresh_idrange_check_constraint is calling alter table to add/drop the constraint
    --repl_identity_range_[artid] do not propagate this DDL change and hence return.
    select @num_columns = count(*) from #merge_altertable ma
            where ma.DDLsubtype in (N'ADDCHECK') and
            lower(ma.ColumnName) like lower(@constraintname)
    if (@num_columns = 1 and OBJECTPROPERTY(@objid, 'tablehasidentity') = 1)
    begin
        return 0
    end

    -- special case for change tracking ddl
    --   We're only capturing the ALTER TABLE for change tracking, and if the subscriber has not manually
    --   enabled it via alter database, the application will fail here.  Since this is likely the normal case
    --   we'll skip the replication of this particular DDL statement.
    if exists (select * from #merge_altertable ma
            where ma.DDLsubtype in (N'ENABLECHANGETRACK', N'DISABLECHANGETRACK'))
    begin
        return 0
    end

    -- Don't want to replicate alter table rebuild statements
    -- so skip if the DDLsubtype is REBUILDTABLE
    if exists (select * from #merge_altertable
            where DDLsubtype = N'REBUILDTABLE')
    begin
        return 0
    end

    --open a local transaction now since this is where we start to make modification to metadata
    begin tran
    save transaction sp_MSmerge_altertable


    -- obtain the snapshot/DDL/admin proc applock to avoid having contention with snapshot
    -- Attempt to get the lock with no wait
    exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
                                                  @lockowner = N'Transaction'
    if @retcode<>0 or @@error<>0
    begin
        raiserror(21386, 16, -1, @qual_object_name)
        goto FAILURE
    end

    select @got_merge_admin_applock = 1

    -- don't do column index shifting for MSmerge_contents.colv1 if there is any dropped column
    if exists (select * from #merge_altertable where DDLsubtype in ('DROPCOLUMN'))
    begin
        -- Find new and old missing_cols for lightweight.
        -- If needed, adjust MSmerge_rowtrack and sysmergearticles.
        if 1=@islightweight
        begin
            delete from dbo.MSmerge_filteringcolumns_lightweight
            where tablenick in (select nickname from dbo.sysmergearticles where objid = @objid)
                  and column_id not in (select column_id from sys.columns where object_id = @objid)
        end    --1=@islightweight
    end

    --add-column, drop-column and alter-column can be performed only from original publisher of the table.
    --except from merge agent, which propagate ALTER TABLE statement to subscribers
    -- we cannot check DROPCONSTRAINT here as after trigger cannot distinguish general vs NOT FOR REPLICATION constraint
    if exists (select * from #merge_altertable where DDLsubtype in ('DROPCOLUMN','ALTERCOLUMN','ADDCOLUMN')) and
              sessionproperty('replication_agent') <> 1
    begin
        if 0=@islightweight
        begin
            -- this article must be inside a publisher (original or republishing) - maybe redundant but good for consistency check
            if NOT exists (select * from dbo.sysmergearticles
                            where objid=@objid and
                                  pubid in
                                    (select pubid from dbo.sysmergepublications
                                        where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
                                              publisher_db=db_name()))
            begin
                raiserror(21531, 16, -1)
                goto FAILURE
            end
            -- the article can not be inside a republisher
            else if exists (select * from dbo.sysmergearticles
                            where objid=@objid and
                                  pubid not in
                                    (select pubid from dbo.sysmergepublications
                                        where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
                                              publisher_db=db_name()))
            begin
                raiserror(21531, 16, -1)
                goto FAILURE
            end
        end
        else
        begin
            raiserror(21531, 16, -1)
            goto FAILURE
        end
    end

    -- don't do anything for adding a ROWGUIDCOL column, this won't happen for table with active merge subscription
    -- anyway since it should already have one.
    if exists (select * from #merge_altertable where DDLsubtype = N'ADDCOLUMN' and ColumnAttr = N'ROWGUIDCOL')
        goto SUCCESS

    -- check on column drops
    -- DDLsubtype = N'DROPCONSTRAINT'
    if exists (select * from #merge_altertable where DDLsubtype in ('DROPCOLUMN', 'ALTERCOLUMN', 'DISABLEROWGUIDCOL'))
        and sessionproperty('replication_agent') <> 1
    begin
        -- if object is published
        if exists (select * from dbo.sysmergearticles
                            where objid=@objid
                                  and pubid in
                                    (select pubid from dbo.sysmergepublications
                                        where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
                                              publisher_db=db_name()))
        begin
            -- set as msg 21546
            -- Attempting to replicate ALTER TABLE command with multiple drops including a column which does not exist in at least one of the subscription, use single drop column in ALTER TABLE instead.
            -- if mutliple column, we require one by one drop to avoid one in partition and the other not, then subscriber
            -- won't be able to drop the column not in partition

            --trigger fires after the DDL event, in case of drop, the corresponding colid is no longer
            --in sys.columns once we are here. So we look at the colids in sysmergearticles.columns but not in sys.columns
            --to figure out how many columns were dropped in this DDL.
            create table #table_mrgartcol(artid uniqueidentifier NOT NULL, colid smallint NOT NULL)
            exec sys.sp_MSmerge_gen_articlecolumnrowset @objid=@objid

            declare @num_columns2 int
            select @num_columns2 = count(*) from #merge_altertable where DDLsubtype = N'DROPCOLUMN'
            if     @num_columns2 > 1 and
                exists(
                        select count(colid) from #table_mrgartcol
                        where colid not in
                        (
                            select column_id from sys.columns where object_id = @objid
                        )
                        group by artid
                        having count(colid) < @num_columns2 and count(colid) <> 0
                    )
begin
                raiserror(21546, 16, 1)
                goto FAILURE
            end

            -- This check is to disallow dropping filter columns.
            declare @failflag bit
            set @failflag=0

            if exists (select * from dbo.sysmergearticles art, #merge_altertable alttable
                where art.objid = @objid and alttable.DDLsubtype ='DROPCOLUMN'
                and sys.fn_MSisfilteredcolumn(art.subset_filterclause, alttable.ColumnName, @objid)=1)
                set @failflag=1

            if exists (select * from dbo.sysmergesubsetfilters filter, dbo.sysmergearticles art, #merge_altertable alttable
                where filter.art_nickname = art.nickname and art.objid = @objid and alttable.DDLsubtype ='DROPCOLUMN'
                and sys.fn_MSisfilteredcolumn(filter.join_filterclause, alttable.ColumnName, @objid)=1)
                set @failflag=1

            if exists (select * from dbo.sysmergesubsetfilters filter, dbo.sysmergearticles art, #merge_altertable alttable
                where filter.join_nickname = art.nickname and art.objid = @objid and alttable.DDLsubtype ='DROPCOLUMN'
                and sys.fn_MSisfilteredcolumn(filter.join_filterclause, alttable.ColumnName, @objid)=1)
                set @failflag=1

            if @failflag=1
            begin
                raiserror(21552, 16, 1)
                goto FAILURE
            end
        end

        -- if object is published and snapshot being run
        if exists (select * from dbo.sysmergearticles
                            where objid=@objid and status=2 -- rowguid is on only when snapshot is run, see 'active', 'unsynced' in sp_MSchangemergeschemaarticle
                                  and pubid in
                                    (select pubid from dbo.sysmergepublications
                                        where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
                                              publisher_db=db_name()))
        begin
            -- if rowguidcol is dropped
            if objectproperty(@objid, 'TableHasRowGuidCol')=0
            begin
                raiserror(21540, 16, -1)
                goto FAILURE
            end
        end
    end

    -- This is to bail out for merge added default constraint by sp_addmergearticle for incremental article
    -- ALTER TABLE [dbo].[t1]
    -- add constraint default_constraint_for_rowguidcol_of_1221579390 default newid() for [c3]
    
    select @num_columns = count(*) from #merge_altertable ma
            where ma.DDLsubtype in (N'ADDDEFAULT') and
            (ma.ColumnName LIKE N'MSmerge_default_constraint_for_rowguidcol_of_%'
              or ma.ColumnName LIKE N'MSmerge_df_rowguid_%'
            )
    if (@num_columns = 1)
    begin
            goto SUCCESS
    end

    -- Disallow explicit drop rowguid constraint - see bug 659934
    select @num_columns = count(*) from #merge_altertable ma
            where    ma.DDLsubtype in (N'DROPCONSTRAINT') and
            (ma.ColumnName LIKE N'MSmerge_default_constraint_for_rowguidcol_of_%'
              or ma.ColumnName LIKE N'MSmerge_df_rowguid_%'
            )
    if (@num_columns = 1) and sessionproperty('replication_agent') <> 1
    begin
        raiserror(25005, 16, -1)
        goto FAILURE
    end

    --disallow addingconstraints/defaults without name: server auto generated name can be different on
    --publisher and subscriber - relaxing this restriction
    --if exists( select * from #merge_altertable ma
    --        where ma.DDLsubtype  in (N'ADDPRIMARYKEY', N'ADDUNIQUE', N'ADDREFERENCE', N'ADDCHECK', N'ADDDEFAULT')
    --                and (ma.ColumnName is NULL or len(ma.ColumnName) = 0))
    --begin
    --    RAISERROR (21731, 16, -1)
    --    goto FAILURE
    --end

    --if table has no active subscription and DDL does not add/drop column,  don't do anything
    declare @subscription_active int
    set @subscription_active=1

    if not exists (SELECT A.artid
                        FROM dbo.sysmergearticles A,
                        dbo.sysmergepublications P,
                        dbo.sysmergesubscriptions S
                        WHERE A.pubid = P.pubid and P.pubid = S.pubid
                        and A.objid = @objid
                        and S.status = @subscription_active  -- active
                    )
        and not exists (select * from #merge_altertable where DDLsubtype in( N'ADDCOLUMN', N'DROPCOLUMN'))
    begin
            goto SUCCESS
    end

    /*
    --fk is cascade delete/update without not for replication clause, error out
    -- see bug 551947
    select @num_columns = count(*) from #merge_altertable where DDLsubtype = N'ADDREFERENCE'
    declare #fk_name CURSOR LOCAL FAST_FORWARD for
            select name, is_not_for_replication,  delete_referential_action, update_referential_action
            from sys.foreign_keys
            where     parent_object_id = @objid
            order by create_date desc

    open #fk_name
    fetch #fk_name into @colname, @is_not_for_replication,  @delete_referential_action, @update_referential_action
    while (@@fetch_status <> -1 and @num_columns > 0)
    begin
        if @is_not_for_replication = 0
            and (@delete_referential_action = 1 or @update_referential_action = 1)
        begin
            raiserror(21724, 10, 1, @colname, @qual_object_name)
            select @should_fail = 1
        end
        fetch #fk_name into @colname, @is_not_for_replication,  @delete_referential_action, @update_referential_action
        select @num_columns = @num_columns - 1
    end
    close #fk_name
    deallocate #fk_name

    if @should_fail = 1
    begin
        goto FAILURE
    end
*/

    --adding constraints without name, switch to single cmd mode
    --now that we can query catelog view for constraints name, update the parser resultset so later join on name will work
    if exists( select * from #merge_altertable ma
            where ((ma.schemasubtype = @schemasubtype_addun) OR (ma.schemasubtype = @schemasubtype_addfk) OR (ma.schemasubtype = @schemasubtype_addchk))
                    and len(isnull(ma.ColumnName, '') )= 0)
    begin

        exec @retcode = sys.sp_MSmergefixup_constraints_name @schemasubtype_addun = @schemasubtype_addun    --N'ADDUNIQUE'
                                                            ,@schemasubtype_addfk = @schemasubtype_addfk    --N'ADDREFERENCE'
                                                            ,@schemasubtype_addchk = @schemasubtype_addchk	--N'ADDCHECK'
                                                            ,@objid = @objid
                                                            ,@debug_print = @debug_print
        if @retcode <>0 or @@ERROR<>0
                goto FAILURE
    end

    --adding default without name, switch to single cmd mode
    --now that we can query catelog view for constraints name, update the parser resultset so later join on name will work
    if exists( select * from #merge_altertable ma
            where (ma.schemasubtype  = @schemasubtype_adddf)
                    and (len(isnull(ma.ColumnName, '') )= 0 or
                            ColumnName collate database_default in
                            (select name from sys.columns where object_id = @objid)))
    begin
        exec @retcode = sys.sp_MSmergefixup_defaults_name @schemasubtype_adddf = @schemasubtype_adddf	--N'ADDDEFAULT'
                                                            ,@schemasubtype_addcolumn = @schemasubtype_addcolumn	--N'ADDCOLUMN'
                                                            ,@objid = @objid
                                        ,@debug_print = @debug_print
        if @retcode <>0 or @@ERROR<>0
                goto FAILURE
    end

    
    -- iterate through publication and article pair

    -- if the current table is part of a publication with less than 90 compat level we will not allow the alter table
    if exists (select 1 from dbo.sysmergepublications where backward_comp_level<90 and pubid in
                (select pubid from dbo.sysmergearticles where objid = @objid))
    begin
        if exists (select * from #merge_altertable where DDLsubtype in( N'ADDCOLUMN', N'DROPCOLUMN', N'ALTERCOLUMN') )
        begin
            raiserror(25013, 16, -1)
            goto FAILURE
        end

         --Merge replication does not support ALTER COLUMN ADD SPARSE for merge published columns
         if	exists (select * from sys.columns col join #merge_altertable ma on col.name = ma.ColumnName collate database_default
                			where col.object_id = @objid
                			        and col.is_merge_published = 1
                				 and ma.DDLsubtype = N'ENABLESPARSE')
         begin
            select @artname= name  from dbo.sysmergearticles where objid=@objid
         	raiserror(21863, 16, -1, @artname)
              goto FAILURE
         end

         goto SUCCESS
    end

    if 0=@islightweight
    begin
        declare #each_publication CURSOR LOCAL FAST_FORWARD FOR
            select DISTINCT
                    name,
                    snapshot_ready,
                    pubid,
                    publisher,
                    publisher_db,
                    sync_mode,
                    case
                        when centralized_conflicts=1 and decentralized_conflicts=1 then 2
                        when centralized_conflicts=1 and decentralized_conflicts=0 then 1
                        else 0
                    end,
                    replicate_ddl
                FROM dbo.sysmergepublications where
                    pubid in (select pubid from dbo.sysmergearticles where objid=@objid)
            for read only
    end
    else
    begin
        declare #each_publication CURSOR LOCAL FAST_FORWARD FOR
            select distinct
                    name,
                    case status
                        when @active then @ready
                        else @notready
                    end,
                    pubid,
                    publisher,
                    publisher_db,
                    0,    -- Lightweight does not further propagate DDL, thus certainly no need to invoke sp_MSNonSQLDDL for non-SQL subscribers.
                    case
                        when centralized_conflicts=1 and decentralized_conflicts=1 then 2
                        when centralized_conflicts=1 and decentralized_conflicts=0 then 1
                        else 0
                    end,
                    replicate_ddl
                from dbo.sysmergepublications
                where pubid in (select pubid from dbo.sysmergearticles where objid=@objid)
                for read only
    end

    open #each_publication
    fetch #each_publication into @pubname, @snapshot_ready, @pubid, @publisher, @publisher_db, @sync_mode, @conflict_logging, @replicate_ddl
    while (@@fetch_status <> -1)
    begin
        if upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
           db_name()=@publisher_db
        begin
            set @is_publisher= 1
        end
        else
        begin
            set @is_publisher= 0
        end

        declare @bypassreplicatedllpub bit
        set @bypassreplicatedllpub = @bypassreplicatedll

        if 0=@islightweight
        begin
            select @artname = name, @columnsbm=columns, @artid=artid
                        ,@dest_table=destination_object, @dest_owner=destination_owner
                        ,@sync_objid = sync_objid, @vertical_partition = vertical_partition, @schema_option = schema_option, @status = status
                from dbo.sysmergearticles
                where pubid=@pubid and objid=@objid
        end
        else
        begin
            select @artname= name,
            @artid=artid,
                   @columnsbm=0x00    -- Only used if this replica is a publisher.
                       ,@dest_table=destination_object, @dest_owner=destination_owner
                       ,@sync_objid = sync_objid, @vertical_partition = vertical_partition, @schema_option = schema_option, @status = status
                from dbo.sysmergearticles
                where pubid=@pubid and objid=@objid
        end

        set @NeedSchemaChange = 0
        set @missingORexcluded = NULL

    if @is_publisher = 1
    begin
        if exists (select * from #merge_altertable where DDLsubtype in ('DROPCOLUMN'))
        begin
            exec @retcode = sys.sp_MSFixPubColumnBitmaps     @pubid = @pubid,
                                                        @artid = @artid,
                                                        @bm = @missingORexcluded output,
                                                        @NeedSchemaChange = @NeedSchemaChange output

            if @@error<>0 or @retcode<>0
            goto FAILURE
        end


        declare #alter_table_cursor CURSOR LOCAL FAST_FORWARD FOR
            select DISTINCT id, ColumnName
            from #merge_altertable where DDLsubtype = N'ADDCOLUMN'
            order by id ASC
            for read only
        open #alter_table_cursor
        fetch #alter_table_cursor into @statement_id, @column

        while (@@fetch_status <> -1)
        begin
            select @excluded_cols = excluded_cols,
                @missing_cols = missing_cols
                from dbo.sysmergearticles
                where pubid=@pubid and objid=@objid

            if @replicate_ddl & 1 = 0
            -- When we are not replicating DDL we have to add the newly added column to
            -- the excluded columns bitmask.
            begin

                select @column_id = column_id from sys.columns where object_id = @objid and name = @column

                if exists (select pubid from dbo.sysmergearticles where artid=@artid and pubid<>@pubid and sys.fn_MSmerge_islocalpubid(pubid)=0)
                    select @is_republisher=1
                else
                    select @is_republisher=0

                if @is_republisher = 0
                -- If this is not the republisher then the column id is already absolute so set
                -- the bit in the exluded columns bitmask at the column id.
                begin
                    exec sys.sp_MSsetbit @excluded_cols OUTPUT, @column_id
                end
                else
                -- If this is not the republisher then we need to convert the relative column id to absolute value.
                begin
                    exec sys.sp_MSget_absolute_colid @missing_cols, @column_id, @abs_column_id output
                    exec sys.sp_MSsetbit @excluded_cols OUTPUT, @abs_column_id
                end


                exec @retcode= sys.sp_MSBitmapCount
                            @bm1= @excluded_cols,
                            @count= @excluded_col_count output

                update  dbo.sysmergearticles
                    set excluded_cols     = @excluded_cols,
                    excluded_col_count = @excluded_col_count
                    where pubid=@pubid and objid=@objid
            end
            else
            -- Always send the schema change to the subscriber to update its missing columns bitmask when adding a new column
            -- Some columns may have been added before this addcolumn with replicateddl set to 0. we need to make sure that
            -- those get marked as missing on the subscriber.
            begin
                select @NeedSchemaChange = 1

                exec @retcode= sys.sp_ORbitmap
                                @inputbitmap1= @missing_cols,
                                @inputbitmap2= @excluded_cols,
                                @resultbitmap3= @missingORexcluded output

            end

            fetch #alter_table_cursor into @statement_id, @column
        end -- while-loop over #alter_table_cursor

        close #alter_table_cursor
        deallocate #alter_table_cursor
    end

        -- check to make sure we have <= 246 columns in the table
        if exists (select * from #merge_altertable where DDLsubtype=N'ADDCOLUMN')
        begin
                if @sync_objid is not null
                begin
                    select @cCols= count(*) from sys.columns where object_id = @sync_objid and is_computed <> 1 and system_type_id <> type_id('timestamp')
                                                            and is_merge_published = 1

                    if @is_publisher=1 and (@replicate_ddl & 1 <> 0) and (@cCols > 246)
                    begin
                        RAISERROR (25006, 16, -1, @artname, 246)
                        goto FAILURE
                    end
                end
                else if @vertical_partition = 0
                begin
                    select @cCols= count(*) from sys.columns where object_id = @objid and is_computed <> 1 and system_type_id <> type_id('timestamp')
                    set @cMissing= coalesce((select max(missing_col_count) from dbo.sysmergearticles where objid = @objid), 0)
                    if ((@cCols + @cMissing) > 246)
                    begin
                        RAISERROR (25006, 16, -1, @artname, 246)
                        goto FAILURE
                    end
                end
        end
		
        -- only propagate this change when snapshot has been run; or at subscriber side where re-publishing is possible.
        if @snapshot_ready=@ready or @snapshot_ready=@snapshotobsolete
        begin

	        if (@status not in (2, 6))
	        begin
		        -- If the article is not active then we don't need schema change
		        select @NeedSchemaChange = 0
		        select @bypassreplicatedllpub=1
	        end

            if @NeedSchemaChange <> 0
            begin
                select @schemaversion_bitmask = schemaversion from dbo.sysmergeschemachange
                if (@schemaversion_bitmask is NULL)
                    set @schemaversion_bitmask = 1
                else
                    select @schemaversion_bitmask = 1 + max(schemaversion) from dbo.sysmergeschemachange

                set @schemaguid_bitmask = newid()
                set @schematype_bitmask= @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP
                set @schemasubtype_bitmask = 0
                exec sys.xp_varbintohexstr @missingORexcluded, @missingORexcludedStr output
                select @schematext_bitmask = 'exec dbo.sp_MSFixSubColumnBitmaps @artid = ''' + convert(nchar(36), @artid) + ''', @bm = ' + @missingORexcludedStr
                exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion_bitmask, @schemaguid_bitmask, @schematype_bitmask, @schematext_bitmask, @schemasubtype_bitmask
                if @@ERROR<>0 or @retcode<>0 goto FAILURE
            end

            -- Any statement containing drop-column has to be delivered - there is no bit to skip that.
            if @is_publisher=1 and (@replicate_ddl & 1 <> 0 or exists (select * from #merge_altertable where DDLsubtype=N'DROPCOLUMN'))
            begin
               -- check validity of disable trigger request, we want to protect trg used by merge
                if exists (select * from #merge_altertable where DDLsubtype=N'DISABLETRIGGER')
                begin
                    -- request like 'Alter table t2 DISABLE Trigger All'
                    if exists (select * from #merge_altertable where schemasubtype=@schemasubtype_disabletriggerall)
                    begin
                            raiserror(21541, 16, -1)
                            goto FAILURE
                    end

                    -- request like 'Alter table t2 DISABLE Trigger t2_ITrig, t2_ITrig2, t2_ITrig3'
                    exec @retcode=sys.sp_MSguidtostr @artid, @guidstr out
                    if @retcode<>0 or @@ERROR<>0
                    begin
                                goto FAILURE
                    end
                    set @instrigname = 'MSmerge_ins_' + @guidstr
                    set @updtrigname = 'MSmerge_upd_' + @guidstr
                    set @deltrigname = 'MSmerge_del_' + @guidstr
                    if exists (select ColumnName from #merge_altertable
                                    where DDLsubtype=N'DISABLETRIGGER'
                                    and (ColumnName=@instrigname or ColumnName=@updtrigname or ColumnName=@deltrigname)
                                  )
                    begin
                          raiserror(21545, 16, -1)
                            goto FAILURE
                    end
                end

                if @bypassreplicatedllpub=0
                begin

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

                    set @schemaguid = newid()
                    set @schematype = @SCHEMA_TYPE_DDL_ACTIONS

                    -- we need to change the 3 part naming into 2 part: [owner].[tablename]
                    if(@dest_owner is not NULL) and (len(@dest_owner) > 0)
                        select @qual_dest_object = QUOTENAME(@dest_owner) + N'.'
                    else
                        select @qual_dest_object = N''
                    if(@dest_table is not null) and (len(@dest_table) > 0)
                        select @qual_dest_object = @qual_dest_object + QUOTENAME(@dest_table)
                    else
                        select @qual_dest_object = @qual_object_name

                    select @temp_ddlcmds = N'if object_id(N''' +sys.fn_replreplacesinglequote(@qual_dest_object)+ N''') is not null exec('''
                                        + sys.fn_replreplacesinglequote('ALTER TABLE '
                                        + @qual_dest_object + N' '
                                        + @pass_through_scripts )+ N''')'

                    exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @temp_ddlcmds, @schemasubtype
                    if @@ERROR<>0 or @retcode<>0 goto FAILURE

                end
            end

            declare #alter_table_cursor CURSOR LOCAL FAST_FORWARD FOR
                select DISTINCT id, DDLsubtype, ColumnName, schemasubtype
                from #merge_altertable
                order by id ASC
                for read only
            open #alter_table_cursor
            fetch #alter_table_cursor into @statement_id, @subtype, @column, @schemasubtype

            while (@@fetch_status <> -1)
            begin

                --this contains addcolumn - do alter-table-add-column first
                if @schemasubtype=@schemasubtype_addcolumn
                begin
                    -- check if this is an identity column. if so we should prevent the add
                    if exists (select * from sys.columns where object_id = @objid and name = @column and is_identity=1)
                    begin
                        RAISERROR (25012, 16, -1)
                            goto FAILURE
                    end

                    -- check if this is a sparse column. if so we should prevent the add
                    if exists (select * from sys.columns where object_id = @objid and (is_sparse=1 or is_column_set=1) )
                    begin
                        raiserror(25025, 16, -1)
                        goto FAILURE
                    end

		      -- If the current column being altered is a hierachy/geometry/geography/filestream/new datetime
		      --block for SSCE subscribers according to the following table

			--                             Type		100RTM character		90RTM native		90RTM character
			-- 			hierarchy			block				block			block
			--		geometry/geography						block			block
			--			filestream		block				block			block
			--		new datetime types						block			block

		       if  exists  (select * from sys.columns sc
			               where object_id = @objid and name = @column and
			               ((type_name(sc.system_type_id) in ('date', 'time', 'datetime2', 'datetimeoffset'))
			               or (type_name(sc.user_type_id) in ('hierarchyid', 'geometry', 'geography'))
			               or (sc.system_type_id in (165) and sc.is_filestream = 1 and sc.max_length=-1))) -- filestream
		       begin
				if exists (select 1 from dbo.sysmergepublications where backward_comp_level<=90
								and pubid in (select pubid from dbo.sysmergearticles where objid = @objid))
				begin
			              RAISERROR (20739, 16, -1, @artname)
			              goto FAILURE				
				end

				if exists (select 1 from dbo.sysmergepublications where backward_comp_level=100
								and sync_mode = 1
								and pubid in (select pubid from dbo.sysmergearticles where objid = @objid) )
						  and (exists (select * from sys.columns sc
						                        where object_id = @objid and name = @column and
					                            (type_name(sc.user_type_id) = 'hierarchyid'
					                            or (sc.system_type_id in (165) and sc.is_filestream = 1 and sc.max_length=-1)))) -- filestream
				begin
			       	RAISERROR (22586, 16, -1, @column, @artname, @pubname)
			              goto FAILURE				
				end
		       end

                    if 0=@islightweight
                    begin
                        /* We are adding a column - include it in the count */
                        if @sync_objid is not null
                        begin
                            select @cCols= count(*) from sys.columns where object_id = @sync_objid and is_computed <> 1 and system_type_id <> type_id('timestamp')
                                                    and is_merge_published = 1
                            if @is_publisher=1 and (@replicate_ddl & 1 <> 0) and (@cCols + 1 > 246)
                            begin
                                RAISERROR (25006, 16, -1, @artname, 246)
                                    goto FAILURE
                            end
                        end
                        else if @vertical_partition = 0
                        begin
                            select @cCols= count(*) from sys.columns where object_id = @objid and is_computed <> 1 and system_type_id <> type_id('timestamp')
                            set @cMissing= coalesce((select max(missing_col_count) from dbo.sysmergearticles where objid = @objid), 0)
                            if ((@cCols + @cMissing + 1) > 246)
                            begin
                                RAISERROR (25006, 16, -1, @artname, 246)
                                    goto FAILURE
                             end
                        end

                        update dbo.sysmergearticles set vertical_partition=1 where pubid=@pubid and objid=@objid
                        if @@ERROR<>0 goto FAILURE

                        if not exists (select * from #merge_altertable where DDLsubtype=N'ADDCOLUMN' and id>@statement_id)
                        begin
                            --now drop conflict insert proc and set its column in dbo.sysmergearticle to NULL
                            exec @retcode=sp_MSDropTriggerProcs    @qual_object_name, @pubid
                            if @@ERROR<>0 or @retcode <>0 goto FAILURE
                        end

                        if @sync_mode = 1 and @replicate_ddl & 1 <> 0 and @bypassreplicatedllpub=0
                        begin
                            exec @retcode = sys.sp_MSNonSQLDDL @qual_object_name, @pubid, @column, @schemasubtype --add column
                            if @retcode <>0 or @@ERROR<>0 goto FAILURE
                        end

                        if @is_publisher=1 and @replicate_ddl & 1 <> 0
                        begin

                            if @schemaversion is not NULL and @bypassreplicatedllpub=0
                            begin
                                -- we need to quote object using 2 part: owner.tablename
                                -- we have to ensure table object exists as user can add a new article and do ddl on it, and then ask us
                                -- to upload before reinit
                                select @temp_ddlcmds = schematext from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion
                                select @temp_ddlcmds = N' if not exists (select * from syscolumns where name = N''' + sys.fn_replreplacesinglequote(@column) collate database_default  + ''' and id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_des
t_object)  collate database_default + ''')) ' + @temp_ddlcmds
                                -- Never overwrite schemasubtype of addcolumn, dropcolumn, altercolumn since these schema changes control
                                -- the versioning of the procedures. The sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid) function uses
                                -- the schema subtype to locate the maximum schema version guid pertaining to an article. If the article has
                                -- had a significant schema change of addcolumn, dropcolumn, altercolumn (that causes procs to be reversioned)
                                -- in the same batch as some non-significant schemachanges such as drop constraints etc, the schemasubtype of
                                -- the significant schemachange needs to be preserved.
                                select @schematype_existing = schematype , @schemasubtype_existing = schemasubtype from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion
                                if ((@schematype_existing = @SCHEMA_TYPE_DDL_ACTIONS) and @schemasubtype_existing in (@schemasubtype_addcolumn, @schemasubtype_dropcolumn, @schemasubtype_altercolumn))
                                    set @schemasubtype = @schemasubtype_existing

                                update dbo.sysmergeschemachange
                                    set schemasubtype = @schemasubtype,
                                        schematext = @temp_ddlcmds
                                        where pubid=@pubid and schemaversion=@schemaversion
                            end
                            if @bypassreplicatedllpub=0
                            begin
                                    -- Indicate that this is an internal caller of sp_mergearticlecolumn
                                    EXEC @retcode = sys.sp_MSsetcontext_internalcaller @onoff=1
                                    IF @@ERROR <> 0 or @retcode <> 0
                                        goto FAILURE

                                exec @retcode = sys.sp_mergearticlecolumn @pubname, @artname, @column, 'add', 'true'
                                if @@ERROR<>0 or @retcode <>0 goto FAILURE

                                    -- Turn off indication that this is an internal caller of sp_mergearticlecolumn
                                    EXEC @retcode = sys.sp_MSsetcontext_internalcaller @onoff=0
                                    IF @@ERROR <> 0 or @retcode <> 0
                                        goto FAILURE

                            end
                            -- regenerate publication view at publishser side
                            exec @retcode = sys.sp_MSpublicationview @publication = @pubname, @force_flag = 2, @articlename = @artname
                            if @retcode<>0 or @@ERROR<>0
                            begin
                                raiserror(25008, 16, -1)
                                goto FAILURE
                            end

                            --conflict table is at publisher side for centralized conflicts 
                            --conflict table is at publisher and subscriber for decentralized conflicts
                            exec @retcode = sys.sp_MSAdjustConflictTable @qual_object_name, @pubid,@column, @column_change_type=1
                            if @@ERROR<>0 or @retcode <>0 goto FAILURE

                        end
                        else if @conflict_logging <> 1 and @replicate_ddl & 1 <> 0 --adjust the conflict table at subscriber side, if logging is decentralized or both
                        begin
                            exec @retcode = sys.sp_MSAdjustConflictTable @qual_object_name, @pubid,@column, @column_change_type=1
                            if @@ERROR<>0 or @retcode <>0 goto FAILURE
                        end
                    end -- 0=@islightweight
                    else
                    begin
                        --conflict table is at publisher side for centralized conflicts 
                        --conflict table is at publisher and subscriber for decentralized/both conflicts
                        if @conflict_logging <> 1 and @replicate_ddl & 1 <> 0
                        begin
                            exec @retcode= sys.sp_MSAdjustConflictTable @qual_object_name, @pubid,@column, @column_change_type=1
                            if @@ERROR<>0 or @retcode <>0 goto FAILURE
                        end
                    end

                    if not exists (select * from #merge_altertable where DDLsubtype=N'ADDCOLUMN' and id>@statement_id)
                    begin
                        exec @retcode = sys.sp_MSResetTriggerProcs @qual_object_name, @pubid, 1 /* @article_level_only */
                        if @@ERROR<>0 or @retcode <>0 goto FAILURE
                    end
                end
                --this contains dropcolumn
                else if @schemasubtype = @schemasubtype_dropcolumn
                begin
                    if 0=@islightweight
                    begin
                        update dbo.sysmergearticles set vertical_partition=1 where pubid=@pubid and objid=@objid
                        if @@ERROR<>0 goto FAILURE
                    end

                    if not exists (select * from #merge_altertable where DDLsubtype=N'DROPCOLUMN' and id>@statement_id)
                    begin
                        --now drop conflict insert proc and set its column in dbo.sysmergearticle to NULL
                        exec @retcode= sys.sp_MSDropTriggerProcs @qual_object_name, @pubid
                        if @@ERROR<>0 or @retcode <>0 goto FAILURE
                    end

                    if @is_publisher=1
                    begin
                        -- we need to quote object using 2 part: owner.tablename
                        --if this column exists in the subscriber's partition
                        select @temp_ddlcmds = schematext from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion
                        select @temp_ddlcmds = N' if exists (select * from syscolumns where name = N''' + sys.fn_replreplacesinglequote(@column) collate database_default + ''' and id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_dest_object) coll
ate database_default + ''')) ' + @temp_ddlcmds
                        -- Never overwrite schemasubtype of addcolumn, dropcolumn, altercolumn since these schema changes control
                        -- the versioning of the procedures. The sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid) function uses
                        -- the schema subtype to locate the maximum schema version guid pertaining to an article. If the article has
                        -- had a significant schema change of addcolumn, dropcolumn, altercolumn (that causes procs to be reversioned)
                        -- in the same batch as some non-significant schemachanges such as drop constraints etc, the schemasubtype of
                        -- the significant schemachange needs to be preserved.
                        select @schematype_existing = schematype , @schemasubtype_existing = schemasubtype from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion
                        if ((@schematype_existing = @SCHEMA_TYPE_DDL_ACTIONS) and @schemasubtype_existing in (@schemasubtype_addcolumn, @schemasubtype_dropcolumn, @schemasubtype_altercolumn))
                            set @schemasubtype = @schemasubtype_existing

                        update dbo.sysmergeschemachange
                            set schemasubtype = @schemasubtype,
                                schematext = @temp_ddlcmds
                            where pubid=@pubid and schemaversion=@schemaversion

                        --adjust column bitmask now that vertical partitioning is on
                        select @max_colid = max(column_id) from sys.columns where object_id = @objid
                        set @colid = 1
                        set @columnsbm_after_drop = 0x00
                        while (@colid < @max_colid + 1) --take care of the case where last column gets dropped
                        begin
                            exec @in_partition = sys.sp_MStestbit @bm=@columnsbm, @coltotest=@colid
                            --it current column among those dropped?
                            if @in_partition <> 0 and exists
                                (select * from sys.columns where object_id = @objid and column_id = @colid and is_computed <>1 and system_type_id <> type_id('timestamp'))
                            begin
                                --reset bit in column bitmask
                                exec @retcode=sys.sp_MSsetbit @columnsbm_after_drop OUTPUT, @colid
                                if @@ERROR<>0 or @retcode<>0 goto FAILURE
                            end
                            set @colid = @colid + 1
                        end
                        --update current columns bitmap
                        update dbo.sysmergearticles set columns = @columnsbm_after_drop where pubid=@pubid and objid=@objid
                        if @@ERROR<>0 goto FAILURE
                        --regenerate publication view
                        exec @retcode = sys.sp_MSpublicationview @publication = @pubname, @force_flag = 2, @articlename = @artname
                        if @retcode<>0 or @@ERROR<>0
                        begin
                            raiserror(25008, 16, -1)
                            goto FAILURE
                        end

                        --conflict table is at publisher side for centralized conflicts 
                        --conflict table is at publisher and subscriber for decentralized conflicts
                        exec @retcode = sys.sp_MSAdjustConflictTable @qual_object_name, @pubid, @column, @column_change_type=2 --drop column
                        if @@ERROR<>0 or @retcode <>0 goto FAILURE

                    end
                    else if @conflict_logging <> 1 --adjust conflict table at subscriber side
                    begin
                            exec @retcode = sys.sp_MSAdjustConflictTable @qual_object_name, @pubid, @column, @column_change_type=2 --drop column
                            if @@ERROR<>0 or @retcode <>0
                                goto FAILURE
                    end

                    --generate ALTER TABLE DROP for heterogenous subscribers
                    if @sync_mode = 1
                    begin
                        exec @retcode = sys.sp_MSNonSQLDDL @qual_object_name, @pubid, @column, @schemasubtype --drop column
                        if @@ERROR<>0 or @retcode <>0
                                goto FAILURE
                    end


                    if not exists (select * from #merge_altertable where DDLsubtype=N'DROPCOLUMN' and id>@statement_id)
                    begin
                        exec @retcode = sys.sp_MSResetTriggerProcs @qual_object_name, @pubid, 1 /* @article_level_only */
                        if @@ERROR<>0 or @retcode <>0 goto FAILURE
                    end
                end
                -- this contains ALTER-TABLE ALTER COLUMN
                else if @schemasubtype = @schemasubtype_altercolumn
                begin
                    -- Modify the alter column to containg the if exists check - do this only at the publisher
                    if @is_publisher=1
                    begin
                        select @temp_ddlcmds = schematext from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion
                        select @temp_ddlcmds = N' if exists (select * from syscolumns where name = N''' + sys.fn_replreplacesinglequote(@column) collate database_default + ''' and id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_dest_object) coll
ate database_default + ''')) ' + @temp_ddlcmds

                        -- Never overwrite schemasubtype of addcolumn, dropcolumn, altercolumn since these schema changes control
                        -- the versioning of the procedures. The sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid) function uses
                        -- the schema subtype to locate the maximum schema version guid pertaining to an article. If the article has
                        -- had a significant schema change of addcolumn, dropcolumn, altercolumn (that causes procs to be reversioned)
                        -- in the same batch as some non-significant schemachanges such as drop constraints etc, the schemasubtype of
                        -- the significant schemachange needs to be preserved.
                        select @schematype_existing = schematype , @schemasubtype_existing = schemasubtype from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion
                        if ((@schematype_existing = @SCHEMA_TYPE_DDL_ACTIONS) and @schemasubtype_existing in (@schemasubtype_addcolumn, @schemasubtype_dropcolumn, @schemasubtype_altercolumn))
                            set @schemasubtype = @schemasubtype_existing
                        update dbo.sysmergeschemachange
        set schemasubtype = @schemasubtype,
                                schematext = @temp_ddlcmds
                            where pubid=@pubid and schemaversion=@schemaversion
                    end
                    -- If the current column being altered is a BLOB/XML/UDT column, disallow ALTER COLUMN if we map down datatypes to base datatypes
                    -- Since only the lower 32 bits of @schema_option are
                    -- currently used, the following check is sufficient.
                    DECLARE @schema_option_lodword int
                    SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
                    select @colid = column_id from sys.columns where object_id = @objid and name = @column

                    --Is the current colid being altered a published column
                    exec @in_partition = sys.sp_MStestbit @bm=@columnsbm, @coltotest=@colid

                    -- If the current column being altered is a BLOB/XML/UDT column, disallow ALTER COLUMN if we map down datatypes to base datatypes
                    if @in_partition <> 0 and (exists
                    (select * from sys.columns sc
                        where object_id = @objid and column_id = @colid and
                            ((type_name(sc.system_type_id) in ('image', 'text', 'ntext', 'xml') and sc.max_length = -1) -- Max datatype
                            or sc.system_type_id in (240))                                                            -- CLR-UDTs
                        and ((@schema_option_lodword & 0x10000000 > 0) --map xml to ntext
                        or (@schema_option_lodword & 0x00000020 > 0) --map udt to image
                        or (@schema_option_lodword & 0x20000000 > 0))))--map max to blob
                    begin
                        RAISERROR (21844, 16, -1, @artname)
                        goto FAILURE
                    end

                    -- If the current column being altered is a hierachy/geometry/geography/filestream/new datetime
                    --block for SSCE subscribers according to the following table

			--                             Type		100RTM character		90RTM native		90RTM character
			--                   hierarchy		block				block			block
			--		geometry/geography						block			block
			--			filestream		block				block			block
			--		new datetime types						block			block

		       if  exists  (select * from sys.columns col
		       				join sys.types typ on typ.user_type_id = col.user_type_id
		       				join #merge_altertable ma on col.name = ma.ColumnName collate database_default
		       		  where object_id = @objid
		       		  	and (ma.schemasubtype = @schemasubtype_altercolumn)
		       		     	and (typ.name in (N'date', N'time', N'datetime2', N'datetimeoffset', N'hierarchyid', N'geometry', N'geography')))

		     begin
				if exists (select * from dbo.sysmergepublications where backward_comp_level<=90
								and pubid in (selec
 
Last revision SQL2008SP2
See also

  sp_MSmerge_ddldispatcher (Procedure)
sp_MSrefresh_idrange_check_constraint (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