Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_addmergepublication

  No additional text.


Syntax

create procedure sys.sp_addmergepublication (
    @publication            sysname,                  /* Publication name */
    @description            nvarchar(255)= NULL,      /* Publication description */
    @retention              int          = 14,        /* Retention period of 14 days */
    @sync_mode              nvarchar(10) = 'native',  /* (bcp)native, (bcp)character */
    @allow_push             nvarchar(5)  = 'true',    /* Pulication allows push subscriptions */
    @allow_pull             nvarchar(5)  = 'true',    /* Pulication allows pull subscriptions*/
    @allow_anonymous        nvarchar(5)  = 'true',   /* Pulication allows anonymous subscriptions */
    @enabled_for_internet   nvarchar(5)  = 'false',   /* Pulication is enabled for internet */
    @centralized_conflicts  nvarchar(5)  = null,      /* deprecated, use @conflict_logging instead */
    @dynamic_filters        nvarchar(5)  = 'false',   /* Will publication be filtered on dynamic clause? */
    @snapshot_in_defaultfolder nvarchar(5) = 'true',  /* Will keep a copy of the snapshot files to the default location if an alternate folder is specified */
    @alt_snapshot_folder    nvarchar(255) = NULL,     /* Alternate folder for putting the snapshot file for this publication */
    @pre_snapshot_script    nvarchar(255) = NULL,     /* Pre snapshot commands */
    @post_snapshot_script   nvarchar(255) = NULL,     /* Post snapshot commands */
    @compress_snapshot      nvarchar(5) = 'false',    /* Snapshot compression */
    @ftp_address            sysname = NULL,           /* Post 7.0 FTP Properties */
    @ftp_port               int = 21,                 /* Post 7.0 FTP Properties */
    @ftp_subdirectory       nvarchar(255) = 'ftp',    /* Post 7.0 FTP Properties */
    @ftp_login              sysname = N'anonymous',   /* Post 7.0 FTP Properties */
    @ftp_password           sysname = NULL,           /* Post 7.0 FTP Properties */
    @conflict_retention     int = 14,                 /* Conflict retention period */
    @keep_partition_changes nvarchar(5) = 'true',    /* Optimized Partition Updates/Deletes */
    @allow_subscription_copy  nvarchar(5) = 'false',  /* Allow the subscription to be copied */
    @allow_synctoalternate    nvarchar(5) = 'false',  /* Allow the subscription to be synchronize to alternate partners */
    @validate_subscriber_info nvarchar(500) = NULL,   /* Should we validate that subscriber is using right params? */
    @add_to_active_directory  nvarchar(5) = 'false',
    @max_concurrent_merge     int = 0,                /* value of 0 means no such limit exists */
    @max_concurrent_dynamic_snapshots int = 0,        /* Maximum number of concurrent dynamic snapshot sessions */
    @use_partition_groups        nvarchar(5) = NULL,  /* NULL = Let system decide, otherwise manually set to 'true' or 'false' */
    @publication_compatibility_level nvarchar(6) = '80SP3',  -- backward compatibility level, default=Sphinx
    @replicate_ddl            int = NULL,           -- for now we only use value 1 for alter table add/drop columns and other alter table sub-statements.
                                                    -- we may need more bits for "create table, drop table, " and others
    @allow_subscriber_initiated_snapshot nvarchar(5) = 'false',
    @allow_web_synchronization      nvarchar(5) = 'false',
    @web_synchronization_url        nvarchar(500) = null, -- Default value of InternetURL for subscriptions
    @allow_partition_realignment    nvarchar(5) = 'true',        -- true = send deletes for rows that leave partition, false=do not send deletes for rows that leave partition.
    @retention_period_unit          nvarchar(10) = 'day',    -- possible values are 'day', 'days', 'dd', 'year', 'years', 'yy', 'yyyy', 'month', 'months', 'mm', 'week', 'weeks', 'wk', 'hour', 'hours', 'hh', 'minute', 'minutes', 'mi'
    @generation_leveling_threshold	int	= 1000,			-- determines approximately how many row changes at most will be put in one generation. 0 = no limit.
    @automatic_reinitialization_policy        bit = 0, 	-- 0=no upload first on automatic reinit; 1=upload first
    @conflict_logging       		nvarchar(15)  = NULL   -- 'publisher', 'subscriber', or 'both'. Default is 'publisher' for <90 compat level and 'both' for 90 compat level
    )
    as

    set nocount on

    /*
    ** Declarations.
    */

    declare @retcode                    int            /* return code value for procedure execution */
    declare @push                        tinyint        /* subscription type is push */
    declare @statid                        tinyint        /* status id based on @status */
    declare @sync_modeid                tinyint        /* sync mode id based on @sync_mode */
    declare @global                        tinyint        /* subscriber type of loop-back subscription */
    declare @db_name                    sysname        /* database name */
    declare @replnick                    binary(6)    /* replica nickname */
    declare @tranpublish_bit            smallint    /* online publish bit (flag) in sysdatabases */
    declare @mergepublish_bit            smallint    /* merge publish bit (flag) in sysdatabases */
    declare @found                        int            /* flag indicating if publication is found */
    declare @pubid                        uniqueidentifier    /* Publication identifier */
    declare @allow_push_id                bit
    declare @allow_pull_id                bit
    declare @allow_anonymous_id            bit
    declare @dynamic_filters_id            bit
    declare @allow_subscription_copy_id bit
    declare @allow_synctoalternate_id    bit
    declare @allow_web_synchronization_id    bit
    declare @enabled_for_internet_id    bit
    declare @conflogging                int
    declare @priority                    real
    declare @automatic                    tinyint
    declare @false                        bit
    declare @true                        bit
    declare @distributor                sysname
    declare @distproc                    nvarchar(300)
    declare @distribdb                    sysname
    declare @distpubid                    int
    declare @full                        int
    declare @snapshot_in_defaultfolder_bit        bit
    declare @compress_snapshot_bit        bit
    declare @keep_before_values_int        int
    declare @enc_ftp_password            nvarchar(524)
    declare @ad_guidname                sysname
    declare @schemaversion                int
    declare @schemaguid                    uniqueidentifier
    declare @schematype                    int
    declare @schematext                    nvarchar(2000)
    declare @artid                        uniqueidentifier
    declare @distservername                sysname
    declare @use_partition_groups_id    smallint
    declare @allow_subscriber_initiated_snapshot_id bit
    declare @allow_partition_realignment_id         bit
    declare @REPOLEVersion_70RTM        int
    declare @REPOLEVersion_70SP1        int
    declare @REPOLEVersion_70SP2        int
    declare @REPOLEVersion_80RTM        int
    declare @REPOLEVersion_90            int
    declare @REPOLEVersion_100            int
    declare @REPOLEVersion_70RTM_string        nvarchar(5)
    declare @REPOLEVersion_70SP1_string        nvarchar(5)
    declare @REPOLEVersion_70SP2_string        nvarchar(5)
    declare @REPOLEVersion_70SP3_string        nvarchar(5)
    declare @REPOLEVersion_70SP4_string        nvarchar(5)
    declare @REPOLEVersion_80RTM_string        nvarchar(5)
    declare @REPOLEVersion_80SP1_string        nvarchar(5)
    declare @REPOLEVersion_80SP2_string        nvarchar(5)
    declare @REPOLEVersion_80SP3_string        nvarchar(5)
    declare @REPOLEVersion_90RTM_string        nvarchar(5)
    declare @REPOLEVersion_100RTM_string        nvarchar(6)
    declare @pubcompat_level_id int
    declare @retention_period_unit_id        tinyint
    declare @working_directory      nvarchar(255)
    			,@publishingservername sysname
    declare @got_merge_admin_applock bit

    select @got_merge_admin_applock = 0


    if (sys.fn_MSrepl_editionid() in (22, 40)) -- SQL Express or SQL_Web
    begin
        raiserror(21106, 16, -1) --This edition of SQL Server does not support publications.
        return (1)
    end

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

    select @mergepublish_bit    = 4
    select @tranpublish_bit        = 1
    select @priority            = 100.0
    select @automatic            = 1        /* Const: synchronization type 'automatic' */
    select @true                = 1
    select @false                = 0
    select @full                = 0        /* Const: publication type 'full' */

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

    set @REPOLEVersion_100= 100

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


    /*
    ** Set the status to Active (1)
    */
    select @statid        = 1
    select @global        = 1
    select @push        = 0
    select @db_name        = DB_NAME()
    select @ad_guidname = NULL
    			,@publishingservername = publishingservername()

    /*
    ** Security Check
    */

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

    -- Parameter check: @publication_compatibility_level: Allowed range.
    if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70RTM_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_70RTM
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP1_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_70SP1
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP2_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_70SP2
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP3_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_70SP2
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP4_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_70SP2
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80RTM_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_80RTM
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP1_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_80RTM
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP2_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_80RTM
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP3_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_80RTM
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_90RTM_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_90
    end
    else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_100RTM_string
    begin
        select @pubcompat_level_id = @REPOLEVersion_100
    end
    else
    begin
        raiserror(21402, 16, -1, '@publication_compatibility_level')
        return (1)
    end

    if (@pubcompat_level_id  not in
            (
                @REPOLEVersion_70RTM,
                 @REPOLEVersion_70SP1,
                 @REPOLEVersion_70SP2,
                @REPOLEVersion_80RTM,
                @REPOLEVersion_90,
                @REPOLEVersion_100
            )
        )
    begin
        raiserror(21402, 16, -1, '@publication_compatibility_level')
        return (1)
    end



    -- Parameter check: @publication_compatibility_level: A Katmai publication db can
    -- only have Katmai compatible publications for the new data types.
    -- We will remove this constraint during the next phase of NewDateTime improvement
    -- yiche 12.7.2006
    if @REPOLEVersion_100 = @pubcompat_level_id and
       exists (select * from dbo.sysmergepublications where
                   backward_comp_level < @REPOLEVersion_100)
    begin
        raiserror(21527, 16, -1, @publication, @db_name)
        return (1)
    end
    -- Parameter check: @publication_compatibility_level: A Yukon publication db can either have
    -- downlevel or Yukon compatible publications, but not both.
    else if @REPOLEVersion_90 = @pubcompat_level_id and
       exists (select * from dbo.sysmergepublications where
                   backward_comp_level < @REPOLEVersion_90)
    begin
        raiserror(21527, 16, -1, @publication, @db_name)
        return (1)
    end
    else if @REPOLEVersion_90 > @pubcompat_level_id and
       exists (select * from dbo.sysmergepublications where
                   backward_comp_level >= @REPOLEVersion_90)
    begin
        raiserror(21528, 16, -1, @publication, @db_name)
        return (1)
    end

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers,
    ** and must not be the keyword 'all'.
    */
    exec @retcode = sys.sp_MSreplcheck_name @publication, '@publication', 'sp_addmergepublication'
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    if LOWER (@publication) = 'all'
        begin
            raiserror (14034, 16, -1)
            return (1)
        end

    if @max_concurrent_merge<0
        begin
            raiserror(21402, 16, -1, '@max_concurrent_merge')
            return (1)
        end

    /*
    ** Parameter Check: @retention.
    */

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

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

    if @retention_period_unit_id <> 0 and @pubcompat_level_id < @REPOLEVersion_90
    begin
        raiserror(22528, 16, -1, @publication)
        return 1
    end

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

    /*
    ** Parameter Check: @conflict_retention.
    */

    if @conflict_retention is not NULL and @conflict_retention<0
        begin
            raiserror(20050, 16, -1, 0)
            return(1)
        end

    /*
    ** if it is NULL, use the default value of 14 days.
    */
    if @conflict_retention is NULL
        select @conflict_retention = 14

    /*
    ** Parameter Check: @sync_mode.
    ** Make sure that the sync_mode is one of the following:
    **
    **    id    sync_mode
    **    ==    ==========
    **     0    (bcp)native
    **     1    (bcp)character
    */

    if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS)='portable' select @sync_mode='character'

    if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) is NULL OR LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('bcp native', 'bcp character', 'native', 'character')
        begin
            raiserror (20076, 16, -1)
            return (1)
        end

    if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) = 'native' or
       LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS)='bcp native'
        select @sync_modeid = 0
    else
        select @sync_modeid = 1

    if @retention_period_unit_id <> 0 and @sync_modeid = 1
    begin
        raiserror(22555, 16, -1, @publication)
        return 1
    end

    /*
    ** Parameter Check:     @allow_push.
    */

    if @allow_push IS NULL OR LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_push')
            RETURN (1)
        END

    if LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        select @allow_push_id = 1
    else
        select @allow_push_id = 0

    /*
    ** Parameter Check:     @allow_pull.
    */

    if @allow_pull IS NULL OR LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_pull')
            RETURN (1)
        END
    if LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        select @allow_pull_id = 1
    else
        select @allow_pull_id = 0

    /*
    ** Parameter Check:     @allow_anonymous.
    */

    if @allow_anonymous IS NULL OR LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_anonymous')
            RETURN (1)
        END
    if LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        select @allow_anonymous_id = 1
    else
        select @allow_anonymous_id = 0


    if @generation_leveling_threshold is null
		set @generation_leveling_threshold=0
		
    if (@generation_leveling_threshold < 0)
    begin
		raiserror(21119, 16, -1, @generation_leveling_threshold, '@generation_leveling_threshold')
		return 1
	end

    /*
    ** Parameter Check:     @enabled_for_internet.
    */

    IF @enabled_for_internet IS NULL OR LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@enabled_for_internet')
            RETURN (1)
        END

    IF LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @enabled_for_internet_id = 1
    ELSE
        SELECT @enabled_for_internet_id = 0

    /*
    ** Parameter Check: @conflict_logging, @centralized_conflicts.
    */

	-- Pick defaults based on pubcompat_level
	if @conflict_logging is null
	begin
		if @pubcompat_level_id < @REPOLEVersion_90
			set @conflict_logging = 'publisher'
		else
			set @conflict_logging = 'both'
	end

    if @centralized_conflicts is not null
    begin
        raiserror (21767, 10, 1, '@centralized_conflicts', '@conflict_logging')

        set @conflogging=
                case LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS)
                    when 'true' then 1
                    when 'false' then 0
                    else null
                end

        if @conflogging is null
        begin
            RAISERROR (14148, 16, -1, '@centralized_conflicts')
            RETURN (1)
        end
    end
    else if @conflict_logging is not null
    begin
        set @conflogging=
                case LOWER(@conflict_logging collate SQL_Latin1_General_CP1_CS_AS)
                    when 'publisher' then 1
                    when 'subscriber' then 0
                    when 'both' then 2
                    else null
                end

        if @conflogging is null
        begin
            RAISERROR (14103, 16, -1, '@conflict_logging')
            RETURN (1)
        end
    end
    else
    begin
        RAISERROR (14103, 16, -1, '@conflict_logging')
        RETURN (1)
    end

    if @conflogging = 2 and
       @pubcompat_level_id < @REPOLEVersion_90
    begin
        RAISERROR (20705, 16, -1, @publication)
        RETURN (1)
    end
    else if @conflogging = 0 and
            @pubcompat_level_id < @REPOLEVersion_70SP2
    begin
        raiserror(21349, 10, -1, @publication)
        set @pubcompat_level_id= @REPOLEVersion_70SP2
    end

    /*
    ** Parameter Check:     @dynamic_filter.
    */

    IF @dynamic_filters IS NULL OR LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@dynamic_filters')
            RETURN (1)
        END

    IF LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @dynamic_filters_id = 1
    ELSE
        SELECT @dynamic_filters_id = 0

    if @validate_subscriber_info is not NULL
    begin
        if @dynamic_filters_id = 0
            begin
                raiserror(21313, 16, -1)
                return (1)
            end
    end
    else if @pubcompat_level_id >= @REPOLEVersion_90
		select @validate_subscriber_info = N''	-- Empty string = auto-detect. NULL = don't use the feature.
												-- NULL is not available for new 90 compat publications any more.

    /*
    ** Parameter Check:     @allow_partition_realignment.
    */

    IF @allow_partition_realignment IS NULL OR LOWER(@allow_partition_realignment collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_partition_realignment')
            RETURN (1)
        END

    IF LOWER(@allow_partition_realignment collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @allow_partition_realignment_id = 1
    ELSE
        SELECT @allow_partition_realignment_id = 0

    -- Portable snapshot
    IF @snapshot_in_defaultfolder IS NULL OR LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder')
        RETURN (1)
    END

    IF LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        SELECT @snapshot_in_defaultfolder_bit = 1
    END
    ELSE
    BEGIN
        SELECT @snapshot_in_defaultfolder_bit = 0
    END

    -- Pre/Post snapshot commands
    -- If @sync_method is character mode bcp, this would indicate that
    -- this publication may support non-SQL Server subscribers. In this
    -- case, pre- and post- snapshot commands are not allowed.
    IF @sync_modeid = 1 AND
        ((@pre_snapshot_script IS NOT NULL AND @pre_snapshot_script <> N'' ) OR
         (@post_snapshot_script IS NOT NULL AND @post_snapshot_script <> N''))
    BEGIN
        RAISERROR (21151, 16, -1)
        RETURN (1)
    END

    -- Parameter check - @compress_snapshot
    -- @compress_snapshot can be 1 if @alt_snapshot_folder is non-null
    IF @compress_snapshot IS NULL OR LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@compress_snapshot')
        RETURN (1)
    END

    IF LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        SELECT @compress_snapshot_bit = 1
    END
    ELSE
    BEGIN
        SELECT @compress_snapshot_bit = 0
    END


    -- Only bump up the compatibility level if only a compressed snapshot
    -- is generated at the alternate snapshot folder
    if @snapshot_in_defaultfolder_bit = 0 and
       @compress_snapshot_bit = 1
    begin
        if @pubcompat_level_id < @REPOLEVersion_80RTM
        begin
            raiserror(21350, 10, -1, @publication)
            set @pubcompat_level_id= @REPOLEVersion_80RTM
        end
    end

    -- Snapshot compression can only be enabled if an alternate
    -- snapshot generation folder exists.
    IF (@compress_snapshot_bit = 1 AND
        (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N''))
    BEGIN
        RAISERROR (21157, 16, -1)
        RETURN (1)
    END

    -- Parameter check: ftp_address
    -- If the publication is enabled for internet, ftp_address cannot be null
    IF @enabled_for_internet_id = 1 AND (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR (21158, 16, -1)
        RETURN (1)
    END

    -- Parameter check: ftp_port
    IF @ftp_port IS NULL OR @ftp_port < 0 OR @ftp_port > 65535
    BEGIN
        RAISERROR (21160, 16, -1)
        return 1
    END

    if @ftp_address IS not NULL and @ftp_address <> N''
    begin
        select @enabled_for_internet_id = 1
    end

    -- Encrypt ftp password before putting it into the dbo.sysmergepublications
    -- table if one is provided
    SELECT @enc_ftp_password = NULL
    IF @ftp_password IS NOT NULL
    BEGIN
        SELECT @enc_ftp_password = @ftp_password
        EXEC @retcode = sys.sp_MSreplencrypt @enc_ftp_password OUTPUT
        IF @retcode <> 0
        BEGIN
            RETURN (1)
        END
    END

    /*
    ** Parameter Check:     @keep_partition_changes.
    */

    if LOWER(@keep_partition_changes collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@keep_partition_changes')
            RETURN (1)
        END

    if LOWER(@keep_partition_changes collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        begin
            set @keep_before_values_int = 1
        end
    else
        set @keep_before_values_int = 0

    /*
    ** Parameter Check:     @allow_subscription_copy_id.
    */

    if LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_subscription_copy')
            RETURN (1)
        END

    IF LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @allow_subscription_copy_id = 1
    ELSE
        SELECT @allow_subscription_copy_id = 0

    /*
    ** Parameter Check:     @use_partition_groups.
    */

    if @use_partition_groups is not null and
                LOWER(@use_partition_groups collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@use_partition_groups')
        RETURN (1)
    END

    IF LOWER(@use_partition_groups collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @use_partition_groups_id = 2
    ELSE IF LOWER(@use_partition_groups collate SQL_Latin1_General_CP1_CS_AS) = 'false'
        SELECT @use_partition_groups_id = 0
    ELSE
        SELECT @use_partition_groups_id = NULL    -- Let system decide whether it should be true or false.

    -- Keep-before-image functionality is not supported if partition id's are in use and backward
    -- compat level is 90 or higher.
    if @use_partition_groups_id = 2 and @pubcompat_level_id >= @REPOLEVersion_90
        and @keep_before_values_int = 1
    begin
        select @keep_before_values_int = 0
        --raiserror(21729, 16, -1)
    end

    /*
    ** Parameter Check:     @allow_synctoalternate_id.
    */

    if LOWER(@allow_synctoalternate collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_synctoalternate')
            RETURN (1)
        END

    IF LOWER(@allow_synctoalternate collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @allow_synctoalternate_id = 1
    ELSE
        SELECT @allow_synctoalternate_id = 0

    if LOWER(@allow_web_synchronization collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_web_synchronization')
            RETURN (1)
        END

    IF LOWER(@allow_web_synchronization collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @allow_web_synchronization_id = 1
    ELSE
        SELECT @allow_web_synchronization_id = 0
    /*
    ** Parameter Check: @max_concurrent_dynamic_snapshots
    */

    if @max_concurrent_dynamic_snapshots < 0 or @max_concurrent_dynamic_snapshots is null
    begin
        raiserror(21403, 16, -1)
        return (1)
    end

    /*
    ** Parameter Check:     @allow_subscriber_initiated_snapshot
    */

    if LOWER(@allow_subscriber_initiated_snapshot collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@allow_subscriber_initiated_snapshot')
        RETURN (1)
    END

    IF LOWER(@allow_subscriber_initiated_snapshot collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @allow_subscriber_initiated_snapshot_id = 1
    ELSE
        SELECT @allow_subscriber_initiated_snapshot_id = 0

    /*
    ** Parameter Check:     @replicate_ddl
    */
    -- the default for @replicate_ddl is NULL. Choose a default of 0 or 1 based on the pub compat level
    if @replicate_ddl is NULL
    begin
        if @pubcompat_level_id < @REPOLEVersion_90
            select @replicate_ddl = 0
        else
            select @replicate_ddl = 1
    end
    else
    begin
        if @replicate_ddl <> 0 and @pubcompat_level_id < @REPOLEVersion_90
        begin
            RAISERROR (20650, 16, -1, @publication)
            RETURN (1)
        end
        if @replicate_ddl > 1
            select @replicate_ddl = 1
    end

    /*
    ** Check to see if the publication name is already used.
    ** 1. check merge pubs
    ** 2. check online publications
    */
    if exists (select * from dbo.sysmergepublications
        where name = @publication  and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name())
    begin
        RAISERROR (20025, 16, -1, @publication)
        RETURN (1)
    end

    if (select category & @tranpublish_bit from master.dbo.sysdatabases where name = @db_name collate database_default) <> 0
    begin
        EXEC @retcode = sys.sp_helppublication @publication, @found output

        if @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            RETURN (1)
        END

        if @found <> 0
        BEGIN
            RAISERROR (20025, 16, -1, @publication)
            RETURN (1)
        END
    end


    /*
    **    Add the publication as the designmaster of the replica set.
    */

    /* Generate a guid for the publication ID */
    set @pubid = newid()

    /* Look for existing nickname from any other subscription */
    exec @retcode= sys.sp_MSgetreplnick NULL, NULL , NULL,    @replnick out
    if (@@error <> 0) or @retcode <> 0
    begin
        RETURN(1)
    end

    /* Generate a new replica nickname from the @pubid */
    if (@replnick is null)
    begin
        execute @retcode = sys.sp_MSgenreplnickname
                                @srcguid= @pubid,
                                @replnick= @replnick output,
                                @compatlevel= @pubcompat_level_id
        IF @@ERROR <>0 OR @retcode <> 0
        BEGIN
            RAISERROR (20077, 16, -1)
            RETURN (1)
        END
    end
    else
    begin
        -- if replnick is not null it means that this is a republisher
        -- check to see if there are any anonymous or local subscriptions
        if exists (select 1 from dbo.sysmergesubscriptions
                    where db_name=@db_name and
                          UPPER(subscriber_server) collate database_default = UPPER(publishingservername()) collate database_default and
                          subscriber_type > 1 and
                          status <> 7) -- REPLICA_STATUS_BeforeRestore
        begin
            RAISERROR(21087, 16, -1)
            return (1)
        end

        -- check to see if there are global subscriptions with prority 0
        select @priority=max(priority) from dbo.sysmergesubscriptions
            where db_name=@db_name and
                  UPPER(subscriber_server) collate database_default = UPPER(publishingservername()) collate database_default and
                  status <> 7 -- REPLICA_STATUS_BeforeRestore
        if @priority = 0
        begin
            RAISERROR(21091, 16, -1)
            return (1)
        end
    end


    /*
    ** Get distributor information
    */
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT
                            ,@distributor = @distservername OUTPUT
                            ,@distribdb = @distribdb OUTPUT
                            ,@working_directory = @working_directory OUTPUT
    if @@error <> 0 OR @retcode <> 0 or @distributor IS NULL OR @distribdb IS NULL or @working_directory is null
    BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

    /*
    **    add an entry into dbo.sysmergepublications
    */
    begin tran
    save tran sp_addmergepublication

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

        select @got_merge_admin_applock = 1

        /* Add row in the publications table */
        insert dbo.sysmergepublications
            (pubid,
            name,
            description,
            designmasterid,
            retention,
            parentid,
            sync_mode,
            allow_push,
            allow_pull,
            allow_anonymous,
            centralized_conflicts,
            decentralized_conflicts,
            status,
            snapshot_ready,
            enabled_for_internet,
            publication_type,
            dynamic_filters,
            snapshot_in_defaultfolder,
            alt_snapshot_folder,
            pre_snapshot_script,
            post_snapshot_script,
            compress_snapshot,
            ftp_address,
            ftp_port,
            ftp_subdirectory,
            ftp_login,
            ftp_password,
            conflict_retention,
            keep_before_values,
            allow_subscription_copy,
            allow_synctoalternate,
            validate_subscriber_info,
            ad_guidname,
            backward_comp_level,
            max_concurrent_merge,
            max_concurrent_dynamic_snapshots,
            use_partition_groups,
            replicate_ddl,
            allow_subscriber_initiated_snapshot,
            distributor,
            allow_web_synchronization,
            web_synchronization_url,
            allow_partition_realignment,
            retention_period_unit,
            generation_leveling_threshold,
            automatic_reinitialization_policy)
        values
            (@pubid,
            @publication,
            @description,
            @pubid,
            @retention,
            @pubid,
            @sync_modeid,
            @allow_push_id,
            @allow_pull_id,
            @allow_anonymous_id,
            case @conflogging -- centralized
                when 1 then 1
                when 2 then 1
                else 0
            end,
            case @conflogging -- decentralized
                when 1 then 0
                when 2 then 1
                else 1
            end,
            @statid,
            @false,
            @enabled_for_internet_id,
            @full,
            @dynamic_filters_id,
            @snapshot_in_defaultfolder_bit,
            @alt_snapshot_folder,
            @pre_snapshot_script,
            @post_snapshot_script,
            @compress_snapshot_bit,
            @ftp_address,
            @ftp_port,
            @ftp_subdirectory,
            @ftp_login,
            @enc_ftp_password,
            @conflict_retention,
            @keep_before_values_int,
            @allow_subscription_copy_id,
            @allow_synctoalternate_id,
            @validate_subscriber_info,
            @ad_guidname,
            @pubcompat_level_id,
            @max_concurrent_merge,
            @max_concurrent_dynamic_snapshots,
            @use_partition_groups_id,
            @replicate_ddl,
            @allow_subscriber_initiated_snapshot_id,
            @distservername,
            @allow_web_synchronization_id,
            @web_synchronization_url,
            @allow_partition_realignment_id,
            @retention_period_unit_id,
            @generation_leveling_threshold,
            @automatic_reinitialization_policy)
        if @@ERROR <> 0
            begin
                goto FAILURE
            end

        /* Add row to represent reciprocal subscription */
        INSERT dbo.sysmergesubscriptions (subscriber_server, db_name, pubid, datasource_type,
                subid, replnickname, replicastate, status, subscriber_type, subscription_type,
                sync_type, description, priority, last_validated, replica_version)
        VALUES (publishingservername(), @db_name, @pubid, 0,
                @pubid, @replnick, newid(), @statid, @global, @push,
                @automatic, @description, @priority, getdate(), 90)
        if @@ERROR <> 0
            begin
                goto FAILURE
            end
        /*
        **    Add row for merge publication to dbo.MSmerge_replinfo.
        */
        insert dbo.MSmerge_replinfo(repid, login_name)
            values (@pubid, suser_sname(suser_sid()))
        if @@ERROR <> 0
            begin
                goto FAILURE
            end


        /*
        ** Add the publication to the distributor side
       */
        SELECT @distpubid = @replnick

        declare @publisher_engine_edition int

        select @publisher_engine_edition = sys.fn_MSrepl_editionid()

        select @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
            '.dbo.sp_MSadd_publication'
        EXECUTE @retcode = @distproc
            @publisher = @publishingservername,
            @publisher_db = @db_name,
            @publication = @publication,
            --@publication_id = NULL,
            @publication_type = 2,            -- 0 = Trans, 1 = Snapshot, 2 = Merge
            @independent_agent = @true,
            @immediate_sync = @true,
            @allow_push = @allow_push_id,
            @allow_pull = @allow_pull_id,
            @allow_anonymous = @allow_anonymous_id,
            @description = @description,
            @retention = @retention,
            @allow_subscription_copy = @allow_subscription_copy_id,
            @retention_period_unit = @retention_period_unit_id,
            @publisher_engine_edition = @publisher_engine_edition

        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                GOTO FAILURE
            END

        -- Populate the initial list.
        exec @retcode = sys.sp_grant_publication_access
            @publication = @publication,
            @login = null,
            @reserved = 'init'
        IF @@error <> 0 OR @retcode <> 0
            GOTO FAILURE


    exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
    commit tran

    return (0)

FAILURE:
    RAISERROR (14018, 16, -1)
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        if @got_merge_admin_applock=1
            exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
        ROLLBACK TRANSACTION sp_addmergepublication
        COMMIT TRANSACTION
    end
    return (1)

 
Last revision 2008RTM
See also

  sp_grant_publication_access (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MSmerge_alterschemaonly (Procedure)
sp_MSmerge_altertrigger (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