Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addarticle

  No additional text.


Syntax

create procedure sys.sp_MSrepl_addarticle
(
    @publication				sysname,
    @article					sysname,
    @source_table				nvarchar (517),
    @destination_table			sysname,
    @vertical_partition			nchar(5),
    @type						sysname,
    @filter						nvarchar (386),
    @sync_object				nvarchar (386),
    @ins_cmd					nvarchar (255),
    @del_cmd					nvarchar (255),
    @upd_cmd					nvarchar (255),
    @creation_script			nvarchar (255),
    @description				nvarchar (255),
    @pre_creation_cmd			nvarchar(10),
    @filter_clause				ntext,
    @schema_option				varbinary(8),
    @destination_owner			sysname,
    @status						tinyint,
    @source_owner				sysname,
    @sync_object_owner			sysname,
    @filter_owner				sysname,
    @source_object				sysname,
    @artid						int OUTPUT,
    @identityrangemanagementoption nvarchar(10),
    @auto_identity_range		nvarchar(5),
    @pub_identity_range			bigint,
    @identity_range				bigint,
    @threshold					int,
    @force_invalidate_snapshot	bit,
    @use_default_datatypes		bit,
    @publisher					sysname,
    @publisher_type				sysname,
    @fire_triggers_on_snapshot  nvarchar(5)
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @loc_publisher sysname
            ,@bak_source sysname
            ,@num_columns int
            ,@accessid smallint
            ,@db sysname
            ,@filterid int
            ,@object sysname
            ,@owner sysname
            ,@pubid int
            ,@publish_bit smallint
            ,@retcode int
            ,@site sysname
            ,@syncid int
            ,@tabid int
            ,@objid int
            ,@typeid smallint
            ,@pkkey sysname
            ,@i int
            ,@indid int
            ,@precmdid int
            ,@object_type nchar(2)
            ,@push tinyint
            ,@dbname sysname
            ,@cmd nvarchar(255)
            ,@fHasPk int
            ,@no_sync tinyint
            ,@immediate_sync bit
            ,@is_filter_in_use int
            ,@distributor sysname
            ,@distribdb sysname
            ,@distproc nvarchar (1000)
            ,@sync_method tinyint
            ,@allow_initialize_from_backup bit
            ,@colname sysname
            ,@fire_triggers_on_snapshot_bit bit
            ,@distributortimestamp datetime
            ,@instance_id int
            ,@ccs_appendix smallint
            ,@pub_is_immediate_sync bit
            ,@proctag nvarchar(12)
            ,@publisher_dbms sysname
            ,@publisher_version sysname

    DECLARE  @autogen_sync_procs_id int
            ,@custom_proc_name nvarchar(255)
            --,@guid varbinary(16)
            ,@allow_sync_tran bit
            ,@repl_freq int
            ,@allow_queued_tran bit
            ,@allow_dts bit
            ,@merge_pub_object_bit  int
            ,@valid_ins_cmd nvarchar(255)
            ,@valid_upd_cmd nvarchar(255)
            ,@valid_del_cmd nvarchar(255)
            ,@MSrepl_tran_version_datatype sysname
            ,@colid int
            ,@backward_comp_level int
            ,@schema_option_lodword int
            ,@OracleColumnsExist bit -- Flag to indicate if #OracleColumns table was create
            ,@publisher_id int       -- needed for Heterogeneous publications
            ,@article_view nvarchar(255)
            ,@columns binary(128)
            ,@publishedcolumns binary(128)
            ,@table_id int
            ,@fpublished int
            ,@publisher_db sysname
            ,@is_p2p bit
            ,@max_range bigint
            ,@filter_proc_type sysname
            ,@is_enabled_for_het_sub bit
            ,@tablehasidentity bit
            ,@hr_admin sysname
            ,@applock_result int
            ,@usecustomprocs bit
            ,@constraint_name sysname
            ,@typetext nvarchar(max)
            ,@fcolnullable int
            ,@xtype int
           ,@xprec int
            ,@viewname nvarchar(255)
            ,@issqlpublisher bit
            ,@p2porid int
            ,@tmp_cmd nvarchar(255)

    
    -- constants
    
            ,@customprocmaxlen int
            ,@customprocoffset int
            -- Option Bitfield Constants
            ,@OPT_ENABLED_FOR_P2P int
            ,@internal sysname
            ,@OPT_ENABLED_FOR_HET_SUB int
            ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
    
    -- Initialize
    
    SELECT @OracleColumnsExist = 0
            ,@backward_comp_level = 10 -- default to sphinx
            ,@merge_pub_object_bit    = 128
            ,@push = 0
            ,@dbname = DB_NAME()
            ,@allow_initialize_from_backup = 0
            ,@customprocmaxlen = 128
            ,@issqlpublisher = case when (@publisher_type = N'MSSQLSERVER') then 1 else 0 end
            
            --for ccs publications this offset will be adjusted by 11
            --since we want to append '_msrepl_ccs' at the end
            
            ,@customprocoffset = 17
            ,@ccs_appendix = 11
            ,@is_p2p = 0
            ,@OPT_ENABLED_FOR_P2P = 0x1
            ,@fire_triggers_on_snapshot_bit = 0
            ,@OPT_ENABLED_FOR_HET_SUB = 0x4
            ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8

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

    -- Check to see if database is activated for publication
    IF sys.fn_MSrepl_istranpublished(@dbname,1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    -- Get a real server name if needed
    SELECT @loc_publisher = CASE
                                WHEN @publisher IS NOT NULL THEN @publisher
                                ELSE publishingservername()
                            END

    -- Get distributor info
    EXECUTE @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
                                                        @distribdb  = @distribdb OUTPUT,
                                                        @publisher = @loc_publisher,
                                                        @publisher_id = @publisher_id OUTPUT

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

    /*
    ** Padding out the specified schema option to the left
    */
    select @schema_option = fn_replprepadbinary8(@schema_option)
            ,@publisher_db = case when (@issqlpublisher = 1) then @dbname else @distribdb end

    IF @issqlpublisher = 0
    BEGIN
        IF @publisher_id IS NULL
        BEGIN
            RAISERROR(21600, 16, -1, @publisher)
            RETURN (1)
        END

        IF @publisher_db IS NULL
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END
    END
    /*
    ** Parameter Check: @article.
    ** The @article name cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    exec @retcode = sys.sp_MSreplcheck_name @article, '@article', 'sp_MSrepl_addarticle'
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    if LOWER(@article) = N'all'
    BEGIN
        RAISERROR (14032, 16, -1, '@article')
        RETURN (1)
    END
    /*
    ** Parameter Check: @publication.
    ** The @publication name cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, N'@publication', N'sp_MSrepl_addarticle')
        RETURN (1)
    END

    EXECUTE @retcode = sys.sp_validname @publication
    IF @retcode <> 0
        RETURN (1)
    /*
    ** Parameter Check: @destination_owner.
    ** The @destination_owner must conform to the rules
    ** for identifiers.
    */
    if @destination_owner is not null
    BEGIN
        EXECUTE @retcode = sys.sp_validname @destination_owner
        IF @retcode <> 0
            RETURN (1)
    END
    
    -- Parameter Check: @source_table.
    -- Check to see that the @source_table is local, that it conforms
    -- to the rules for identifiers, and that it is a table, and not
    -- a view or another database object.
    -- Parameter Check: @source_object
    -- Required for HREPL
    
    IF @issqlpublisher = 1
    BEGIN
        -- SQL Publisher
        IF @source_table IS NULL
        BEGIN
            if @source_object is NOT NULL
                select @source_table = @source_object
            else
            begin
                RAISERROR (14043, 16, -1, N'@source_table', N'sp_MSrepl_addarticle')
                RETURN (1)
            end
        END
		else
		begin
       		 -- The @source_table parameter(s) have been deprecated and should no longer be used. See the 'sp_addarticle' documentation for more information.
		        RAISERROR(21827, 16, -1, '@source_table', 'sp_addarticle')
               RETURN (1)
		end

        IF @source_owner is NULL -- 6.5 users only
        begin
            -- LIKE N'%.%.%' doesn't guarantee that the object name
            -- is a 3-part name as . can be embedded in a quoted identifier.

            IF @source_table LIKE N'%.%.%' AND ISNULL(PARSENAME(@source_table, 3), @dbname) <> @dbname
            BEGIN
                RAISERROR (14004, 16, -1, @source_table)
                RETURN (1)
            END
        end

        -- For 7.0 users, @source_owner is not nullable.

        select @bak_source = @source_table

        IF @source_owner is not NULL
        begin
            select @source_table = QUOTENAME(@source_owner) + N'.' + QUOTENAME(@source_table)
            select @tabid = object_id(@source_table)
            IF @destination_table IS NULL
                SELECT @destination_table = @bak_source
        end
        ELSE
        begin
            -- Make @source_table qualifed.
            select @tabid = object_id(QUOTENAME(@source_table))
            if @tabid is not null
            begin
                exec @retcode = sys.sp_MSget_qualified_name @tabid, @source_table output
                if @retcode <> 0 or @@error <> 0
                    return 1
                IF @destination_table IS NULL
                -- Set destination_table if not provided or default by now.
                -- If @source_table is qualified (6.x behavior) only use table name for destination name.
                    SELECT @destination_table = PARSENAME(@source_table, 1)
            end
        end
        
        -- Some table property local var initializations
        
        select @tablehasidentity = case when (OBJECTPROPERTY(@tabid, N'tablehasidentity') = 1) then 1 else 0 end

    END -- SQL Publisher
    ELSE
    BEGIN
        -- HREPL Publisher
        IF @source_table IS NOT NULL
        BEGIN
            -- For HREPL publications, restrict to NULL (not supported)
            RAISERROR (21632, 16, -1, N'@source_table', N'NULL')
            RETURN (1)
        END
        IF @source_object IS NULL
        BEGIN
            -- For HREPL publications, source object required
            RAISERROR (14043, 16, -1, N'@source_object', N'sp_MSrepl_addarticle')
            RETURN (1)
        END
        SELECT @source_table = @source_object

        -- Get the pubid and its properties
        EXEC @retcode = sys.sp_IHgetPublicationInfo @publisher = @publisher,
                                                    @publication   = @publication,
                                                    @publication_id   = @pubid                    OUTPUT,
                                                    @autogen_sync_procs_id = @autogen_sync_procs_id    OUTPUT,
                                                    @sync_method   = @sync_method              OUTPUT,
                                                    @allow_sync_tran  = @allow_sync_tran          OUTPUT,
                                                    @allow_queued_tran  = @allow_queued_tran        OUTPUT,
                                                    @allow_dts    = @allow_dts                OUTPUT,
                                                    @repl_freq    = @repl_freq                OUTPUT,
                                                    @hr_admin    = @hr_admin     OUTPUT
        IF @@ERROR <> 0 AND @retcode <> 0
            RETURN (1)

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

        -- Reject any objects that share the admin schema
        IF @source_owner = @hr_admin
        BEGIN
            RAISERROR (20802, 16, -1, @source_owner)
            RETURN (1)
        END

        select @bak_source = @source_table

        -- Check the table for heterogeneous publishers
        EXEC @retcode = sys.sp_IHCheckSourceTable
                                    @publisher_type  = @publisher_type,
                                    @publisher   = @publisher,
                                    @source_table  = @source_table,
                                    @source_owner  = @source_owner,
                                    @publication_type = @repl_freq

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

        -- Set object type to a User table
        SELECT @object_type = N'U'

        IF @destination_table is NULL
            SELECT @destination_table = @source_table
        
        -- Some table property local var initializations
        
        select @tablehasidentity = 0
    END -- HREPL Publisher

    /*
    ** Parameter Check: @type
    ** If the article is added as a 'schema-bound 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 = case when (@type IS NULL) then N'logbased' else lower(@type) end

    IF @issqlpublisher = 0
    BEGIN
        IF @type <> 'logbased'
        BEGIN
            raiserror(21601, 16, 1)
            return (1)
        END
    END

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

    /*
    ** Vertical partitioning can only be enabled for logbased article
    */
    select @vertical_partition = lower(@vertical_partition)
    if @vertical_partition = N'true' and
       @type not in (N'logbased',
                            N'logbased manualfilter',
                            N'logbased manualview',
                            N'logbased manualboth',
                            N'indexed view logbased',
                            N'indexed view logbased manualfilter',
                            N'indexed view logbased manualview',
                            N'indexed view logbased manualboth')
    begin
        raiserror(22548, 11, -1)
        return (1)
    end

    /*
    ** If the article is published as an IV logbased article, we'd better make
    ** sure that the view is schema bound and it has a clustered index.
    ** Conversely, a schema-bound view should never be published as a regular
    ** table logbased article.
    */
    IF @issqlpublisher = 1
    BEGIN
        if @type in (N'indexed view logbased',
                     N'indexed view logbased manualfilter',
                     N'indexed view logbased manualview',
                     N'indexed view logbased manualboth') and
            (isnull(objectproperty(@tabid, N'IsSchemaBound'),0) <> 1 or
             not exists (select * from sysindexes where id = @tabid) or
             isnull(objectproperty(@tabid, N'IsView'),0) = 0)
        begin
            raiserror (21278, 11, -1, @source_table)
            return (1)
        end

        if @type in (N'view schema only',
                     N'logbased',
                     N'logbased manualfilter',
                     N'logbased manualview',
                     N'logbased manualboth') and
            objectproperty(@tabid, N'IsSchemaBound') = 1
        begin
            raiserror (21275, 11, -1, @source_table)
            return (1)
        end

        IF @use_default_datatypes != 1
        BEGIN
            RAISERROR (21606, 16, -1, '@use_default_datatypes', '1')
            RETURN (1)
        END
    END

    -- Virtual subscriptions of all the articles will be deactivated.
    EXECUTE @retcode  = sys.sp_MSreinit_article
                                    @publication    = @publication,
                                    @need_new_snapshot   = 1,
                                    @force_invalidate_snapshot = @force_invalidate_snapshot,
                                    @check_only     = 1,
                                    @publisher     = @publisher,
                                    @publisher_type    = @publisher_type

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

    -- 
    -- HREPL specific parameter checks
    -- 
    IF @issqlpublisher = 0
    BEGIN
        -- Parameter Check: @pub_identity_range
        -- For HREPL publications, restrict to NULL (not supported)

        IF @pub_identity_range IS NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, '@pub_identity_range', 'NULL')
            RETURN (1)
        END


        -- Parameter Check: @identity_range
        -- For HREPL publications, restrict to NULL (not supported)

        IF @identity_range IS NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, '@identity_range', 'NULL')
            RETURN (1)
        END

        -- Parameter Check: @threshold
        -- For HREPL publications, restrict to NULL (not supported)

        IF @threshold IS NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, '@threshold', 'NULL')
            RETURN (1)
        END

        -- Parameter Check: @sync_object
        -- For HREPL publications, restrict to NULL (not supported)

        IF @sync_object IS NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, '@sync_object', 'NULL')
            RETURN (1)
        END

        -- Parameter Check: @sync_object_owner
        -- For HREPL publications, restrict to NULL (not supported)

        IF @sync_object_owner IS NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, '@sync_object_owner', 'NULL')
            RETURN (1)
        END

        -- Parameter Check: @filter_owner
        -- For HREPL publications, restrict to NULL

        IF @filter_owner IS NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, '@filter_owner', 'NULL')
            RETURN (1)
        END
    END -- HREPL checks

    /*
    ** Parameter Check: @status
    ** If NULL, default to 16
    */
    IF @status IS NULL
        SET @status = 16

    IF @issqlpublisher = 1
    BEGIN
        -- SQL Publisher

        BEGIN TRANSACTION
        SAVE TRANSACTION sp_addarticle

        -- Get applock to co-ordinate with snapshot agent. Basically,
        -- we want to prevent a new article from being added while a
        -- snapshot is being generated.
        EXEC @applock_result = sys.sp_getapplock @Resource = @publication,
                @LockMode = N'Exclusive',
                @LockOwner = N'Transaction',
                @LockTimeout = 0,
                @DbPrincipal = N'db_owner'

        IF @applock_result < 0
        BEGIN
            RAISERROR(21854, 16, -1, @publication)
            GOTO UNDO
        END

        -- Lock source table
        IF (@tabid IS NOT NULL)
        BEGIN
            EXEC %%Object(MultiName = @source_table).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
            IF @@ERROR <> 0
                SELECT @tabid = NULL
        END

        IF @tabid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, @source_table)
            GOTO UNDO
        END

        /*
        **  Get the type of the @source_table
        */
        SELECT @object_type = type
            FROM sys.objects
            WHERE object_id = @tabid

        -- Encrypted objects are not publishable for replication
        IF @type IN (N'proc exec',
                     N'serializable proc exec',
                     N'proc schema only',
                     N'indexed view schema only',
                     N'indexed view logbased',
                     N'indexed view logbased manualfilter',
                     N'indexed view logbased manualview',
                     N'indexed view logbased manualboth',
                     N'view schema only',
                     N'func schema only')
        BEGIN
            IF EXISTS (SELECT * FROM syscomments
                        WHERE id = @tabid
                          AND encrypted = 1)
            BEGIN
                RAISERROR(21004, 16, -1, @source_table)
                GOTO UNDO
            END
        END
    END -- SQL Publisher

    -- if PeerToPeer replication is turned on for this publication level then
    -- we need to make a few other checks on options to be restricted for the pub
    IF EXISTS(select *
                    from syspublications
                    where name = @publication
                        and (options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P)
    BEGIN
        SELECT @is_p2p = 1

        /*
        Hetero is not supported
        */
        IF @issqlpublisher = 0
        BEGIN
            -- Peer-To-Peer publications only support an '@publisher_type' parameter value of 'MSSQLSERVER'.
            RAISERROR (21679, 16, -1, '@publisher_type', '''MSSQLSERVER''')
            GOTO UNDO
        END

        /*
        Disallow any type of dest owner/object name change...
        */
        IF NOT ISNULL(@destination_table, N'') = N''
            AND @destination_table != object_name(@tabid)
        BEGIN
            -- Peer-To-Peer publications do not destination owner name change. Please change the '@destination_table' parameter value.
            RAISERROR(20646, 16, -1, 'destination object name change',  '@destination_table')
            GOTO UNDO
        END

        IF NOT ISNULL(@destination_owner, N'') = N''
                AND @destination_owner != schema_name(OBJECTPROPERTY(@tabid,N'SchemaId'))
        BEGIN
            -- Peer-To-Peer publications do not destination owner name change. Please change the '@destination_owner' parameter value.
            RAISERROR(20646, 16, -1, 'destination owner name change',  '@destination_owner')
            GOTO UNDO
        END

        /*
        Ensure that we are not attempting to create an invalid
        Peer-To-Peer publication/subscription configuration...
        */
        EXEC @retcode = sys.sp_MSdetectinvalidaddarticle @publication = @publication,
                                                                            @dest_table = @destination_table,
                                                                            @dest_owner = @destination_owner
        IF @@error <> 0 OR @retcode <> 0
            GOTO UNDO

        /*
        Disallow any type of filtering...
        */
        IF ISNULL(LOWER(@vertical_partition), N'false') = N'true'
        BEGIN
            -- Peer-To-Peer publications only support an '@vertical_partition' parameter value of 'false'.
            RAISERROR (21679, 16, -1, '@vertical_partition', '''false''')
            GOTO UNDO
        END

        IF @type IN (N'logbased manualfilter',
                N'logbased manualview',
                N'logbased manualboth',
                N'indexed view logbased manualfilter',
                N'indexed view logbased manualview',
                N'indexed view logbased manualboth')
        BEGIN
            -- Peer-To-Peer publications do not support filtering. Please change the '@type' parameter value.
            RAISERROR(20646, 16, -1, 'filtering',  '@type')
            GOTO UNDO
        END

        IF NOT ISNULL(@filter, N'') = N''
        BEGIN
            -- Peer-To-Peer publications do not support filtering. Please change the '@filter' parameter value.
            RAISERROR(20646, 16, -1, 'filtering',  '@filter')
            GOTO UNDO
        END

        IF NOT ISNULL(@filter_owner, N'') = N''
        BEGIN
            -- Peer-To-Peer publications do not support filtering. Please change the '@filter_owner' parameter value.
            RAISERROR(20646, 16, -1, 'filtering',  '@filter_owner')
            GOTO UNDO
        END

        IF NOT ISNULL(@filter_clause, N'') like N''
        BEGIN
            -- Peer-To-Peer publications do not support filtering. Please change the '@filter_clause' parameter value.
            RAISERROR(20646, 16, -1, 'filtering',  '@filter_clause')
            GOTO UNDO
        END

        IF NOT ISNULL(@sync_object_owner, N'') = N''
        BEGIN
            -- Peer-To-Peer publications do not support filtering. Please change the '@sync_object_owner' parameter value.
            RAISERROR(20646, 16, -1, 'filtering',  '@sync_object_owner')
            GOTO UNDO
        END

        IF NOT ISNULL(@sync_object, N'') = N''
        BEGIN
            -- Peer-To-Peer publications do not support filtering. Please change the '@sync_object' parameter value.
            RAISERROR(20646, 16, -1, 'filtering',  '@sync_object')
            GOTO UNDO
        END
        
        -- Only allow manual identity ranges
        
        IF ISNULL(LOWER(@auto_identity_range), N'false') = N'true'
        BEGIN
            -- Peer-To-Peer publications only support an '@auto_identity_range' parameter value of 'false'.
            RAISERROR (21679, 16, -1, '@auto_identity_range', '''false''')
            GOTO UNDO
        END

        IF NOT ISNULL(LOWER(@pub_identity_range ), N'') = N''
        BEGIN
            -- Peer-To-Peer publications only support an '@pub_identity_range' parameter value of NULL.
            RAISERROR (21679, 16, -1, '@pub_identity_range', 'NULL')
            GOTO UNDO
        END

        IF NOT ISNULL(LOWER(@identity_range ), N'') = N''
        BEGIN
            -- Peer-To-Peer publications only support an '@identity_range' parameter value of NULL.
            RAISERROR (21679, 16, -1, '@identity_range', 'NULL')
            GOTO UNDO
        END

        IF NOT ISNULL(LOWER(@threshold ), N'') = N''
        BEGIN
            -- Peer-To-Peer publications only support an '@threshold' parameter value of NULL.
            RAISERROR (21679, 16, -1, '@threshold', 'NULL')
            GOTO UNDO
        END

        /*
        Invalid Columns for P2P
        Timestamp is allowed if not replicated - schema_option bit 8 should be set
        */
        IF ISNULL(OBJECTPROPERTY(@tabid, N'TableHasTimestamp'), 0) = 1
            AND (sys.fn_replgetbinary8lodword(@schema_option) & 0x8) = 0
        BEGIN
            -- Peer-To-Peer publications do not support replicating timestamp columns as varbinary(8). Adding an article with this option or adding/altering a table to include a timestamp column as varbinary(8) is not allowed.
            RAISERROR (21734, 16, -1)
            GOTO UNDO
        END

        /*
        Invalid Command Types for P2P
        */

        IF @ins_cmd IS NOT NULL
            AND LEFT(UPPER(LTRIM(@ins_cmd)), 3) IN (N'SQL')
        BEGIN
            -- Peer-To-Peer publications do not support 'SQL' command type. Please change the '@ins_cmd' parameter value.
            RAISERROR(20646, 16, -1, '''SQL'' Command Type',  '@ins_cmd')
            GOTO UNDO
        END

        IF @del_cmd IS NOT NULL
            AND LEFT(UPPER(LTRIM(@del_cmd)), 3) IN (N'SQL')
        BEGIN
            -- Peer-To-Peer publications do not support 'SQL' command type. Please change the '@del_cmd' parameter value.
            RAISERROR(20646, 16, -1, '''SQL'' command type',  '@del_cmd')
            GOTO UNDO
        END

        IF @upd_cmd IS NOT NULL
            AND LEFT(UPPER(LTRIM(@upd_cmd)), 3) IN (N'SQL')
        BEGIN
            -- Peer-To-Peer publications do not support 'SQL' command type. Please change the '@upd_cmd' parameter value.
            RAISERROR(20646, 16, -1, '''SQL'' Command Type',  '@upd_cmd')
            GOTO UNDO
        END

        /*
        Enable parameterized statements now if it is not already enabled...
        */
        IF (@status & 16) != 16
        BEGIN
            DECLARE @status_str nvarchar(50)

            SELECT @status = @status | 16
            SELECT @status_str = CONVERT(nvarchar(50), @status)

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

    -- Parameter check: @fire_triggers_on_snapshot
    SELECT @fire_triggers_on_snapshot = LOWER(@fire_triggers_on_snapshot)
    IF @fire_triggers_on_snapshot IS NULL or @fire_triggers_on_snapshot not in (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@fire_triggers_on_snapshot')
        GOTO UNDO
    END
    SELECT @fire_triggers_on_snapshot_bit = case when (@fire_triggers_on_snapshot = N'true') then 1 else 0 end

    -- if the publication has been enabled for heterogeneous subscriptions
    -- some additional restriction apply to the articles
    IF EXISTS(select *
                    from syspublications
                    where name = @publication
                        and (options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
    BEGIN
        select @is_enabled_for_het_sub = 1

        -- @auto_identity_range cannot be true
        IF ISNULL(LOWER(@auto_identity_range), N'false') = N'true'
        BEGIN
            -- Publications enabled for heterogeneous subscriptions do not support %s. Please change the '@auto_identity_range' parameter value.
            RAISERROR(20649, 16, -1, 'identity range management',  '@auto_identity_range')
            GOTO UNDO
        END

        IF NOT @pub_identity_range IS NULL
        BEGIN
            -- Publications enabled for heterogeneous subscriptions do not support %s. Please change the '@pub_identity_range' parameter value.
            RAISERROR(20649, 16, -1, 'identity range management',  '@pub_identity_range')
            GOTO UNDO
        END

        IF NOT @identity_range IS NULL
        BEGIN
            -- Publications enabled for heterogeneous subscriptions do not support %s. Please change the '@identity_range' parameter value.
            RAISERROR(20649, 16, -1, 'identity range management',  '@identity_range')
            GOTO UNDO
        END

        IF NOT @threshold IS NULL
        BEGIN
            -- Publications enabled for heterogeneous subscriptions do not support %s. Please change the '@threshold' parameter value.
            RAISERROR(20649, 16, -1, 'identity range management',  '@threshold')
            GOTO UNDO
        END

        IF NOT ISNULL(LOWER(@identityrangemanagementoption), N'none') = N'none'
        BEGIN
            -- Publications enabled for heterogeneous subscriptions do not support %s. Please change the '@identityrangemanagementoption' parameter value.
   RAISERROR(20649, 16, -1, 'identity range management',  '@identityrangemanagementoption')
            GOTO UNDO
        END

        IF NOT ISNULL(@fire_triggers_on_snapshot, N'false') = N'false'
        BEGIN
            -- Publications enabled for heterogeneous subscriptions do not support %s. Please change the '@fire_triggers_on_snapshot' parameter value.
            RAISERROR(20649, 16, -1, 'firing user triggers on a replicated table when the snapshot is applied',  '@fire_triggers_on_snapshot')
            GOTO UNDO
        END

    END

    -- Don't allow the same object to be published twice in a publication that
    -- allows is enabled for autonosyncs (Restriction temporarily lifted)
    --    IF EXISTS (SELECT *
    --                 FROM dbo.sysextendedarticlesview sa
    --                INNER JOIN dbo.syspublications sp
    --                   ON sa.pubid = sp.pubid
    --                WHERE sp.allow_initialize_from_backup = 1
    --                  AND sa.objid = @tabid
    --                  AND sp.name = @publication)
    --    BEGIN
    --        RAISERROR(18798, 16, -1)
    --        GOTO UNDO
    --    END

    -- Default HREPL publications to generate primary object
    IF @issqlpublisher = 0
    BEGIN
        IF @schema_option IS NULL
        BEGIN
            SELECT @schema_option = case when (@is_enabled_for_het_sub = 1)
                then 0x0000000000004041 else 0x00000000000050D3 end
        END
        ELSE
        BEGIN
            DECLARE @valid_schema_options int
                        ,@schema_option_val int

            -- Restrict HREPL schema options
            SELECT @valid_schema_options = 0xD0D3,
                        @schema_option_val = fn_replgetbinary8lodword(@schema_option)

            IF (@schema_option_val & ~@valid_schema_options) <> 0
            BEGIN
                RAISERROR (21635, 16, -1)
                GOTO UNDO
            END
        END
    END


/*
	--a tiny optimization, if there is no table based iv nor 'proc exec', no need to check for this.
	--otherwise, check for 21843 condition
	
	if exists(select 1 from sysarticles where objectproperty(objid, 'IsView') = 1 or objectproperty(objid, 'IsProcedure') = 1)
	begin
		if sys.fn_ftableivprocexec(@tabid) = 1
		begin
			raiserror (21843, 16, 1, @article)
			GOTO UNDO
		end
	end
*/
    -- at this point, we've done all the common parameter checks.
    -- If this is a procedure execution article, branch to the proc execution publishing
    -- routine; or if this is a schema only procedure or view article, branch to the
    -- schema only article publishing routine; otherwise continue processing as if it were a table

    -- Note: We need to obtain the pubid and the associated
    -- publication properties for a SQL Server
    -- article prior to calling sp_MSaddexecarticle
    -- or sp_MSaddschemaarticle because the code that gets
    -- executed when these procedure are done relies
    -- on their presence.
    IF @issqlpublisher = 1
    BEGIN
        SELECT @pubid = pubid, @autogen_sync_procs_id = autogen_sync_procs, @sync_method = sync_method,
            @allow_sync_tran = allow_sync_tran,
            @allow_queued_tran = allow_queued_tran,
            @allow_dts  = allow_dts,
            @repl_freq = repl_freq,
            @allow_initialize_from_backup = allow_initialize_from_backup
        FROM syspublications where name = @publication

        IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            GOTO UNDO
        END
        
        --for ccs publications this offset will be adjusted by 11
        --since we want to append '_msrepl_ccs' at the end
        
        if (@sync_method in (3, 4))
        begin
            select @customprocoffset = @customprocoffset + @ccs_appendix
        end

        -- make sure that if the destination_owner was not specified we set it to
        -- the source objects schema_name. this is the same as UI default. Be sure
        -- to only set it if we know that the publication is not in character mode
        -- bcp. This is because non-sql subscriptions expect an empty schema_name :
        -- 		1 = character
        -- 		4 = concurrent character
        -- sward  Use of character mode bcp to determine whether a publication is intended
        --        for heterogeneous subscribers is obsolete.  The publication property
        --        @enabled_for_het_sub now identifies publications designed for use with
        --        heterogeneous subscriptions.
        -- sward  temporarily, allow @sync_method alone to prevent @destination_owner from defaulting to @source_owner
        --        Note that this check will eliminate publications enabled for heterogeneous subscribers as well
        --        since these publications must have @sync_method IN (1, 4).  It will additionally eliminate publications
        --        that have not been enabled for heterogeneous subscriptions, but do have @sync_method In (1, 4), the old behavior.	    		
        IF (@destination_owner IS NULL
                OR RTRIM(@destination_owner) = N'')
                AND @sync_method NOT IN (1, 4)
        -- AND @is_enabled_for_het_sub = 0
        BEGIN
            SELECT @destination_owner = schema_name(OBJECTPROPERTY(@tabid, N'SchemaId'))
        END
    END -- SQL publisher

    IF @issqlpublisher = 1
        and ((@object_type in (N'P', N'PC') AND @type <> N'proc schema only')
        or  @type IN (N'proc schema only', N'view schema only', N'func schema only', N'indexed view schema only', N'aggregate schema only', N'synonym schema only'))
    BEGIN
        
        -- Processing non table articles
        -- Only SQL Publications
        
        IF (@object_type in (N'P', N'PC') AND @type <> N'proc schema only')
        begin

            IF @schema_option IS NULL
                SELECT @schema_option = 0x0000000000000001

            EXECUTE @retcode = sys.sp_MSaddexecarticle @publication,
                @article,
                @source_table,
                @destination_table,
                @type,
                @creation_script,
                @description,
                @pre_creation_cmd,
                @schema_option,
                @destination_owner,
                @artid OUTPUT
        end
        else
        begin
            -- Note: a transaction is started inside sp_MSaddschemaarticle
            IF @schema_option IS NULL
            BEGIN
                SELECT @schema_option = 0x0000000000000001
            END

            IF @type = N'proc schema only'
            BEGIN
                SELECT @typeid = 0x20
            END
            ELSE IF @type = N'view schema only'
            BEGIN
                SELECT @typeid = 0x40
            END
            ELSE IF @type = N'aggregate schema only'
            BEGIN
                SELECT @typeid = 0x60
                select @backward_comp_level = 90
            END
            ELSE IF @type = N'synonym schema only'
            BEGIN
                SELECT @typeid = 0xA0
                select @backward_comp_level = 90
            END
            ELSE IF @type = N'func schema only'
            BEGIN
                SELECT @typeid = 0x80
                select @backward_comp_level = 40 -- UDF not available in sphinx
            END
            ELSE IF @type = N'indexed view schema only'
            BEGIN
                SELECT @typeid = 0x40
                select @backward_comp_level = 40 -- SchemaBinding not available in sphinx
            END

            EXECUTE @retcode = sys.sp_MSaddschemaarticle
                @publication = @publication,
                @article = @article,
                @source_object = @source_table,
    @destination_object = @destination_table,
                @type = @typeid,
                @creation_script = @creation_script,
                @description = @description,
                @pre_creation_cmd = @pre_creation_cmd,
                @schema_option = @schema_option,
                @destination_owner = @destination_owner,
                @status = @status,
                @artid = @artid OUTPUT

        end
        IF @retcode <> 0 or @@error <> 0
            goto UNDO
    END -- Handle non table articles
    ELSE
    BEGIN -- Block to handle table articles
        
        -- Handle table articles
        

        -- Make sure that the table name specified is a table and not a view.
        IF @issqlpublisher = 1
        BEGIN
            IF NOT EXISTS (SELECT * FROM sys.objects
                WHERE object_id = @tabid
                AND type = N'U')
                AND NOT EXISTS ( SELECT * FROM sys.objects so, sysindexes si
                WHERE so.object_id = @tabid
                AND so.type = N'V'
                AND si.id = so.object_id )
            BEGIN
                RAISERROR (14028, 16, -1)
                GOTO UNDO
            END
        END
        /*
        ** Parameter Check: @vertical_partition
        ** Check to make sure that the vertical partition is either TRUE or FALSE.
        */
        SELECT @vertical_partition = LOWER(@vertical_partition)
        IF @vertical_partition NOT IN (N'true', N'false')
        BEGIN
            RAISERROR (14029, 16, -1)
            GOTO UNDO
        END
        /*
        ** Parameter Check: @filter
        ** Make sure that the filter is a valid stored procedure.
        */
        IF @filter IS NULL
        BEGIN
            select @filterid = 0
        END
        ELSE
        BEGIN
            -- @filter was specified
            IF @issqlpublisher = 0
            BEGIN
                raiserror(21632, 16, -1, '@filter', 'NULL')
                return (1)
            END

            IF @filter_owner IS NULL
            BEGIN
                select @object = PARSENAME( @filter, 1 )
                        ,@owner  = PARSENAME( @filter, 2 )
                        ,@db     = PARSENAME( @filter, 3 )
                        ,@site   = PARSENAME( @filter, 4 )

                if @object IS NULL
                    GOTO UNDO
            END
            ELSE
            BEGIN
                select @filter = QUOTENAME(@filter_owner) + N'.' + QUOTENAME(@filter)
            END
            /*
            ** Get the id of the @filter
            */
            select @filterid = object_id,
                    @filter_proc_type = type
            from sys.objects
            where object_id = OBJECT_ID(@filter)

            IF @filterid IS NULL
            BEGIN
                -- @filter does not exist in the current database.
                RAISERROR (14027, 11, -1, @filter)
                GOTO UNDO
            END
            ELSE IF @filter_proc_type != N'RF'
            BEGIN
                -- Stored procedures for replication are the only objects that can be used as a filter.
                RAISERROR (14049, 11, -1)
                GOTO UNDO
            END

            EXEC @is_filter_in_use = sys.sp_MSdoesfilterhaveparent @filterid
            if( @is_filter_in_use <> 0 )
            BEGIN
                RAISERROR( 21009, 11, -1 )
                GOTO UNDO
            END
        END -- @filter was specified

        -- (All the following restrictions are lifted
        -- If publication allows automated nosync subscription setup, disallow
        -- i) Vertical partitioning
        -- ii) Horizontal partitioning
        -- iii) A different destination owner name
        -- iv) A different destination object name
        -- v) IV -> Table article
        -- vi) Table with a timestamp column
        IF @issqlpublisher = 1 AND @allow_initialize_from_backup = 1
        BEGIN
--            -- i)
--            IF @vertical_partition = N'true'
--            BEGIN
--                RAISERROR (18792, 16, -1)
--                GOTO UNDO
--            END

--            -- ii)
--            IF (@filter IS NOT NULL AND RTRIM(@filter) <> N'') OR
--               (@filter_clause IS NOT NULL) OR
--               @type in (
--                N'logbased manualfilter',
--                N'logbased manualview',
--                N'logbased manualboth')
--            BEGIN
--                RAISERROR (18792, 16, -1)
--                GOTO UNDO
--            END

--            -- iii)
--            -- Note that we set @destination_owner to source object owner
--            -- if it is unspecified
            IF @destination_owner IS NULL OR RTRIM(@destination_owner) = N''
            BEGIN
                SELECT @destination_owner = schema_name(OBJECTPROPERTY(@tabid,N'SchemaId'))
            END
--            IF @destination_owner <> schema_name(OBJECTPROPERTY(@tabid,N'SchemaId'))
--            BEGIN
--                RAISERROR (18791, 16, -1)
--                GOTO UNDO
--            END

--            -- iv)
--            -- Note that we set @destination_table to source table name if it
--            -- is unspecified
            IF @destination_table IS NULL OR RTRIM(@destination_table) = N''
            BEGIN
                SELECT @destination_table = OBJECT_NAME(@tabid)
            END
--            IF @destination_table <> OBJECT_NAME(@tabid)
--            BEGIN
--                RAISERROR (18791, 16, -1)
--                GOTO UNDO
--            END

--            -- v)
--            IF @type IN (
--                N'indexed view logbased',
--                N'indexed view logbased manualfilter',
--                N'indexed view logbased manualview',
--                N'indexed view logbased manualboth')
--            BEGIN
--                RAISERROR (18794, 16, -1)
--                GOTO UNDO
--            END

--            -- vi)
--            IF ISNULL(OBJECTPROPERTY(@tabid, 'TableHasTimestamp'), 0) = 1
--            BEGIN
--                RAISERROR (21409, 16, -1)
--                GOTO UNDO
--            END

        END -- allow autonosync validation

        IF @issqlpublisher = 1
        BEGIN
            -- SQL Publisher
            -- Only allow table and index view for dts publications
            -- HetPubs only allow logbased, so DTS is always ok
            if @allow_dts <> 0 and @type not in (
                N'logbased',
                N'logbased manualfilter',
                N'logbased manualview',
                N'logbased manualboth',
                N'indexed view logbased',
                N'indexed view logbased manualfilter',
                N'indexed view logbased manualview',
                N'indexed view logbased manualboth')
            begin
                raiserror(20611, 16, -1)
                GOTO UNDO
            end

            -- can't do fancy type stuff with MVs!

            ELSE IF (@allow_sync_tran <> 0
                    OR @allow_queued_tran <> 0)
                    AND EXISTS ( select * from sys.objects
                        where object_id = OBJECT_ID(@source_table)
                        and type = N'V' )
            BEGIN
                RAISERROR(14059, 16, -1)
                GOTO UNDO
            END
            -- Parameter Check:  @article, @publication.
            -- Check if the article already exists in this publication.
            IF EXISTS (SELECT *
                         FROM sysextendedarticlesview
                        WHERE pubid = @pubid
                          AND name = @article)
            BEGIN
                RAISERROR (14030, 16, -1, @article, @publication)
                GOTO UNDO
            END
      
            -- Validate @type
            
            IF LOWER(@type) NOT IN
                                        (N'logbased',
                                         N'logbased manualfilter',
                                         N'logbased manualview',
                                         N'logbased manualboth',
                                         N'indexed view logbased',
                                         N'indexed view logbased manualfilter',
                                         N'indexed view logbased manualview',
                                         N'indexed view logbased manualboth',
                                         N'proc schema only',
                                         N'view schema only')
            BEGIN
                RAISERROR (14023, 16, -1)
                GOTO UNDO
            END
        END -- SQL Publisher
        ELSE
        BEGIN
            -- HREPL publisher
            -- Parameter Check:  @article, @publication.
            -- Check if the article already exists in this publication.
            IF EXISTS
            (
                SELECT *
                FROM dbo.IHextendedArticleView
                WHERE publication_id = @pubid
                    AND article = @article
            )
            BEGIN
                RAISERROR (14030, 16, -1, @article, @publication)
                RETURN (1)
            END
            
            -- @type validated already for hetpubs
            -- Nothing to do here
            
        END -- HREPL publisher

        
        -- parameter check: @status
        -- bits 8, 16, 64 can be set directly by user
        -- Other bits from 1 ~ 64 are used but cannot be set here.
        -- Bit 64 can only be set for publication that allows DTS.
        -- Bit 32 is set internally according to whether or not timestamp is in
        -- the partition if we want to replicate it as varbinary or not.
        -- If Bit 32 is set then timestamp will scripted on subscriber as timestamp
        -- and the values will not be replicated (each replica will have its own
        -- timestamp values). This is the default for updating subscriptions.
        -- For legacy we still support scripting of timestamp as varbinary as default
        
        IF (@status & ~ 88 ) <> 0
        BEGIN
            RAISERROR( 21061, 16, -1, @status, @article )
            GOTO UNDO
        END
        else if ((@status & 64) = 64 and @allow_dts = 0)
        begin
            raiserror(20590, 16, -1)
            GOTO UNDO
        end

        /*
        ** Set the typeid.  The default type is logbased.  Anything else is
        ** currently undefined (reserved for future use).
        **
        **      @typeid     type
        **      =======     ========
        **          1     logbased
        **          3     logbased manualfilter
        **          5     logbased manualview
        **          7     logbased manualboth
        **          8     proc exec              (valid in sys.sp_MSaddexecarticle)
        **          24    serializable proc exec (valid in sys.sp_MSaddexecarticle)
        **          32    proc schema only       (valid in sys.sp_MSaddschemaarticle)
        **          64    view schema only       (valid in sys.sp_MSaddschemaarticle)
        **         128    func schema only       (valid in sys.sp_MSaddschemaarticle)
        **       Note that for the following article types, the 256 bit is not really persisted
        **         257    indexed view logbased
        **         259    indexed view logbased manualfilter
        **         261    indexed view logbased manualview
        **         263    indexed view logbased manualboth
        **         320    indexed view schema only (valid in sys.sp_MSaddschemaarticle)
        */

        SELECT @typeid = case
          when (LOWER(@type) = N'logbased' OR LOWER(@type) = N'indexed view logbased') then 1
                                when (LOWER(@type) = N'logbased manualfilter' OR LOWER(@type) = N'indexed view logbased manualfilter') then 3
                                when (LOWER(@type) = N'logbased manualview' OR LOWER(@type) = N'indexed view logbased manualview') then 5
                                when (LOWER(@type) = N'logbased manualboth' OR LOWER(@type) = N'indexed view logbased manualboth') then 7
                                end
        /*
        ** 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) NOT IN (N'none', N'drop', N'delete', N'truncate')
        BEGIN
            RAISERROR (14061, 16, -1)
            GOTO UNDO
        END

        /*
        ** Determine the integer value for the pre_creation_cmd.
        */
        SELECT @precmdid = case
                                        when (LOWER(@pre_creation_cmd) = N'none') then 0
                                        when (LOWER(@pre_creation_cmd) = N'drop') then 1
                                        when (LOWER(@pre_creation_cmd) = N'delete') then 2
                                        when (LOWER(@pre_creation_cmd) = N'truncate') then 3
                                     end

        IF @issqlpublisher = 1
        BEGIN
            -- SQL Publisher
            -- process @sync_object
            IF @sync_object IS NULL
            BEGIN
                select @syncid = @tabid
            END
            ELSE
            BEGIN
                IF @sync_object_owner is NULL  -- 6.5 only
                BEGIN
                    /*
                    ** Parameter Check: @sync_object.
                    ** Check to see that the sync_object is local and that it
                    ** conforms to the rules for identifiers.
                    */
                    select @object = PARSENAME( @sync_object, 1 )
                            ,@owner  = PARSENAME(  @sync_object, 2 )
                            ,@db     = PARSENAME(  @sync_object, 3 )
                            ,@site   = PARSENAME(  @sync_object, 4 )

                    if @object IS NULL
                        GOTO UNDO
                    -- LIKE N'%.%.%' doesn't guarantee that the object name
                    -- is a 3-part name as . can be embedded in a quoted identifier.

                    IF @sync_object LIKE N'%.%.%' AND ISNULL(@db, @dbname) <> @dbname
                    BEGIN
                        RAISERROR (14004, 16, -1, @sync_object)
                        GOTO UNDO
                    END
                END -- end of 65 processing
                else
                BEGIN
                    -- for sphinx, @sync_object_owner can not be null
                    select @sync_object = QUOTENAME(@sync_object_owner) + N'.' + QUOTENAME(@sync_object)
                END

                /*
                **  Get the id of the @sync_object
                */
                SELECT @syncid = OBJECT_ID(@sync_object)
                IF @syncid IS NULL
                BEGIN
                    RAISERROR (14027, 11, -1, @sync_object)
                    GOTO UNDO
                END
                /*
                ** Make sure the sync object specified is a table or a view.
                */
                IF NOT EXISTS (SELECT * FROM sys.objects
                                WHERE object_id = OBJECT_ID(@sync_object)
                                    AND type in ('U','V'))
                BEGIN
                    RAISERROR (14031, 16, -1)
                    GOTO UNDO
                END
            END -- @sync_object is not null

            /*
            ** If the publication is log-based, or allows updating subscribers
            ** make sure there is a primary key on the source table.
            ** or a UCI on the view.
            ** For HetPubs this is done in IHchecktables
            ** NOTE!  sprok in SPSUP.SQL
            */
            IF EXISTS (SELECT * FROM syspublications
                WHERE pubid = @pubid AND
                        (repl_freq = 0 OR allow_sync_tran = 1 OR @allow_queued_tran = 1))
            BEGIN
                EXEC @fHasPk = sys.sp_MSreplsup_table_has_pk @tabid
                IF @fHasPk = 0
                BEGIN
                    IF EXISTS ( select * from sys.objects
                        where object_id = OBJECT_ID(@source_table)
                            and type = 'U' )
                    BEGIN
                        RAISERROR (14088, 16, -1, @source_table)
                    END
                    ELSE
                    BEGIN
                        RAISERROR( 14089, 16, -1, @source_table)
                    END
                    GOTO UNDO
                END

                if sys.fn_fcomputedpkimprecise(object_id(@source_table)) = 1
                BEGIN
                    RAISERROR (21730, 16, -1, @source_table)
                    GOTO UNDO
                END
            END
            /*
            ** Parameter Check:  @creation_script and @schema_option
            ** @schema_option cannot be null
            ** If @schema_option is 0, there have to be @creation_script defined.
            */
            IF @schema_option IS NULL
            BEGIN
                -- If publication is enabled for heterogeneous subscriptions
                -- set default schema options to replicate primary key,
                -- and clustered and nonclustered indexes.
                IF @is_enabled_for_het_sub = 1
                BEGIN
                    SELECT @schema_option  = 0x0000000000004071
                END
                ELSE
                BEGIN
                    -- Snapshot publication, no custom proc. generation
                    -- We need insert proc for snapshot publications for DTS.
                    -- Do not generate user triggers by default (0x00100 - user trigger flag)
                    SELECT @schema_option  = case when (@repl_freq = 1 and @allow_dts = 0)
                                                                then 0x0000000000030071 else 0x0000000000030073 end
                    if (@allow_queued_tran = 1 or @allow_sync_tran = 1)
                        select @schema_option = convert(binary(8),(convert(bigint, @schema_option) | 0x80))
                    if (@is_p2p = 1) -- p2p does not allow replicating timestamp as binary(8)
                        select @schema_option = convert(binary(8),(convert(bigint, @schema_option) | 0x8))
                END
            END
            ELSE
            BEGIN
                -- Raid 504438
                -- For immediate updating subscribers backward compatibility with SQLDMO generated scripting
                -- If 0x80 is not set - we will set it automatically
                if ((@allow_sync_tran = 1) and ((fn_replgetbinary8lodword(@schema_option) & 0x80) = 0))
                begin
                    select @schema_option = convert(binary(8), (convert(bigint,@schema_option) | 0x80))
                end
            END
            
            -- Parameter Check: @schema_option
            -- If bit 0x2 is set, this cannot be an article for a snapshot publication
            -- For Updating subscribers, DRI_Primary Key option has to be set
            
            IF ((CONVERT(INT, @schema_option) & 0x2) <> 0) AND (@repl_freq = 1)
            BEGIN
                RAISERROR (21143, 16, -1)
                GOTO UNDO
            END
            else if ((@allow_queued_tran = 1 or @allow_sync_tran = 1) and
                    ((fn_replgetbinary8lodword(@schema_option) & 0x80) = 0))
            BEGIN
                RAISERROR (21394, 16, 1)
                GOTO UNDO
            END
            
            -- For a transactional read-only publication, a timestamp column
            -- cannot be replicated as a timestamp column if it is part of
            -- the primary key as that will violate the equivalency of
            -- primary key values that transactional replication is based
            -- on. As such, we will implicitly disable the 0x08 KeepTimestamp
            -- schema option if the primary key of the base table contains
            -- a timestamp column
            
            if @repl_freq = 0 and ((fn_replgetbinary8lodword(@schema_option) & 0x8) !=0) and exists (
                select c.name
                  from sys.columns c
            inner join sys.index_columns ic
                    on c.column_id = ic.column_id
                   and c.object_id = ic.object_id
                   and ic.is_included_column = 0
            inner join sys.indexes i
                    on i.index_id = ic.index_id
                   and i.object_id = ic.object_id
                   and i.is_primary_key = 1
                 where c.object_id = @tabid
                   and c.system_type_id = 189)
            begin
                set @schema_option = fn_replprepadbinary8(cast(@schema_option as bigint) & ~8)
            end

            
            -- Check if schema option to keep timestamps is set - if yes then
            -- status bit 32 will always be set internally
            -- For queued updating subscriptions set this by default
            
            if (@allow_queued_tran =1) or ((fn_replgetbinary8lodword(@schema_option) & 0x8) != 0)
                select @status = (@status | 32) | 8
            
            -- Set schema_option bit 0x8 if status bit 32 is set
            -- for timestamp columns. This will enable scripting of timestamp
            -- as timestamp on subscriber
            
            if (@status & 32 = 32) and ((fn_replgetbinary8lodword(@schema_option) & 0x8) = 0)
                select @schema_option = convert(binary(8), (convert(bigint, @schema_option) | 0x8))
            
            -- For updating subscribers
            -- PK cannot contain Timestamp column
            
            if ((@allow_sync_tran = 1 or @allow_queued_tran = 1)
                and (OBJECTPROPERTY ( @tabid , 'TableHasTimestamp' ) = 1))
            begin
                
                -- get the index id for the source table
                
                select @indid = indid
                from sysindexes
                where id = @tabid and (status & 2048) != 0
                if (@indid is null)
                begin
                    raiserror (14088, 16, -1, @source_table)
                    goto UNDO
                end
                
                -- iterate through each column in the PK
                
                select @i = 1
                while (@i <= 16)
                begin
                    
                    -- get the column name in the table for this key index
                    
                    select @pkkey = index_col(@source_table, @indid, @i)
                    if @pkkey is NULL
                        break
                    
                    -- is this column timestamp
                    
                    if exists (select * from sys.columns
                    where object_id = @tabid
                        and name = @pkkey
                        and TYPE_NAME(system_type_id) = N'timestamp')
                    begin
                        raiserror(21460, 16, 1, @source_table, @pkkey)
                        goto UNDO
                    end
                    
                    -- fetch next key
      
                    select @i = @i + 1
                end
            end -- updating publication with timestamp

        END -- SQL Publisher

        
        -- custom proc naming
        
        -- for the PeerToPeer case we must tack on an int val to the
        -- procs if they are auto-generated custom procedures. this
        -- will prevent us from overwriting procs from other nodes
        
        if (@allow_dts = 0 or @is_p2p = 1)
        begin
            -- retrieve a guid and convert it to an int using checksu, to be used in certain naming cases
            select @proctag = replace(convert(nvarchar(12), checksum(newid())), N'-', N'0')

            
            -- non DTS article
            -- custom proc name is based on destination table name
            -- If destination owner is specified - use it as part of the custom proc name
            
            if (@destination_owner is null)
            begin
                
                -- custom proc based on destination table
                -- check the length and truncate as necessary
                -- The call format is of the form XXXXX [sp_MSxxx_custom_proc_name]
                -- We need to adjust for 17 characters(defined as @customprocoffset) in this format
                
                -- in the p2p case we always want to use the GUID based name to
                -- ensure each immediate publisher has its own auto procs to use
                if len(@destination_table) < (@customprocmaxlen - @customprocoffset - 1)
                        and @is_p2p = 0
                begin
                    
                    -- we can have a readable proc name
                    
                    select @custom_proc_name = @destination_table
                end
                else
                begin
                    
                    -- Use a GUID(32 chars) based name
                    
                    select @custom_proc_name = substring(@destination_table, 1, (@customprocmaxlen - @customprocoffset - len(@proctag))) + @proctag
                e
 
Last revision 2008RTM
See also

  sp_addarticle (Procedure)
sp_MSdrop_repltran (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