Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_addmergearticle

  No additional text.


Syntax
create procedure sys.sp_addmergearticle
    @publication            sysname,                            /* publication name */
    @article                sysname,                            /* article name */
    @source_object          sysname,                            /* source object name */
    @type                   sysname = 'table',                  /* article type */
    @description            nvarchar(255)= NULL,                /* article description */
    @column_tracking        nvarchar(10) = 'false',             /* column level tracking */
    @status                 nvarchar(10) = 'unsynced',          /* unsynced, active */
    @pre_creation_cmd       nvarchar(10) = 'drop',              /* 'none', 'drop', 'delete', 'truncate' */
    @creation_script        nvarchar(255)= NULL,                /* article schema script */
    @schema_option          varbinary(8)   = NULL,              /* article schema creation options */
    @subset_filterclause    nvarchar(1000) = '',                /* filter clause */
    @article_resolver       nvarchar(255)= NULL,                /* custom resolver for article */
    @resolver_info          nvarchar(517) = NULL,               /* custom resolver info */
    @source_owner           sysname = NULL,
    @destination_owner      sysname = NULL,
    @vertical_partition     nvarchar(5) = 'FALSE',             /* vertical partitioning or not */
    @auto_identity_range    nvarchar(5) = NULL,                /* this parameter is deprecated. use @identityrangemanagementoption */
    @pub_identity_range     bigint    = NULL,
    @identity_range         bigint = NULL,
    @threshold              int = NULL,
    @verify_resolver_signature     int = 1,                    /* 0=do not verify signature, 1=verify that signature is from trusted source, more values may be added later */
    @destination_object            sysname = @source_object,
    @allow_interactive_resolver    nvarchar(5) = 'false',        /* whether article allows interactive resolution or not */
    @fast_multicol_updateproc      nvarchar(5) = 'true',        /* whether update proc should update multiple columns in one update statement or not. if 0, then separate update issued for each column changed. */
    @check_permissions         int = 0, /* bitmap where 0x00 for nochecks, 0x01 for insert check, 0x2 for update check, 0x4 for delete check */
    @force_invalidate_snapshot bit = 0, /* Force invalidate existing snapshot */
    @published_in_tran_pub     nvarchar(5) = 'false', /* Indicates that this article could be published in a transactional publication as well */
    @force_reinit_subscription bit = 0, /* Force reinit subscription */
    @logical_record_level_conflict_detection nvarchar(5) = 'false',
    @logical_record_level_conflict_resolution nvarchar(5) = 'false',
    @partition_options tinyint = 0, -- 0, 1, 2 or 3 meaning none, no out of partition dml, partition based and subscription based
    @processing_order int = 0,
    @subscriber_upload_options tinyint = 0, -- possible values are 0, 1, and 2 meaning 'allow uploads', 'disables uplods', 'disable uploads and prohibit subscriber changes'
    @identityrangemanagementoption nvarchar(10) = NULL, -- NONE, MANUAL, AUTO
    @delete_tracking    nvarchar(5) = 'true',    --'true' = replicate deletes as usual.  false'= do not track deletes in triggers..
    @compensate_for_errors    nvarchar(5) = 'false',
    @stream_blob_columns nvarchar(5) = 'false'    -- 'true'= use blob optimization, 'false' = disable blob optimization.
    AS

    set nocount on

    declare @max_range              numeric(38,0)
    declare @min_range              numeric(38,0)
    declare @max_used               numeric(38,0)
    declare @ident_incr             numeric(38,0)
    declare @publisher              sysname
    declare @publisher_db           sysname
    declare @already_published      bit
    declare @identity_so_far        bigint
    declare @ver_partition          int
    declare @sp_resolver            sysname
    declare @num_columns            smallint
    declare @pubid                  uniqueidentifier                /* Publication id */
    declare @db                     sysname
    declare @identity_support       int
    declare @object                 sysname
    declare @owner                  sysname
    declare @retcode                int
    declare @objid                  int
    declare @sync_objid             int
    declare @typeid                 smallint
    declare @replnick               binary(6)
    declare @merge_pub_object_bit   int
    declare @column_tracking_id     int
    declare @cmd                    nvarchar(2000)
    declare @statusid               tinyint --1: inactive; 2: active; 5:new_inactive 6:new_active
    declare @next_seed              bigint
    declare @precmdid               int
    declare @resolver_clsid         nvarchar(50)
    declare @resolver_clsid_old     nvarchar(50)
    declare @tablenick              int
    declare @artid                  uniqueidentifier
    declare @i                      int
    declare @max_identity           bigint
    declare @colname                sysname
    declare @indid                  int
    declare @pkkey                  sysname
    declare @dbname                 sysname
    declare @db_name                sysname
    declare @subset                 int
    declare @is_publisher           int
    declare @row_size               int
    declare @sp_name                sysname
    declare @sp_owner               sysname
    declare @qualified_name         nvarchar(517)
    declare @snapshot_ready         tinyint
    declare @sync_mode              tinyint
    declare @allow_interactive_bit  bit
    declare @fast_multicol_updateproc_bit bit
    declare @additive_resolver       sysname
    declare @average_resolver        sysname
    declare @mindate_resolver        sysname
    declare @needs_pickup            bit
    declare @maxdate_resolver        sysname
    declare @minimum_resolver        sysname
    declare @maximum_resolver        sysname
    declare @mergetxt_resolver       sysname
    declare @pricolumn_resolver      sysname
    declare @xtype                   int
    declare @xprec                   int
    declare @bump_to_80              bit
    declare @gen                     bigint
    declare @genguid                 uniqueidentifier
    declare @dt                      datetime
    declare @qualname                nvarchar(517)
    declare @compatlevel             int
    declare @allow_partition_realignment bit
    declare @logical_record_level_conflict_detection_id bit
    declare @logical_record_level_conflict_resolution_id bit
    declare @published_in_tran_pub_bit bit
    declare @allow_anonymous bit
    declare @compensateforerrors bit
    declare @deletetracking bit
    declare @reinit_subscriptions bit
    declare @article_has_dynamic_filters bit
    declare @publication_has_dynamic_filters bit
    declare @REPOLEVersion_80SP3 int
    declare @stream_blob_columns_bit bit
    declare @missing_col_count       int
    declare @missing_cols            varbinary(128)
    declare @pubname_lessthan90compat sysname
    declare @preserve_rowguidcol bit
    declare @automatic_reinitialization_policy bit
    declare @use_partition_groups smallint
    declare @pub_number smallint
    declare @functions_in_subset_filter nvarchar(500)
    declare @dynamic_filters_function_list nvarchar(500)
    declare @got_merge_admin_applock bit
            	,@obj_name sysname
    declare @filestream_col_published int
    declare @has_filestream int

    select @got_merge_admin_applock = 0
    select @filestream_col_published = 0
    select @has_filestream = 0

    -- Security Check
    exec @retcode= sys.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0 return (1)

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

    /*
    ** Initializations
    */
    set @REPOLEVersion_80SP3= 60
    select @is_publisher = 0
    select @needs_pickup = 0
    select @bump_to_80 = 0
    select @already_published = 0
    select @publisher = publishingservername()
    select @publisher_db = db_name()
    select @max_identity    = NULL
    select @next_seed        = NULL
    select @statusid        = 0
    select @resolver_clsid    = NULL
    select @subset            = 1        /* Const: publication type 'subset' */
    select @merge_pub_object_bit    = 128
    select @db_name = db_name()
    select @additive_resolver   = formatmessage(21701)
    select @average_resolver    = formatmessage(21702)
    select @mindate_resolver    = formatmessage(21703)
    select @maxdate_resolver    = formatmessage(21704)
    select @minimum_resolver    = formatmessage(21706)
    select @mergetxt_resolver   = formatmessage(21707)
    select @maximum_resolver    = formatmessage(21708)
    select @pricolumn_resolver  = formatmessage(21709)
    select @sp_resolver         = formatmessage(21712)
    select @reinit_subscriptions = 0
    select @article_has_dynamic_filters = 0
    select @publication_has_dynamic_filters = 0
    select @pubname_lessthan90compat = NULL

    if @subscriber_upload_options not in (0, 1, 2)
    begin
        raiserror (22542, 16, -1)
        return (1)
    end

    if 'false' = lower(@compensate_for_errors collate SQL_Latin1_General_CP1_CS_AS)
    begin
        set @compensateforerrors= 0
    end
    else if 'true' = lower(@compensate_for_errors collate SQL_Latin1_General_CP1_CS_AS)
    begin
        set @compensateforerrors= 1
    end
    else
    begin
        raiserror (14148, 16, -1, '@compensate_for_errors')
        return (1)
    end

    if 'false' = lower(@delete_tracking collate SQL_Latin1_General_CP1_CS_AS)
    begin
        set @deletetracking= 0
    end
    else if 'true' = lower(@delete_tracking collate SQL_Latin1_General_CP1_CS_AS)
    begin
        set @deletetracking= 1
    end
    else
    begin
        raiserror (14148, 16, -1, '@delete_tracking')
        return (1)
    end

    if 'false' = lower(@stream_blob_columns collate SQL_Latin1_General_CP1_CS_AS)
    begin
        set @stream_blob_columns_bit= 0
    end
    else if 'true' = lower(@stream_blob_columns collate SQL_Latin1_General_CP1_CS_AS)
    begin
        set @stream_blob_columns_bit= 1
    end
    else
    begin
        raiserror (14148, 16, -1, '@stream_blob_columns')
        return (1)
    end

    if @source_owner is NULL
    begin
        select @source_owner = SCHEMA_NAME(schema_id) from sys.objects where object_id = object_id(QUOTENAME(@source_object))
        if @source_owner is NULL
        begin
            raiserror (14027, 11, -1, @source_object)
            return (1)
        end
    end

    select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_object)

    /*
    **    Get the id of the @qualified_name
    */
    select @objid = OBJECT_ID(@qualified_name)
    if @objid is NULL
    begin
        raiserror (14027, 11, -1, @qualified_name)
        return (1)
    end

    -- check if the object is marked as ms shipped. If so it cannot be published
    if exists (select 1 from sys.objects where object_id = @objid and is_ms_shipped=1)
    begin
        raiserror (20696, 16, -1, @qualified_name)
        return (1)
    end

    if @destination_owner is NULL
        select @destination_owner='dbo'

    /*
    ** Pad out the specified schema option to the left
    */
    select @schema_option = fn_replprepadbinary8(@schema_option)

    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    if @publication is NULL
    begin
        raiserror (14043, 16, -1, '@publication', 'sp_addmergearticle')
        return (1)
    end

    select @pubid = pubid,
           @snapshot_ready = snapshot_ready,
           @sync_mode=sync_mode,
           @compatlevel=backward_comp_level,
           @allow_anonymous = allow_anonymous,
           @use_partition_groups = use_partition_groups,
           @pub_number = publication_number,
           @publication_has_dynamic_filters = dynamic_filters,
           @allow_partition_realignment = allow_partition_realignment,
           @automatic_reinitialization_policy = automatic_reinitialization_policy,
           @dynamic_filters_function_list = dynamic_filters_function_list
    from dbo.sysmergepublications
    where name = @publication and UPPER(publisher) collate database_default = UPPER(@publisher) collate database_default and publisher_db=@publisher_db
    if @pubid is NULL
    begin
        raiserror (20026, 16, -1, @publication)
        return (1)
    end

    if lower(@article)='all'
    begin
        raiserror(21401, 16, -1)
        return (1)
    end

    if  (0=@allow_partition_realignment and 0=@subscriber_upload_options)
    begin
        raiserror(22543, 16, -1)
        return (1)
    end

    -- Compensate for errors can be turned on only when upload options allows subscriber uploads.
    if 1=@compensateforerrors and (1=@subscriber_upload_options or 2=@subscriber_upload_options)
    begin
        raiserror(20022, 10, -1)
    end

    -- Parameter check @subset_filterclause
    if @subset_filterclause <> '' and @subset_filterclause is not NULL
    begin
        /* check the validity of subset_filterclause */
        exec ('declare @test int select @test=1 from ' + @qualified_name + ' where (1=2) and ' + @subset_filterclause)
        if @@ERROR<>0
        begin
            raiserror(21256, 16, -1, @subset_filterclause, @article)
            return (1)
        end

        -- check if the subsetfilter clause contains a computed column. To do this get a list of computed columns
        -- for the given article. Then check if the filter name is like the computed column
        declare @computedcolname sysname

        declare compted_columns_cursor cursor LOCAL FAST_FORWARD
        for (select name from sys.columns where object_id = @objid and is_computed=1)
        open compted_columns_cursor
        fetch compted_columns_cursor into @computedcolname
        while (@@fetch_status <> -1)
        begin

            if sys.fn_MSisfilteredcolumn(@subset_filterclause, @computedcolname, @objid) = 1
            begin
                raiserror(20656, 16, -1)
                return (1)
            end
            fetch compted_columns_cursor into @computedcolname
        end
        close compted_columns_cursor
        deallocate compted_columns_cursor


        -- check if the subsetfilter clause contains any column of type that is not supported in
        --  a subset filter.

        if exists    (
                        select * from sys.columns
                        where object_id = @objid and
                            (
                            --(sys.fn_IsTypeBlob(sc.system_type_id,sc.max_length) = 1) -- Blob type text,ntext,xml
                              (system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml')))
                              or max_length = -1
                              or system_type_id = 240    -- CLR-UDTs
                            )
                        and
                        sys.fn_MSisfilteredcolumn(@subset_filterclause, name, @objid) = 1
                    )
        begin
            raiserror(22518, 16, -1, @qualified_name)
            return (1)
        end

    end

    /*
    ** Parameter Check: @type
    ** If the article is added as a 'indexed view schema only' article,
    ** make sure that the source object is a schema-bound view.
    ** Conversely, a schema-bound view cannot be published as a
    ** 'view schema only' article.
    */
    select @type = lower(@type collate SQL_Latin1_General_CP1_CS_AS)

    if @type = N'indexed view schema only' and objectproperty(object_id(@qualified_name), 'IsSchemaBound') <> 1
    begin
        raiserror (21277, 11, -1, @qualified_name)
        return (1)
    end
    else if @type = N'view schema only' and objectproperty(object_id(@qualified_name), 'IsSchemaBound') = 1
    begin
        raiserror (21275, 11, -1, @qualified_name)
        return (1)
    end

    /*
    ** Only publisher can call sp_addmergearticle
    */
    EXEC @retcode = sys.sp_MScheckatpublisher @pubid
    IF @@ERROR <> 0 or @retcode <>    0
    BEGIN
        RAISERROR (20073, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is local, that it conforms
    ** to the rules for identifiers, and that it is a table, and not
    ** a view or another database object.
    */

    exec @retcode = sys.sp_MSreplcheck_name @article, '@article', 'sp_addmergearticle'
    if @@ERROR <> 0 or @retcode <> 0
        return(1)


    /*
    ** Set the precmdid.  The default type is 'drop'.
    **
    **        @precmdid    pre_creation_cmd
    **        =========    ================
    **              0        none
    **              1        drop
    **              2        delete
    **              3        truncate
    */
    IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop', 'delete', 'truncate')
    BEGIN
      RAISERROR (14061, 16, -1)
      RETURN (1)
    END

    /*
    ** Determine the integer value for the pre_creation_cmd.
    */
    IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'none'
       select @precmdid = 0
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
       select @precmdid = 1
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'delete'
       select @precmdid = 2
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'truncate'
       select @precmdid = 3


    /*
    ** Set the typeid.    The default type is table.    It can
    ** be one of following.
    **
    **        @typeid        type
    **        =======        ========
    **           0xa        table
    **          0x20        proc schema only
    **          0x40        view schema only
    **          0x80        func schema only
    **          0x40        indexed view schema only (overloaded)
    **          0xA0        synonym schema only
    */

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('table', 'proc schema only', 'view schema only', 'func schema only', 'indexed view schema only', 'synonym schema only')
       BEGIN
            RAISERROR (21276, 16, -1)
            RETURN (1)
       END

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'table'
    BEGIN
       SET @typeid = 0x0a
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'proc schema only'
    BEGIN
       SET @typeid = 0x20
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'view schema only'
    BEGIN
       SET @typeid = 0x40
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'indexed view schema only'
    BEGIN
       SET @typeid = 0x40
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'func schema only'
    BEGIN
       SET @typeid = 0x80
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'synonym schema only'
    BEGIN
       SET @typeid = 0xA0
    END


    select @sync_objid = OBJECT_ID(@qualified_name)
    if @sync_objid is NULL
        begin
            raiserror (14027, 11, -1, @qualified_name)
            return (1)
        end


    if @typeid in (0x20,0x40,0x80, 0xA0)
    begin
        if exists (select * from syscomments
                    where id = @sync_objid
                      and encrypted = 1)
        begin
            raiserror(21004, 16, -1, @source_object)
            return 1
        end
    end

    /*
    ** Parameter Check:     @article, @publication.
    ** Check if the article already exists in this publication.
    */

    IF EXISTS (SELECT *
                FROM dbo.sysmergeextendedarticlesview
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
            raiserror (21292, 16, -1, @article)
            RETURN (1)
        END

    --if @compatlevel < 90 and @processing_order <> 0
    --begin
    --    raiserror(21585, 16, -1, @publication)
        --return 1
    --end

    /*
    ** At this point, all common parameter validations
    ** for table and schema only articles have been
    ** performed, so branch out here to handle schema
    ** only articles as a special case.
    */

    IF @typeid in (0x20, 0x40, 0x80, 0xA0)
    BEGIN
        IF @destination_object IS NULL OR @destination_object = N''
        BEGIN
            SELECT @destination_object = @source_object
        END

        IF @schema_option IS NULL
        BEGIN
            SELECT @schema_option = 0x0000000000000001
        END
        EXEC @retcode = sys.sp_MSaddmergeschemaarticle
                @pubid = @pubid,
                @article = @article,
                @source_object = @source_object,
                @type = @typeid,
                @description = @description,
                @status = @status,
                @pre_creation_command = @precmdid,
                @creation_script = @creation_script,
                @source_owner = @source_owner,
                @destination_owner = @destination_owner,
                @schema_option = @schema_option,
                @destination_object = @destination_object,
                @qualified_name = @qualified_name,
                @publication = @publication,
                @snapshot_ready = @snapshot_ready,
                @force_invalidate_snapshot = @force_invalidate_snapshot,
                @processing_order = @processing_order

       RETURN (@retcode)
    END

    /*
    ** Make sure that the table name specified is a table.
    */

    if OBJECT_ID(@qualified_name, 'U') is NULL
    begin
        raiserror (20074, 16, -1)
        return (1)
    end

    /*
        Refer to the Yukon_Merge_Feature_Reference_Tables.doc for
        the detailed schema_option table.
    */
    IF @schema_option IS NULL
    BEGIN
        IF @compatlevel < 90
        BEGIN
            IF @sync_mode = 0   -- native
            BEGIN
    	        SELECT @schema_option = 0x0000000030034FF1
            END
            ELSE                -- character (SSCE)
            BEGIN
    	        SELECT @schema_option = 0x0000000030034FF1
            END
        END
        ELSE    -- 90 compatlevel
        BEGIN
            IF @sync_mode = 0   -- native
            BEGIN
    	        SELECT @schema_option = 0x000000000C034FD1
            END
            ELSE                -- character (SSCE)
            BEGIN
    	        SELECT @schema_option = 0x0000000008034FF1
            END
        END
    END

    /*
       Verify that the schema option being set is compatible with
       publication compatibility level.
    */
    -- Since only the lower 32 bits of @schema_option are
    -- used, the following check is sufficient. Note that @schema_option is
    -- already padded out to the left at the beginning of this procedure.
    -- whenever anything here is changed also change sp_MSmap_subscriber_type
    declare @schema_option_lodword int
    declare @schema_option_hidword int
    declare @schema_option_xml_to_ntext int
    declare @schema_option_max_to_nonmax int
    declare @schema_option_create_schema int
    declare @schema_option_xml_indexes int
    declare @schema_option_katmaidatetime_to_string int
    declare @schema_option_hierarchyid_to_varbinarymax int
    declare @schema_option_largeUDT_to_varbinarymax int
    declare @schema_option_spatial_indexes int
    declare @schema_option_spatial_to_varbinarymax int
    declare @schema_option_udt_to_base_types int
    declare @schema_option_filtered_indexes int
    declare @schema_option_filestream_attribute int
    declare @schema_option_str sysname

    declare @schema_option_compression int
    select @schema_option_lodword = sys.fn_replgetbinary8lodword(@schema_option)
    select @schema_option_hidword = sys.fn_replgetbinary8hidword(@schema_option)
    select @schema_option_xml_to_ntext  = 0x10000000 -- this has to be on  for < 90RTM
    select @schema_option_max_to_nonmax = 0x20000000 -- this has to be on  for < 90RTM
    select @schema_option_create_schema = 0x08000000 -- this has to be off for < 90RTM
    select @schema_option_xml_indexes   = 0x04000000 -- this has to be off for < 90RTM
    select @schema_option_katmaidatetime_to_string = 0x00000002

    select @schema_option_hierarchyid_to_varbinarymax = 0x00000020 -- this has to be on for < 100RTM
    select @schema_option_largeUDT_to_varbinarymax = 0x00000010 -- this has to be on for < 100RTM
    select @schema_option_spatial_indexes = 0x00000100 -- this has to be off for < 100RTM
    select @schema_option_spatial_to_varbinarymax = 0x00000080 -- this has to be on for < 100RTM
    select @schema_option_udt_to_base_types = 0x00000020
    select @schema_option_filtered_indexes = 0x00000040 -- this has to be off for < 100RTM
    select @schema_option_filestream_attribute = 0x00000001
    select @schema_option_compression   = 0x00000004 -- this has to be off for < 100RTM and SSCE
    select @schema_option_filtered_indexes = 0x00000040 -- this has to be off for < 100RTM

    declare @schema_option_has_changed bit;
    select @schema_option_has_changed = 0;


    /*
    ** If compatlevel is less than 100RTM then make sure the schema option to map down
    ** 100 datatypes to equivalent downlevel datatypes is set.
    ** * Namely, map large UDT down to varbinary(max)
    ** * If spatial_indexes are set for replication, disable if compatlevel < 100
    ** * Map spatial types to varbinary(max)
    ** * Make sure the schema option to map down 100 datetime types to downlevel data types.
    ** * Disable compression if it is enabled
    ** * Map hierarchyid to varbinary(max)
    */
    if (@compatlevel < 100 and
       ((@schema_option_hidword & @schema_option_largeUDT_to_varbinarymax = 0) or
       (@schema_option_hidword & @schema_option_compression <> 0) or
       (@schema_option_hidword & @schema_option_hierarchyid_to_varbinarymax = 0) or
       (@schema_option_hidword & @schema_option_spatial_to_varbinarymax = 0) or
       (@schema_option_hidword & @schema_option_spatial_indexes <> 0) or
       (@schema_option_hidword & @schema_option_filtered_indexes <> 0) or
       (@schema_option_hidword & @schema_option_katmaidatetime_to_string = 0)))
    begin
        select @schema_option_hidword = @schema_option_hidword | @schema_option_largeUDT_to_varbinarymax
        select @schema_option_hidword = @schema_option_hidword & ~(@schema_option_compression)
        select @schema_option_hidword = @schema_option_hidword | @schema_option_hierarchyid_to_varbinarymax
        select @schema_option_hidword = @schema_option_hidword | @schema_option_spatial_to_varbinarymax
        select @schema_option_hidword = @schema_option_hidword & ~(@schema_option_spatial_indexes)
        select @schema_option_hidword = @schema_option_hidword & ~(@schema_option_filtered_indexes)
        select @schema_option_hidword = @schema_option_hidword | @schema_option_katmaidatetime_to_string

        select @schema_option = sys.fn_replcombinehilodwordintobinary8(@schema_option_hidword, @schema_option_lodword)
        select @schema_option_str = sys.fn_varbintohexstr(@schema_option)
        select @schema_option_has_changed = 1;
    end


    /*
    ** For hierarchy we will map it to varbinarymax for 100 RTM compatibility level in character sync mode (for SSCE subscriber).
    ** Turn off the data compression for SSCE subscriber
    */
    if(@compatlevel = 100 and
	 @sync_mode <>0 and
	((@schema_option_hidword & @schema_option_hierarchyid_to_varbinarymax = 0) or
	(@schema_option_hidword & @schema_option_compression <> 0) ))
    begin
    	select @schema_option_hidword = @schema_option_hidword | @schema_option_hierarchyid_to_varbinarymax
        select @schema_option_hidword = @schema_option_hidword & ~(@schema_option_compression)

        select @schema_option = sys.fn_replcombinehilodwordintobinary8(@schema_option_hidword, @schema_option_lodword)
        select @schema_option_str = sys.fn_varbintohexstr(@schema_option)
        select @schema_option_has_changed = 1;    	
    end


    /*
    ** If a spatial type is being mapped to non spatial type on the subscriber,
    ** ensure that the spatial index schema option is disabled.
    */
    if (@schema_option_hidword & @schema_option_spatial_indexes <> 0 and
       ((@schema_option_hidword & @schema_option_spatial_to_varbinarymax <> 0) or
       (@schema_option_lodword & @schema_option_udt_to_base_types <> 0)))
    begin
        select @schema_option_hidword = @schema_option_hidword & ~(@schema_option_spatial_indexes)
        select @schema_option = sys.fn_replcombinehilodwordintobinary8(@schema_option_hidword, @schema_option_lodword)
        select @schema_option_str = sys.fn_varbintohexstr(@schema_option)
        select @schema_option_has_changed = 1;
    end

    /*
    ** If compatlevel is less than 90RTM then make sure the schema option to map down
    ** 90 datatypes to equivalent downlevel datatypes is set.
    */
    if (@compatlevel < 90 and
        (((@schema_option_lodword & (@schema_option_xml_to_ntext | @schema_option_max_to_nonmax)) <>
        (@schema_option_xml_to_ntext | @schema_option_max_to_nonmax)) or
        ((@schema_option_lodword & (@schema_option_create_schema | @schema_option_xml_indexes)) <> 0)))
    begin
        select @schema_option_lodword = @schema_option_lodword | @schema_option_xml_to_ntext | @schema_option_max_to_nonmax
        select @schema_option_lodword = @schema_option_lodword & ~(@schema_option_create_schema | @schema_option_xml_indexes)
        select @schema_option = sys.fn_replcombinehilodwordintobinary8(@schema_option_hidword, @schema_option_lodword)
        select @schema_option_str = sys.fn_varbintohexstr(@schema_option)
        select @schema_option_has_changed = 1;
    end


    /*
    ** If we changed what the user originally input, output a message telling them what
    ** the new schema_option value is.
    */
    if @schema_option_has_changed = 1
    begin
        RAISERROR (20732, 10, -1, @schema_option_str)
    end

    /*
    ** If filestream attribute is enabled, enable stream_blob_columns since
    ** this will lead to lower memory utilization during sync.
    */
    if @schema_option_hidword & @schema_option_filestream_attribute <> 0 and
       @stream_blob_columns_bit = 0
    begin
        RAISERROR (20737, 10, -1)
        select @stream_blob_columns_bit = 1
    end

    /*
    ** If scheme option contains collation or extended properties,
    ** bump up the compatibility-level
    */
    declare @xprop_schema_option int
    declare @collation_schema_option int
    select @xprop_schema_option = 0x00002000
    select @collation_schema_option = 0x00001000
    if (@schema_option_lodword & @collation_schema_option) <> 0 and @compatlevel < 40
    begin
        raiserror(21389, 10, -1, @publication)
        select @bump_to_80 = 1
    end
    if (@schema_option_lodword & @xprop_schema_option) <> 0 and @compatlevel < 40
    begin
        raiserror(21390, 10, -1, @publication)
        select @bump_to_80 = 1
    end

    /*
    ** Merge table articles does not really support destination object. It has the same value as source
    */
    if @destination_object <> @source_object
    begin
        raiserror(20638, 10, -1)
    end

    select @destination_object = @source_object

    /*
    select @row_size=sum(max_length) from sys.columns where object_id=OBJECT_ID(@qualified_name)
    if @row_size>6000
        begin
            RAISERROR (21062, 16, -1, @qualified_name)
            -- RETURN (1)
        end
    */
    IF LOWER(@vertical_partition collate SQL_Latin1_General_CP1_CS_AS) = 'false'
    begin
        select @ver_partition = 0
    end
    else
    begin
        select @ver_partition = 1
    end
    select @num_columns=count(*) from sys.columns where object_id = object_id(@qualified_name)

    -- After Yukon beta 1, this will be changed to 1024.
    if @num_columns > 246 and LOWER(@vertical_partition collate SQL_Latin1_General_CP1_CS_AS) = 'false'
    begin
        RAISERROR (20068, 16, -1, @qualified_name, 246)
        RETURN (1)
    end

    -- Colvs can only handle 246 columns so we can not allow more than 246 columns to exist on a table
    -- if column tracking is being used. The reason is that we track all the columns even when vertical
    -- partitioning is used and only some of the columns are published.
    if @num_columns > 246 and LOWER(@column_tracking collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    begin
        RAISERROR (25020, 16, -1, @qualified_name, 246)
        RETURN (1)
    end

    -- If the input param @subscriber_upload_options contradicts to the property of an already existing
    -- article for the same base table, we raise an error.
    if exists (select top 1 artid from dbo.sysmergearticles
                where objid = @objid and upload_options <> @subscriber_upload_options)
    begin
        raiserror (20053, 11, -1, 'subscriber_upload_options', @qualified_name)
        return (1)
    end

    -- If the input param @delete_tracking contradicts with the property of an already existing
    -- article for the same base table, we raise an error.
    if exists (select top 1 artid from dbo.sysmergearticles
                    where objid = @objid and delete_tracking <> @deletetracking)
    begin
        raiserror (20648, 16, -1, @qualified_name)
        return (1)
    end

    -- If the input param @stream_blob_columns contradicts with the property of an already existing
    -- article for the same base table, we raise an error.
    if exists (select top 1 artid from dbo.sysmergearticles
                    where objid = @objid and stream_blob_columns <> @stream_blob_columns_bit)
    begin
        raiserror (20053, 11, -1, 'stream_blob_columns', @qualified_name)
        return (1)
    end

    -- If the input param @compensate_for_errors contradicts to the property of an already existing
    -- article for the same base table, we raise an error.
    if exists (select top 1 artid from dbo.sysmergearticles
                    where objid = @objid and compensate_for_errors <> @compensateforerrors)
    begin
        raiserror (20053, 11, -1, 'compensate_for_errors', @qualified_name)
        return (1)
    end

    -- Subscribers below 80SP3 build 858 will ignore the @compensate_for_errors=false setting.
    -- Raise a warning.
    if @compensateforerrors = 0 and
       @compatlevel <= @REPOLEVersion_80SP3
    begin
        raiserror(20004, 10, -1, @publication, 'compensate_for_errors', '8.00.0858')
    end

    -- articles with >=246 columns can only be added to Yukon-compatible publications
    -- if the article is about to be republished, there might already be missing/excluded columns
    
    if @compatlevel <= 90    -- After Yukon beta 1, we will change this to " < 90".
    begin
        declare @cCols int -- number of columns in the table
        declare @cMissing int -- number of missing cols

        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) and LOWER(@vertical_partition collate SQL_Latin1_General_CP1_CS_AS) = 'false'
        begin
            --raiserror(21522,16,1,@article,@publication, 246)
            RAISERROR (20068, 16, -1, @qualified_name, 246)
            return (1)
        end
    end

    /*
    ** If current publication contains a non-sync subscription, all articles to be added in it
    ** has to contain a rowguidcol.
    */
    if exists (select * from dbo.sysmergesubscriptions where pubid = @pubid and sync_type = 2)
    begin
        if not exists (select * from sys.columns
            where object_id=@objid and is_rowguidcol = 1)
            begin
                raiserror(20086 , 16, -1, @publication)
                return (1)
            end
    end

    
    -- Parameter Check:  @identityrangemanagementoption.
    -- We will override the value specified in @auto_identity_range if
    -- @identityrangemanagementoption is not null
    
    if (@identityrangemanagementoption is NULL)
    begin
        
        -- @identityrangemanagementoption is null
        -- Check @auto_identity_range
        
        IF @auto_identity_range IS NULL
        begin
            
            -- user did not specify any explicit values for identity management
            
            select @identityrangemanagementoption = 'none'
        end
        else if LOWER(@auto_identity_range collate SQL_Latin1_General_CP1_CS_AS) IN ('true', 'false')
        begin
            -- map the value of @auto_identity_range to @identityrangemanagementoption
            select @identityrangemanagementoption = case
                when LOWER(@auto_identity_range collate SQL_Latin1_General_CP1_CS_AS) = N'true'
                then N'auto' else N'none' end
            -- Issue warning on deprecation of this option
            raiserror (21767, 10, 1, '@auto_identity_range', '@identityrangemanagementoption')
        end
        else
        BEGIN
            -- invalid value for @auto_identity_range
            RAISERROR (14148, 16, -1, '@auto_identity_range')
            return (1)
        END
    end
    else
    begin
        
        -- validate @identityrangemanagementoption
        
        if LOWER(@identityrangemanagementoption collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'manual', 'auto')
        BEGIN
            -- invalid value for @identityrangemanagementoption
            RAISERROR (20644, 16, -1, @identityrangemanagementoption)
            return (1)
        END
        select @identityrangemanagementoption = lower(@identityrangemanagementoption collate SQL_Latin1_General_CP1_CS_AS)
    end

    if LOWER(@identityrangemanagementoption collate SQL_Latin1_General_CP1_CS_AS) <> 'auto' and (@identity_range is not NULL or @threshold is not NULL or @pub_identity_range is not NULL)
    begin
        raiserror(21282, 16, -1)
        return (1)
    end


    if LOWER(@identityrangemanagementoption collate SQL_Latin1_General_CP1_CS_AS) = 'auto'
    begin
        /*
        ** If you want to have identity support, @range and threshold can not be NULL
        */
        if (@identity_range is NULL or @pub_identity_range is NULL)
        begin
            raiserror(21193, 16, -1)
            return (1)
        end

        if @compatlevel < 90 and @threshold is NULL
        begin
            raiserror(21193, 16, -1)
            return (1)
        end

        exec @retcode = sys.sp_MScheck_autoident_parameters
                                    @qualified_name,
                                    @pub_identity_range,
                                    @identity_range,
                                    @threshold

        if @retcode<>0 or @@error<>0
        begin
            raiserror(20707, 16, -1)
            return 1
        end

        select @ident_incr = IDENT_INCR(@qualified_name)

        select @identity_support = 1

        exec @retcode = sys.sp_MScompute_maxmin_identity @objid, @max_range output, @min_range output
        if @retcode<>0 or @@error<>0
        begin
            raiserror(20707, 16, -1)
            return 1
        end

        if @ident_incr < 0
        begin
            select @pub_identity_range = -1*@pub_identity_range
            select @identity_range = -1*@identity_range
        end
    end
    else
        select @identity_support = 0

    /*
    ** If the table contains one more columns of type bigint or sql_variant,
    ** we bump up the backward compatibility level.
    */
    if EXISTS (SELECT * FROM sys.columns c WHERE c.object_id = @sync_objid
                AND (c.system_type_id = type_id('bigint') or c.system_type_id = type_id('sql_variant'))) and @compatlevel < 40
    begin
        raiserror(21357, 10, -1, @publication)
        select    @bump_to_80 = 1
    end

    /*
    ** 7.0 subscribers do not like data type 'timestamp'
    */
    if EXISTS (select * from sys.columns where object_id=@sync_objid and system_type_id = type_id('timestamp')) and @compatlevel < 40
    begin
        raiserror(21358, 10, -1, @publication)
        select @bump_to_80 = 1
    end

    /*
    ** Validate the column tracking
    */
    if @column_tracking IS NULL OR LOWER(@column_tracking collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@column_tracking')
        RETURN (1)
    END
    if LOWER(@column_tracking collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SET @column_tracking_id = 1
    else
        SET @column_tracking_id = 0


    /*
    ** Check for partioned tables. Not supported when sync_mode is 1 (SSCE)
    */

    if @sync_mode = 1
    begin
        -- Check if the table is partitioned.
        if exists (select * from (sys.indexes as i INNER JOIN sys.partition_schemes as ps
                                                              ON (i.data_space_id = ps.data_space_id))
                        where (i.object_id = object_id(@qualified_name)) and
                            (i.index_id IN (0,1)))  -- to ensure that we are dealing with tables
        begin
            RAISERROR (22534, 16, -1)
            RETURN (1)
        end
    end

    /*
    ** Parameter Check: @allow_interactive_resolver
    */
    if LOWER(@allow_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_interactive_resolver')
            RETURN (1)
        END
    if LOWER(@allow_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        set @allow_interactive_bit = 1
    else
        set @allow_interactive_bit = 0

    /*
    ** Parameter Check: @published_in_tran_pub
    */
    if LOWER(@published_in_tran_pub collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@published_in_tran_pub')
            RETURN (1)
        END
    if LOWER(@published_in_tran_pub collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        set @published_in_tran_pub_bit = 1
    END
    else
        set @published_in_tran_pub_bit = 0

 /*
    ** Parameter Check: @fast_multicol_updateproc
    */
    if LOWER(@fast_multicol_updateproc collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@fast_multicol_updateproc')
        RETURN (1)
    END
    if LOWER(@fast_multicol_updateproc collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        set @fast_multicol_updateproc_bit = 1
    else
        set @fast_multicol_updateproc_bit = 0

    if @partition_options not in (0, 1, 2, 3)
    begin
        RAISERROR (22526, 16, -1, '@partition_options')
        RETURN (1)
    end

    if @partition_options in (1, 2, 3) and @compatlevel < 90
    begin
        raiserror(20681, 16, -1, @publication)
        return 1
    end

    execute @retcode = sys.sp_MSgetreplnick @pubid = @pubid, @replnick = @replnick output
    if (@@error <> 0) or @retcode <> 0 or @replnick IS NULL
    begin
        RAISERROR (14055, 11, -1)
        RETURN(1)
    end

    /*
    ** Validate the article resolver
    */
    if @article_resolver IS NOT NULL
    begin
        if @article_resolver = 'default' OR @article_resolver = ''
            begin
                select @article_resolver = NULL
                select @resolver_clsid = NULL
            end
        else
            begin
                EXECUTE @retcode = sys.sp_lookupcustomresolver @article_resolver, @resolver_clsid OUTPUT
                IF @retcode <> 0 or @resolver_clsid IS NULL
                BEGIN
                      RAISERROR (20020, 16, -1, @article_resolver)
                      RETURN (1)
                END
            end
    end

    /*
    ** A resolver clsid of '00000000-0000-0000-0000-000000000000' indicates a .NET Assembly resolver , ensure that the
    ** resolver_info contains the name of the class that implements the Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
    ** interface.
    */
    IF @resolver_clsid = '00000000-0000-0000-0000-000000000000'
        begin
            declare @is_dotnet_assembly bit
            declare @dotnet_assembly_name nvarchar(255)
            declare @dotnet_class_name nvarchar(255)

            EXECUTE @retcode = sys.sp_lookupcustomresolver @article_resolver, @resolver_clsid OUTPUT, @is_dotnet_assembly OUTPUT, @dotnet_assembly_name OUTPUT, @dotnet_class_name OUTPUT
            if @dotnet_assembly_name IS NULL
                begin
                    RAISERROR (21856, 16, -1, @article_resolver)
                    return (1)
                end
            if @dotnet_class_name IS NULL
                begin
                    RAISERROR (21808, 16, -1, @article_resolver)
                    return (1)
                end
            select @article_resolver = @dotnet_assembly_name
            /* If passed in resolver_info contains a .NET class name, do not override it with the default */
            if @resolver_info is null
                select @resolver_info = @dotnet_class_name
        end


    /*
    ** If article resolver is 'SP resolver', make sure that resolver_info refers to an SP or XP;
    ** Also make sure it is stored with owner qualification
    */
    if    @article_resolver = @sp_resolver
        begin
            if not exists (select * from sys.objects where object_id = object_id(@resolver_info) and ( type = 'P' or type = 'X'))
                begin
                    raiserror(21343, 16, -1, @resolver_info)
                    return (1)
                end

            select @sp_name = name, @sp_owner=SCHEMA_NAME(schema_id) from sys.objects where object_id = object_id(@resolver_info)
            select @resolver_info = QUOTENAME(@sp_owner) + '.' + QUOTENAME(@sp_name)
        end

    /* The following resolvers expect the @resolver_info to be NON NULL */
    if    @article_resolver = @sp_resolver or
        @article_resolver = @additive_resolver or
        @article_resolver = @average_resolver or
        @article_resolver = @minimum_resolver or
        @article_resolver = @maximum_resolver or
        @article_resolver = @mindate_resolver or
        @article_resolver = @maxdate_resolver or
        @article_resolver = @mergetxt_resolver or
        @article_resolver = @pricolumn_resolver
        begin
            if @resolver_info IS NULL
                begin
                    RAISERROR (21301, 16, -1, @article_resolver)
                    return (1)
                end
        end
    /*
    ** If article resolver uses column names, make sure that resolver_info refers to a valid column.
    */
    if    @article_resolver = @pricolumn_resolver or
        @article_resolver = @additive_resolver or
        @article_resolver = @average_resolver or
        @article_resolver = @minimum_resolver or
        @article_resolver = @maximum_resolver
        begin
            if not exists (select * from sys.columns where object_id = @objid and name=@resolver_info)
                begin
                    RAISERROR (21501, 16, -1, @article_resolver)
                    return (1)
                end
        end
    /*
    ** If article resolver is 'mindate/maxdate resolver', make sure that resolver_info refers to a column that is of datatype 'datetime' or smalldatetime
    */
    if  @article_resolver = @mindate_resolver or
        @article_resolver = @maxdate_resolver
    begin
        if not exists (select * from sys.columns where object_id = @objid and name=@resolver_info and (system_type_id=type_id('datetime') or system_type_id=type_id('smalldatetime')
           or system_type_id=type_id('datetime2')
           or system_type_id=type_id('date')
	    or system_type_id=type_id('time')
           )) /*need to add version condition >=100*/
        begin
            RAISERROR (21302, 16, -1, @article_resolver)
            return (1)
        end
    end

    /* The following resolvers expect the article to be column tracked - warn that the default resolver will be used */
    if    @article_resolver = @additive_resolver or
        @article_resolver = @average_resolver or
        @article_resolver = @mergetxt_resolver
    begin
        if @column_tracking_id = 0
        begin
            RAISERROR (21303, 10, -1, @article, @article_resolver)
        end
    end

    if @resolver_info IS NOT NULL and @article_resolver IS NULL
    begin
        RAISERROR (21300, 10, -1, @article)
        set @resolver_info = NULL
    end

    /*
    ** Parameter Check: logical_record_level_conflict_detection
    */
    if @column_tracking IS NULL OR LOWER(@logical_record_level_conflict_detection collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@logical_record_level_conflict_detection')
            RETURN (1)
        END
    if LOWER(@logical_record_level_conflict_detection collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SET @logical_record_level_conflict_detection_id = 1
    else
        SET @logical_record_level_conflict_detection_id = 0

    /*
    ** Parameter Check: logical_record_level_conflict_resolution
    */
    if @column_tracking IS NULL OR LOWER(@logical_record_level_conflict_resolution collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@logical_record_level_conflict_resolution')
            RETURN (1)
        END
    if LOWER(@logical_record_level_conflict_resolution collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SET @logical_record_level_conflict_resolution_id = 1
    else
        SET @logical_record_level_conflict_resolution_id = 0

    if @logical_record_level_conflict_detection_id = 1 and @logical_record_level_conflict_resolution_id = 0
    begin
        raiserror (21728, 16, -1)
        return 1
end

    if @logical_record_level_conflict_detection_id = 1 or @logical_record_level_conflict_resolution_id = 1
    begin
		-- Only supported with publications that have 90 compatibility level.
		select top 1 @pubname_lessthan90compat = name from dbo.sysmergepublications
		where backward_comp_level < 90
		and (pubid = @pubid or
			 pubid in
				(select pubid from dbo.sysmergearticles where objid = @objid))
	
		if @pubname_lessthan90compat is not null
		begin
			raiserror(21574, 16, -1, @article, @pubname_lessthan90compat)
			return 1
		end
		
		if exists (select * from dbo.sysmergepublications where pubid = @pubid and sync_mode = 1)
		begin
			raiserror(22541, 16, -1, @publication)
			return 1
		end
		
		if exists (select * from dbo.sysmergepublications where pubid = @pubid and allow_web_synchronization = 1)
		begin
			raiserror(22545, 16, -1, @publication)
			return 1
		end

		-- Cannot use Logical records and BusinessLogicResolvers at the same time.
		IF @resolver_clsid = '00000000-0000-0000-0000-000000000000'
        begin
			raiserror(20708, 16, -1)
			return 1
        end

		
		-- based on usability feeback, we should set the allow_subscriber_initiated_snapshot
		-- option to 1 rather than raise an error.
		update dbo.sysmergepublications set allow_subscriber_initiated_snapshot = 1
			where pubid = @pubid
	end
	
	/* Make sure that coltracking option matches */
    if exists (select * from dbo.sysmergearticles where objid = @objid and
            identity_support <> @identity_support)
    begin
        raiserror (21240, 16, -1, @source_object)
        return (1)
    end

    -- Do not allow the table to be published by both merge and queued tran
    if object_id('syspublications') is not NULL
    begin
        if exists (select * from syspublications p, sysarticles a where
            p.allow_queued_tran = 1 and
            p.pubid = a.pubid and
            a.objid = @objid)
        begin
            select @obj_name = object_name(@objid)
            raiserror(21266, 16, -1, @obj_name)
            return (1)
        end

        -- Do not allow the table to be published in both merge tran using automatic identity range management
        if exists (select * from  sysarticles sa, sysarticleupdates au, syspublications pub where
                sa.objid = @objid and
                au.artid = sa.artid and
                au.pubid = pub.pubid and
                au.identity_support = 1) and
           @identity_support = 1
        begin
            raiserror(20677, 16, -1, @article)
            return (1)
        end
    end
	--co-existance of uploadable merge article on queued subscription table may cause non-convergence in tran pub since queued trigger is NFR
	--allow it in case some customer rely on this already, write warning to errorlog so we can track this condition
	if (0 = @subscriber_upload_options) and (object_id('dbo.MSsubscription_articles') is not null)
	begin
		if exists(select * from dbo.MSsubscription_articles where object_id(quotename(owner) + N'.' + quotename(dest_table)) = @objid)
		begin
			select @obj_name = object_name(@objid)
			raiserror(21860, 10, -1, @obj_name, @db_name) WITH LOG
		end
	end
	
    if exists (select * from dbo.sysmergearticles where objid=@objid and pubid in(select pubid from dbo.sysmergepublications where UPPER(publisher)=UPPER(@publisher)
                and publisher_db=@publisher_db))
        select @already_published = 1

    if @already_published = 1 and LOWER(@identityrangemanagementoption collate SQL_Latin1_General_CP1_CS_AS) = 'auto'
    begin
        if @compatlevel < 40
        begin
            raiserror(21359, 10, -1, @publication)
            select @bump_to_80 = 1
        end
        if exists (select * from dbo.sysmergearticles where objid=@objid and
            ((pub_range<>@pub_identity_range) or (range <> @identity_range) or (threshold <> @threshold)))
        begin
            raiserror(21291, 16, -1)
            return (1)
        end
    end

    if 0 <> @subscriber_upload_options and @compatlevel < 90
    begin
        raiserror(21522, 16, -1, 'subscriber_upload_options', @publication)
        return 1
    end

    --Do not allow the table to be published if it contains sparse columns or sparse column_set
    if exists (select * from sys.columns where object_id = @objid and (is_sparse = 1 or is_column_set=1) )
    begin
        raiserror(20738, 16, -1, @article);
        return (1)
    end


    /*
    **    Add article to dbo.sysmergearticles and update sys.objects category bit.
    */
    begin tran
    save TRAN sp_addmergearticle

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

    select @got_merge_admin_applock = 1

    -- Parameter check @subset_filterclause
    if @subset_filterclause <> '' and @subset_filterclause is not NULL
    begin
        -- check if this is a dynamically filtered article and this is not a dynamically filtered publication
        select @article_has_dynamic_filters = 0
        exec @retcode = sys.sp_check_subset_filter
                                @qualified_name,
                                @subset_filterclause,
                                @article_has_dynamic_filters output,
                                @functions_in_subset_filter output
        if @retcode<>0 or @@ERROR<>0
        begin
            raiserror(20641, 16, -1)
            goto FAILURE
        end
        if @article_has_dynamic_filters = 1 and
           (@publication_has_dynamic_filters = 0 or @functions_in_subset_filter <> @dynamic_filters_function_list)
        begin
            if @snapshot_ready > 0 and
               ((@allow_anonymous = 1 and @compatlevel < 90) or
                 exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1)) and
               @force_reinit_subscription = 0
            begin
                raiserror(20642, 16, -1, @article, @subset_filterclause, @publication)
                goto FAILURE
            end
            select @reinit_subscriptions = 1
        end

        -- If the newly added article has dynamic filters and the publication is already using partition groups
        -- then setup the correct metadata in merge system tables  such that rerun of snapshot agent sets the partition groups metadata correctly
        if @article_has_dynamic_filters = 1 and @use_partition_groups > 0 and @functions_in_subset_filter <> @dynamic_filters_function_list
        begin
             if @use_partition_groups > 0
             begin
                 delete from dbo.MSmerge_current_partition_mappings where publication_number = @pub_number
                 if @@error <> 0 goto FAILURE
                 delete from dbo.MSmerge_past_partition_mappings where publication_number = @pub_number
                 if @@error <> 0 goto FAILURE
                 delete from dbo.MSmerge_generation_partition_mappings where publication_number = @pub_number
                 if @@error <> 0 goto FAILURE
                 update dbo.sysmergepublications set use_partition_groups = 2 where pubid = @pubid
                 if @@error <> 0 goto FAILURE
             end

             exec @retcode = sys.sp_MSdropmergedynamicsnapshotjob @publication = @publication
             if @@error <> 0 or @retcode <> 0
                 goto FAILURE

             delete from dbo.MSmerge_dynamic_snapshots where partition_id in
                 (select partition_id from dbo.MSmerge_partition_groups where publication_number = @pub_number)
             if @@error <> 0 goto FAILURE

             delete from dbo.MSdynamicsnapshotjobs where partition_id in
                 (select partition_id from dbo.MSmerge_partition_groups where publication_number = @pub_number)
             if @@error <> 0 goto FAILURE

             delete from dbo.MSmerge_partition_groups where publication_number = @pub_number
             if @@error <> 0 goto FAILURE

             update dbo.sysmergepublications
             set dynamic_filters_function_list = NULL,
                dynamic_filters = 0
                where pubid = @pubid
             if @@error <> 0 goto FAILURE

             -- Since this is called from sp_addmergearticle, make sure it doesn't raise errors since it is premature stage of the publication.
             -- The snapshot calls this with @dont_raise_error = NULL which should raise appropriate errors
             exec @retcode = sys.sp_MSset_dynamic_filter_options @publication = @publication, @dynamic_filters = @publication_has_dynamic_filters OUTPUT, @dont_raise_error = 1
             if @retcode<>0 or @@ERROR<>0 goto FAILURE
       end
    end

    /*
    ** We used to prevent an article from being added to a publication whose snapshot
    ** has been run already. Now we change this so that it is acceptable by doing reinit.
    */
    if @snapshot_ready > 0
    begin
        if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
        begin
            raiserror(21364, 16, -1, @article)
            goto FAILURE
        end
        update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid
        if @@ERROR<>0
            goto FAILURE
    end

    /*
    ** article status 5 or 6 means there is at least one new article after snapshot is ready
    ** hence all articles added after that point will be new articles as well, regardless of snapshot_ready value.
    */
    if @snapshot_ready>0 or exists (select * from dbo.sysmergearticles where pubid=@pubid and (status=5 or status=6))
    begin
        select @needs_pickup=1
    end

    if @reinit_subscriptions = 1
    begin
        exec @retcode = sys.sp_MSreinitmergepublication
                                @publication = @publication,
                                @upload_first = @automatic_reinitialization_policy
        if @retcode<>0 or @@ERROR<>0 return 1
    end

    -- if because this article is dynamically filtered the publication is going to change from being
    -- a static to a dynamic publication, we need to delete all entries in sysmergeschemachange.
    if @publication_has_dynamic_filters = 0 and @article_has_dynamic_filters = 1
    begin
        declare @SCHEMA_TYPE_DROPARTICLE int

        select @SCHEMA_TYPE_DROPARTICLE = 28
        -- don't delete dropmergearticle related schema changes.
        delete from dbo.sysmergeschemachange where pubid = @pubid and schematype not in (@SCHEMA_TYPE_DROPARTICLE)
        if @reinit_subscriptions = 1
            select @needs_pickup = 0
    end

    -- Acquire sch-M lock up-front on the published object
    exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
    if @qualname is null
        goto FAILURE

    exec %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
    --exec %%Object(MultiName = @qualname).LockExclusiveMatchID(ID = @objid)
    if @@error <> 0
        goto FAILURE

    select @artid = artid,
           @preserve_rowguidcol= preserve_rowguidcol
       from dbo.sysmergearticles where objid = @objid

    -- If that article is already in another publication, we reuse its preserve_rowguidcol.
    -- If the article is added the first time, we set preserve_rowguidcol depending
    -- on whether there already is a rowguidcol.
    if @preserve_rowguidcol is null
    begin
        if ObjectProperty(object_id(@qualified_name), 'tablehasrowguidcol') = 1
        begin
            set @preserve_rowguidcol= 1
        end
        else
        begin
            set @preserve_rowguidcol= 0
        end
    end

    if @snapshot_ready > 0
    begin
        /*
        ** Add the guid column to the user table if needed, cause snapshot_ready>0 would imply
        ** this article has got a rowguid column. No need to add index, triggers, or procedures
        ** as snapshot run will take care of those.
        */
        execute @retcode = sys.sp_MSaddguidcolumn @source_owner, @source_object
        if @@ERROR <> 0 OR    @retcode <> 0  -- NOTE: new change
            goto FAILURE
        execute @retcode = sys.sp_MSaddguidindex @publication, @source_owner, @source_object
        if @@ERROR <> 0 OR @retcode <> 0
            goto FAILURE
    end

    
    -- Need to change sys.columns status before generating sync procs/custom procs
    -- because the status will be used to decide whether or not call set identity insert. Enable
    -- NFR property if identityrangemanagementoption is MANUAL or AUTO. If
    -- identityrangemanagementoption is NONE then we will not explicity enable NFR.
    
    -- This is to change identity column to 'not for replication' if not having been so already
    IF @identityrangemanagementoption in ('auto', 'manual' )
    begin
        select @colname = name
        from sys.columns
        where object_id = @objid and
            is_identity = 1 and -- is identity
            ColumnProperty(object_id, name, 'IsIdNotForRepl') = 0 -- No 'not for repl' property
        if @colname is not null
        begin
            -- Mark 'not for repl'
            EXEC %%ColumnEx(ObjectID = @objid, Name = @colname).SetIdentityNotForRepl(Value = 1)
            IF @@ERROR <> 0
                GOTO FAILURE
        end
    end

    select @statusid = 1  -- default status is inactive

    if @artid is NULL
    begin
        set @artid = newid()
        if @@ERROR <> 0
            goto FAILURE
        execute @retcode = sys.sp_MSgentablenickname @
 
Last revision 2008RTM
See also

  sp_MScheckatpublisher (Procedure)
sp_MScreate_dist_tables (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MSmerge_altertable (Procedure)
sp_MSmerge_create_sub_table (Procedure)
sp_MSset_dynamic_filter_options (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