Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addpublication

  No additional text.


Syntax

create procedure sys.sp_MSrepl_addpublication
(
    @publication                sysname,
    @taskid                        int,
    @restricted                    nvarchar (10),
    @sync_method                nvarchar(40),
    @repl_freq                    nvarchar(10),
    @description                nvarchar (255),
    @status                        nvarchar(8),
    @independent_agent            nvarchar(5),
    @immediate_sync                nvarchar(5),
    @enabled_for_internet        nvarchar(5),
    @allow_push                    nvarchar(5),
    @allow_pull                    nvarchar(5),
    @allow_anonymous            nvarchar(5),
    @allow_sync_tran            nvarchar(5),
    @autogen_sync_procs            nvarchar(5),
    @retention                    int,
    @allow_queued_tran            nvarchar(5),
    @snapshot_in_defaultfolder    nvarchar(5),
    @alt_snapshot_folder        nvarchar(255),
    @pre_snapshot_script        nvarchar(255),
    @post_snapshot_script        nvarchar(255),
    @compress_snapshot            nvarchar(5),
    @ftp_address                sysname,
    @ftp_port                    int,
    @ftp_subdirectory            nvarchar(255),
    @ftp_login                    sysname,
    @ftp_password                sysname,
    @allow_dts                    nvarchar(5),
    @allow_subscription_copy    nvarchar(5),
    @conflict_policy            nvarchar(100),
    @centralized_conflicts        nvarchar(5),
    @conflict_retention            int,
    @queue_type                    nvarchar(10),
    @add_to_active_directory    nvarchar(10),
    @logreader_job_name            sysname,
    @qreader_job_name            sysname,
    @publisher                    sysname,
    @allow_initialize_from_backup        nvarchar(5),
    @replicate_ddl                int,
    @publisher_type                sysname,
    @enabled_for_p2p            nvarchar(5),
    @publish_local_changes_only    nvarchar(5),
    @enabled_for_het_sub        nvarchar(5),
    @p2p_conflictdetection    nvarchar(5),
    @p2p_originator_id         int,
    @p2p_continue_onconflict	nvarchar(5),
	@allow_partition_switch		nvarchar(5),
	@replicate_partition_switch nvarchar(5)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode    int         -- return code value for procedure execution
            ,@rfid tinyint           -- identifier for replication frequency
            ,@publish_bit smallint   -- publication bit (flag) in sys.objects
            ,@smid tinyint           -- identifier for sync method
            ,@statid tinyint         -- status id based on @status
            ,@subs_type_id tinyint   -- subscription type id based on @subscription_type
            ,@distributor sysname
            ,@distribdb sysname
            ,@distproc nvarchar (1000)
            ,@mergepublish_bit smallint
            ,@found int
            ,@independent_agent_id bit
            ,@immediate_sync_id bit
            ,@enabled_for_internet_id bit
            ,@allow_push_id bit
            ,@allow_pull_id bit
            ,@allow_anonymous_id bit
            ,@pubid int
            ,@distgroup sysname
            ,@enc_ftp_password nvarchar(524)
            ,@ad_guidname   sysname
            ,@publisher_db sysname
            ,@Cmd1 varchar(2048)
            ,@Cmd2 varchar(2048)
            ,@allow_initialize_from_backup_id bit
            ,@srvname sysname
            ,@options_bitfield int
            ,@loc_publisher sysname
            ,@publisher_engine_edition int
            ,@allow_dts_id bit
            ,@allow_subscription_copy_id bit

            -- Option Bitfield Constants
            ,@OPT_ENABLED_FOR_P2P int
            ,@OPT_PUBLISH_ONLY_LOCAL_CHANGES int
            ,@OPT_ENABLED_FOR_HET_SUB int
            ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
            ,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT int
			,@OPT_ALLOW_PARTITION_SWITCH int
			,@OPT_REPLICATE_PARTITION_SWITCH int
            -- SyncTran
            ,@allow_sync_tran_id bit
            ,@allow_queued_tran_id bit
            ,@autogen_sync_procs_id bit
            ,@conflict_policy_id int
            ,@centralized_conflicts_bit bit
            ,@queue_type_val int
            -- Portable snapshot
            ,@snapshot_in_defaultfolder_bit int
            -- Snapshot compression
            ,@compress_snapshot_bit bit
            -- sku vars
            ,@qv_replication varchar(10)
            ,@qv_replication_unlimited integer
            ,@qv_value_replication integer
            ,@backward_comp_level int
            ,@db_name sysname
            ,@edition sysname
            ,@job_existing bit
            ,@isyukonsecuritymodelrequired bit
            ,@logreader_exists bit
            ,@qreader_exists bit
            ,@current_db_version int
            ,@issqlpublisher bit
            ,@cdcjob nvarchar(500)
            ,@strval nvarchar(32)

    select @backward_comp_level = 10 -- default to sphinx
            ,@allow_sync_tran_id = 0
            ,@autogen_sync_procs_id = 0
            ,@allow_queued_tran_id = 0
            ,@qv_replication = '2745196162'
            ,@qv_replication_unlimited = 0
            ,@srvname = publishingservername()
            ,@OPT_ENABLED_FOR_P2P = 0x1
            ,@OPT_PUBLISH_ONLY_LOCAL_CHANGES = 0x2
            ,@OPT_ENABLED_FOR_HET_SUB = 0x4
            ,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8
            ,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT = 0x10
			,@OPT_ALLOW_PARTITION_SWITCH = 0x20
			,@OPT_REPLICATE_PARTITION_SWITCH = 0x40
            ,@options_bitfield = 0
            ,@publish_bit = 32
            ,@mergepublish_bit = 4
            ,@db_name = db_name()
            ,@allow_sync_tran_id = 0
            ,@allow_queued_tran_id = 0
            ,@issqlpublisher = case when (@publisher_type = N'MSSQLSERVER') then 1 else 0 end
            ,@publisher_engine_edition = sys.fn_MSrepl_editionid ()
            ,@current_db_version = 0

    /*
    ** Security Check
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
    /*
    ** The default value for TRAN publication is always 72 hours
    */
    if @retention is NULL
    BEGIN
        RAISERROR(20081, 16, -1, N'retention')
        RETURN (1)
    END
    /*
    **  A @retention value of zero means an infinite retention period
    **  can't go past (9999 years * 366 days * 24 hrs) = 87831216 hrs.
    */
    if @retention not between 0 and 87831216
    BEGIN
        RAISERROR (20050, 16, -1, 0)
        RETURN(1)
    END
    /*
    ** Check to see if the database has been activated for publication.
    */
    IF sys.fn_MSrepl_istranpublished(@db_name, 1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END
    
    -- Get distribution server and database information for remote RPC
    -- agent verification and for Het publishers
    
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
                                                                        @rpcsrvname = @distributor OUTPUT,
                                                                        @distribdb = @distribdb OUTPUT
    IF @@error <> 0 OR @retcode <> 0
    BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END
    
    -- Verify distribution info
    
    IF (@publisher IS NOT NULL) AND (@publisher_type IS NULL OR @distribdb IS NULL OR @distributor IS NULL)
    BEGIN
        RAISERROR(21600, 16, -1, @publisher)
        RETURN (1)
    END
    /*
    ** hetpub: get bit to see if publisher is heterogeneous
    */
    if @issqlpublisher = 1
    BEGIN
        select @loc_publisher = @srvname
                ,@publisher_db = @db_name
    END
    ELSE
    BEGIN
        SELECT @loc_publisher = @publisher
                ,@publisher_db = @distribdb
    END
    
    -- task id not supported
    
    IF @taskid <> 0
    BEGIN
        -- No longer supported
        RAISERROR (21023, 16, -1,'@taskid')
        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_MSrepl_addpublication'
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    IF LOWER (@publication) = N'all'
    BEGIN
        RAISERROR (14034, 16, -1)
        RETURN (1)
    END

    /*
    **  Check if the publication already exists.
    **  1. Check transaction-level publications
    **  2. Check merge publications
    */
    IF @issqlpublisher = 1
    BEGIN
        IF EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (14016, 16, -1, @publication)
            RETURN (1)
        END

        if exists (select * from master.dbo.sysdatabases
                    where name = @db_name
                        and category & @mergepublish_bit != 0)
        begin
            EXEC @retcode = sys.sp_helpmergepublication @publication, @found output
            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)

            IF @found <> 0
            BEGIN
                RAISERROR (20025, 16, -1, @publication)
                RETURN (1)
            END
        end
    END -- SQL publication
    ELSE
    BEGIN
        IF EXISTS
        (
            --SELECT	*
            --FROM	[dbo].[IHpublications] IH,
            --		[dbo].[MSpublications] MS,
            --		[master].[dbo].[sysservers] SYS
            --WHERE	name = @publication
            --  AND	IH.pubid = MS.publication_id
            --  AND	MS.publisher_id = SYS.srvid
            --  AND	UPPER(SYS.srvname collate database_default) = UPPER(@publisher)
            
            -- NOTE:  The user interface cannot cleanly support publications of the same name, for two
            --        different Oracle servers sharing the same distribution database.  We therefore
            --        will enforce this constraint here when an attempt is made to add a second
            --        publication of the same name.
            SELECT	*
            FROM	[dbo].[IHpublications]
            WHERE	name = @publication
        )
        BEGIN
            RAISERROR (14016, 16, -1, @publication)
            RETURN (1)
        END
    END
    /*
    ** Parameter Check: @repl_freq.
    ** Make sure that the replication frequency is one of the following:
    **
    **  id  frequency
    **  ==  ==========
    **   0  continuous
    **   1  snapshot
    */
    SELECT @repl_freq = LOWER(@repl_freq)
    IF @repl_freq IS NULL OR @repl_freq NOT IN (N'continuous', N'snapshot')
    BEGIN
        RAISERROR (14015, 16, -1)
        RETURN (1)
    END

    SELECT @enabled_for_het_sub = LOWER(@enabled_for_het_sub)
    IF @enabled_for_het_sub IS NULL OR @enabled_for_het_sub not in (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@enabled_for_het_sub')
        RETURN (1)
    END

    /*
    ** Parameter Check: @sync_method
    ** The synchronization method must be one of the following:
    **
    **      0  [bcp] native
    **      1  [bcp] character
    **      2  dump database
    **      3  concurrent
    **      4  concurrent character
    **      5  database snapshot
    **      6  database snapshot character
    */
    select @sync_method = case
                when (@sync_method IS NULL)
                        then case when (@issqlpublisher = 1)
                                            -- for SQL publications
                                            then case when (ISNULL(@enabled_for_het_sub, N'false') = N'false') then N'native' else N'character' end
                                            -- for heterogenous publications
                                            else CASE WHEN @repl_freq = N'snapshot' THEN N'character' ELSE N'concurrent_c' END
                                            end
                        else LOWER(@sync_method) end
    IF @sync_method NOT IN (N'native', N'character', N'bcp native', N'bcp character', N'dump database', N'concurrent', N'concurrent_c', N'database snapshot', N'database snapshot character')
    BEGIN
        RAISERROR (14014, 16, -1)
        RETURN (1)
    END

    if (@sync_method = N'dump database')
        select @backward_comp_level = 40 -- not sure if we are using this, but has to be shiloh feature

    SELECT @smid =  case
                                    when (@sync_method = N'database snapshot character') then 6
                                    when (@sync_method = N'database snapshot') then 5
                                    when (@sync_method = N'concurrent_c') then 4
                                    when (@sync_method = N'concurrent') then 3
                                    when (@sync_method = N'dump database') then 2
                                    when (@sync_method IN (N'character', N'bcp character')) then 1
                                    else 0
                            end
            ,@rfid = CASE WHEN @repl_freq = N'snapshot' THEN 1 ELSE 0 END

    IF @repl_freq = N'snapshot' AND @smid in (4, 3)
    BEGIN
        RAISERROR(22547, 16, -1)
        RETURN (1)
    END

    IF @issqlpublisher = 0
    BEGIN
        -- HREPL: Must be character mode sync method
        if @smid not in (1,4)
        begin
            RAISERROR(21633, 16, -1, @publication)
            RETURN (1)
        end
        -- Verify HREPL repl frequency/sync method combinations
        -- SNAPSHOT requires CHARACTER
        IF @rfid = 1 AND @smid != 1
        BEGIN
            RAISERROR(21638, 16, -1, @repl_freq, N'''character''')
            RETURN (1)
        END
        -- TRANSACTIONAL requires CONCURRENT_C
        ELSE IF @rfid = 0 AND @smid != 4
        BEGIN
            RAISERROR(21638, 16, -1, @repl_freq, '''concurrent_c''')
            RETURN (1)
        END
    END
    
    -- Check editions for enabling publication creation
    
    IF @issqlpublisher = 1
    BEGIN
        
        -- SQL publisher
        -- Allow snapshot/transactional publication on Enterprise, Eval,
        -- Developer, Standard, Workgroup
        -- For P2P enabled or Heterogenous subscriber enabled publication
        -- additional check is done later
        
        if (@publisher_engine_edition not in (20,21,30,31))
        begin
            raiserror(21108, 16, -1)
            return (1)
        end
    END
    ELSE
    BEGIN
        
        -- HREPL publisher
        -- Allow publication on Enterprise, Eval, Developer
        
        if (@publisher_engine_edition not in (30,31))
        begin
            raiserror(21108, 16, -1)
            return (1)
        end
    END -- end hrepl pub
     /*
    ** Parameter Check:  @restricted.
    */
    SELECT @restricted = LOWER(@restricted)
    IF (@restricted IS NULL) OR (@restricted NOT IN (N'true', N'false'))
    BEGIN
        RAISERROR (14017, 16, -1)
        RETURN (1)
    END
    /*
    ** Restricted publications are no longer supported
    */
    IF @restricted = N'true'
    BEGIN
        RAISERROR (14147, 16, -1)
        RETURN(1)
    END
    /*
    ** Parameter Check:  @status.
    ** The @status value can be:
    **
    **      statid  status
    **      ======  ========
    **           0  inactive
    **           1  active
    */
    SELECT @status = LOWER(@status)
    IF @status IS NULL OR @status NOT IN (N'inactive', N'active')
    BEGIN
        RAISERROR (14012, 16, -1)
        RETURN (1)
    END
    SELECT @statid = case when (@status = N'active') then 1 else 0 end
    /*
    ** Parameter Check:  @independent_agent.
    */
    SELECT @independent_agent = LOWER(@independent_agent)
    IF @independent_agent IS NULL OR @independent_agent NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@independent_agent')
        RETURN (1)
    END
    SELECT @independent_agent_id = case when (@independent_agent = N'true') then 1 else 0 end
    /*
    ** Parameter Check:  @immediate_sync.
    */
    -- set defaults differently depending on p2p being enabled
    SELECT @immediate_sync = case when (@immediate_sync IS NULL)
                    then case when (LTRIM(RTRIM(LOWER(@enabled_for_p2p))) = N'true')
                                        then N'true' else N'false' end
                    else LOWER(@immediate_sync) end

    IF @immediate_sync NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@immediate_sync')
        RETURN (1)
    END
    SELECT @immediate_sync_id = case when (@immediate_sync = N'true') then 1 else 0 end
    /*
    ** Parameter Check:  @enabled_for_internet.
    */
    SELECT @enabled_for_internet = LOWER(@enabled_for_internet)
    IF @enabled_for_internet IS NULL OR @enabled_for_internet NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@enabled_for_internet')
        RETURN (1)
    END
    SELECT @enabled_for_internet_id = case when (@enabled_for_internet = N'true') then 1 else 0 end
    /*
    ** Parameter Check:  @allow_push.
    */
    SELECT @allow_push = LOWER(@allow_push)
    IF @allow_push IS NULL OR @allow_push NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_push')
        RETURN (1)
    END
    SELECT @allow_push_id = case when (@allow_push = N'true') then 1 else 0 end
    /*
    ** Parameter Check:  @allow_pull.
    */
    SELECT @allow_pull = LOWER(@allow_pull)
    IF @allow_pull IS NULL OR @allow_pull NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_pull')
        RETURN (1)
    END
    SELECT @allow_pull_id = case when (@allow_pull = N'true') then 1 else 0 end
    /*
    ** Parameter Check:  @allow_anonymous.
    */
    SELECT @allow_anonymous = LOWER(@allow_anonymous)
    IF @allow_anonymous IS NULL OR @allow_anonymous NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_anonymous')
        RETURN (1)
    END
    SELECT @allow_anonymous_id = case when (@allow_anonymous = N'true') then 1 else 0 end
    /* Immediate_sync publications have to be independent_agent */
    IF @immediate_sync_id = 1 AND @independent_agent_id = 0
    BEGIN
        RAISERROR (21022, 16, -1)
        RETURN (1)
    END
    /*
    ** Non-immediate sync do not support anonymous subscriptions.
    */
    IF @immediate_sync_id = 0 AND @allow_anonymous_id = 1
    BEGIN
            RAISERROR (20011, 16, -1)
            RETURN (1)
    END
    -- SyncTran
    /*
    ** Parameter Check:  @allow_sync_tran
    */
    SELECT @allow_sync_tran = LOWER(@allow_sync_tran)
    IF @allow_sync_tran IS NULL OR @allow_sync_tran NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_sync_tran')
        RETURN (1)
    END

    IF @publisher_type != N'MSSQLSERVER' AND @allow_sync_tran = N'true'
    BEGIN
        RAISERROR (21632, 16, -1, N'@allow_sync_tran', N'''false'' or NULL')
        RETURN (1)
    END
    /*
    ** Parameter Check:  @allow_queued_tran
    */
    SELECT @allow_queued_tran = LOWER(@allow_queued_tran)
    IF @allow_queued_tran IS NULL OR @allow_queued_tran NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_queued_tran')
        RETURN (1)
    END

    IF @publisher_type != N'MSSQLSERVER' AND @allow_queued_tran = N'true'
    BEGIN
        RAISERROR (21632, 16, -1, N'@allow_queued_tran', N'''false'' or NULL')
       RETURN (1)
    END

    IF @allow_sync_tran = N'true'
    BEGIN
        -- If we are doing sync tran, we need independent agents
        -- override the user input
        SELECT @allow_sync_tran_id = 1
            ,@independent_agent = N'true'
            ,@independent_agent_id = 1
            ,@backward_comp_level = 40 -- immediate update needs to have the new sp_addsynctriggers stored proc
    END

    IF @allow_queued_tran = N'true'
    BEGIN
        -- If we are doing queued tran, we need independent agents
        -- override the user input
        SELECT @allow_queued_tran_id = 1
            ,@independent_agent = N'true'
            ,@independent_agent_id = 1
            ,@backward_comp_level = 40 -- queued compenents not avaliable prior to shiloh
    END
    -- Parameter Check:  @autogen_sync_procs
    -- If default requested, set to TRUE for SQL Server publications
    -- and FALSE for HREPL publications
    select @autogen_sync_procs = case when (@autogen_sync_procs IS NULL)
                    then case when (@issqlpublisher = 1) then N'true' else N'false' end
                    else LOWER(@autogen_sync_procs) end

    IF @autogen_sync_procs NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@autogen_sync_procs')
        RETURN (1)
    END

    IF @issqlpublisher = 0 and @autogen_sync_procs = N'true'
    BEGIN
        RAISERROR (21632, 16, -1, N'@autogen_sync_procs', N'''false'' or NULL')
        RETURN (1)
    END
    
    -- For publications that allow updating subscribers (immediate/queued)
    -- this option has to be true at all times, for others
    -- it should be false. This flag is not of any value currently
    -- as we do not have any provision for accepting custom generated
    -- synctan proc names for an article. For now, we will override
    -- the user supplied value
    
    select @autogen_sync_procs_id = case
        when (@allow_sync_tran_id = 0 and @allow_queued_tran_id = 0) then 0
        else 1
    end
    
    -- Portable snapshot
    
    SELECT @snapshot_in_defaultfolder = LOWER(@snapshot_in_defaultfolder)
    IF @snapshot_in_defaultfolder IS NULL OR @snapshot_in_defaultfolder NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@snapshot_in_defaultfolder')
        RETURN (1)
    END
    SELECT @snapshot_in_defaultfolder_bit = case when (@snapshot_in_defaultfolder = N'true') then 1 else 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.
    
    -- HREPL: Ignore this restriction
    if (@pre_snapshot_script = N'')
        select @pre_snapshot_script = NULL
    if (@post_snapshot_script = N'')
        select @post_snapshot_script = NULL

    IF @smid = 1 AND @issqlpublisher = 1 AND
        (@pre_snapshot_script IS NOT NULL OR @post_snapshot_script IS NOT NULL )
    BEGIN
        RAISERROR (21151, 16, -1)
        RETURN (1)
    END
    
    -- Parameter check - @compress_snapshot
    -- @compress_snapshot can be 1 if @alt_snapshot_folder is non-null
    
    SELECT @compress_snapshot = LOWER(@compress_snapshot)
    IF @compress_snapshot IS NULL OR @compress_snapshot NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@compress_snapshot')
        RETURN (1)
    END
    SELECT @compress_snapshot_bit = case when (@compress_snapshot = N'true') then 1 else 0 end
    
    -- Snapshot compression can only be enabled if an alternate
    -- snapshot generation folder exists or the publication
    -- is enabled for internet.
    
    if (@alt_snapshot_folder = N'')
        select @alt_snapshot_folder = NULL
    IF (@compress_snapshot_bit = 1 AND @alt_snapshot_folder IS NULL
    AND @enabled_for_internet = N'false')
    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 (@ftp_address = N'')
        select @ftp_address = NULL
    IF @enabled_for_internet_id = 1 AND @ftp_address IS NULL
    BEGIN
        RAISERROR (21158, 16, -1)
        RETURN (1)
    END

    -- Parameter check: ftp_port
    IF @ftp_port IS NULL or @ftp_port not between  0 and 65535
    BEGIN
        RAISERROR (21160, 16, -1)
        RETURN (1)
    END

    -- Parameter check : DTS
    SELECT @allow_dts = LOWER(@allow_dts)
    IF @allow_dts IS NULL OR @allow_dts NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_dts')
        RETURN (1)
    END

    IF @issqlpublisher = 0 and @allow_dts = N'true'
    BEGIN
        RAISERROR (21632, 16, -1, N'@allow_dts', N'false')
        RETURN (1)
    END

    -- Encrypt ftp password before putting it into the syspublications
    -- table if one is provided
    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
            RETURN (1)
    END

    SELECT @allow_dts_id = case when (@allow_dts = N'true') then 1 else 0 end

    -- To allow DTS, the publication has to be
    -- 1. independent agent
    -- 2. char bcp

    if @allow_dts_id = 1
    begin
        if @smid NOT IN ( 1, 4, 6 )
        begin
            -- 'The publication has to be in char bcp mode.'
            raiserror(21172, 16, -1)
            return(1)
        end
        if @independent_agent_id = 0
        begin
            -- 'The publication has to be independent agent.'
            raiserror(21173, 16, -1)
            return(1)
        end
        if @allow_sync_tran_id = 1 or @allow_queued_tran_id = 1
        begin
            raiserror(21180, 16, -1)
            return(1)
        end
    end

    -- Parameter check : @allow_subscription_copy
    SELECT @allow_subscription_copy = LOWER(@allow_subscription_copy)
    IF @allow_subscription_copy IS NULL OR @allow_subscription_copy NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_dts')
        RETURN (1)
    END
    SELECT @allow_subscription_copy_id = case when (@allow_subscription_copy = N'true') then 1 else 0 end
    
    -- To allow subscription copy, the publication has to be
    -- 1. immediate_sync
    
    if @allow_subscription_copy_id = 1 and @immediate_sync_id != 1
    begin
        raiserror(21210, 16, -1)
        return(1)
    end
    
    -- Check parameters for queued publications
    -- Parameter check: @conflict_policy
    -- Parameter check: @centralized_conflicts
    -- Parameter check: @queue_type
    
    SELECT @conflict_policy = LOWER(@conflict_policy)
                ,@centralized_conflicts = LOWER(@centralized_conflicts)
                ,@queue_type = LOWER(@queue_type)

    IF @issqlpublisher = 0
    BEGIN
        -- HREPL checks
        IF @conflict_policy is NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, N'@conflict_policy', N'NULL')
            RETURN (1)
        END
        IF @centralized_conflicts is NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, N'@centralized_conflicts', N'NULL')
            RETURN (1)
        END
        IF @queue_type is NOT NULL
        BEGIN
            RAISERROR (21632, 16, -1, N'@queue_type', N'NULL')
            RETURN (1)
        END
    END
    
    -- Set queued publication bits
    
    IF (@allow_queued_tran_id = 1)
    begin
        
        -- set default conflict_policy if required
        
        if (@conflict_policy IS NULL)
        begin
            
  -- if it is snapshot based, then the default policy is 'sub reinit'
            -- else the default policy is 'pub wins'
            
            select @conflict_policy = case when (@rfid = 1) then N'sub reinit'
                                        else N'pub wins' end
        end

        select @conflict_policy_id = case
                                    when (@conflict_policy = N'sub reinit') then 3
                                    when (@conflict_policy = N'pub wins') then 1
                                    when (@conflict_policy = N'sub wins') then 2
                                    else 0 end

        if (@conflict_policy_id = 0)
        begin
            raiserror (21184, 16, 2, N'@conflict_policy', N'sub reinit', N'pub wins', N'sub wins')
            return (1)
        end
        
        -- Check snapshot permissible values
        
        if (@rfid = 1 and @conflict_policy_id = 1)
        begin
            raiserror (21270, 16, 1, N'@conflict_policy', @conflict_policy)
            return (1)
        end
        
        -- set default centralized_conflicts if required
        
        if (@centralized_conflicts IS NULL)
            select @centralized_conflicts = N'true'

        if (@centralized_conflicts not in (N'true',N'false'))
        begin
            raiserror (14148, 16, -1, N'@centralized_conflicts')
            return (1)
        end
        select @centralized_conflicts_bit = case when (@centralized_conflicts = N'true') then 1 else 0 end
        
        -- Check snapshot permissible values
        
        if (@rfid = 1 and @centralized_conflicts_bit = 0)
        begin
            raiserror (21270, 16, 1, N'@centralized_conflicts', @centralized_conflicts)
            return (1)
        end

        if (@conflict_retention IS NULL)
            select @conflict_retention = 14
        else if (@conflict_retention < 0)
        begin
            raiserror(20050, 16, -1, 0)
            return (1)
        end

        if ((@queue_type IS NULL) or (@queue_type = N'sql'))
            select @queue_type_val = 2
        else if (@queue_type = N'msmq')
        begin
            raiserror(21767, 10, 1, N'@queue_type = ''msmq''', N'@queue_type = ''sql''')
            select @queue_type_val = 2
        end
        else
        begin
            raiserror (21184, 16, 3, N'@queue_type', N'sql', N'null', N'NULL')
            return (1)
        end
    end

   -- Parameter check: @qreader_job_name
   IF @issqlpublisher = 0 and @qreader_job_name IS NOT NULL
   BEGIN
        RAISERROR (21632, 16, -1, N'@qreader_job_name', N'NULL')
        RETURN (1)
   END

    -- Parameter check: @enabled_for_p2p
    SELECT @enabled_for_p2p = LOWER(@enabled_for_p2p)
    IF @enabled_for_p2p IS NULL OR @enabled_for_p2p not in (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@enabled_for_p2p')
        RETURN (1)
    END

    IF @enabled_for_p2p = N'true'
    begin
        -- Verify SQL Server edition
        -- Peer-To-Peer is restricted to Enterprise and Developer editions
        if (@publisher_engine_edition not in (30,31))
        begin
            select @edition = convert(sysname, serverproperty(N'Edition'))
            raiserror(20816, 16, -1, @edition)
            return (1)
        end

        if @issqlpublisher = 0
        begin
            -- @enabled_for_p2p is not supported for heterogeneous publications, must be false.
            raiserror (21632, 16, -1, N'@enabled_for_p2p', N'false')
            return (1)
        end

        if isnull(@allow_sync_tran, N'false') = N'true'
        begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@allow_sync_tran' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@allow_sync_tran', N'false')
            return (1)
        end

if isnull(@allow_queued_tran, N'false') = N'true'
        begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@allow_queued_tran' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@allow_queued_tran', N'false')
            return (1)
        end

        if isnull(@allow_dts, N'false') = N'true'
        begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@allow_dts' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@allow_dts', N'false')
            return (1)
        end

        if isnull(@conflict_policy, N'false') = N'true'
        begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@conflict_policy' is set to 'NULL'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@conflict_policy', N'NULL')
            return (1)
        end

        if @independent_agent_id != 1
        begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@independent_agent' is set to 'true'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@independent_agent', N'true')
            return (1)
        end

        if @allow_subscription_copy_id = 1
		begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@independent_agent' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@allow_subscription_copy_id', N'false')
            return (1)
        end

        if @repl_freq != N'continuous'
        begin
            -- Parameter '@enabled_for_p2p' can only be set to 'true' when '@repl_freq' is set to 'continuous'.
            raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@repl_freq', N'continuous')
            return (1)
        end

        if not isnull(@replicate_ddl, 1) = 1
        begin
	        -- Parameter N'@enabled_for_p2p' can only be set to N'true' when '@replicate_ddl' is set to '1'.
	        raiserror(21678, 16, -1, N'@enabled_for_p2p', N'true', N'@replicate_ddl', '1')
	        return (1)
        end

        -- section for forcefully setting property values for p2p
        if isnull(@allow_anonymous, N'true') = N'true'
        begin
            select @allow_anonymous = N'false'

            -- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
            raiserror(21853, 10, -1, N'allow_anonymous', N'publication', @publication, N'false', N'Peer-To-Peer')
        end

		if isnull(@immediate_sync, N'false') = N'false'
			or  @immediate_sync_id = 0
        begin
            -- here we have to force immediate_sync to true
            select @immediate_sync = N'true',
            		@immediate_sync_id = 1

            -- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
            raiserror(21853, 10, -1, N'immediate_sync', N'publication', @publication, N'true', N'Peer-To-Peer')
        end

        if isnull(@allow_initialize_from_backup, N'false') = N'false'
        begin
            -- here we have to force allow_initialize_from_backup to true
            select @allow_initialize_from_backup = N'true'

            -- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
            raiserror(21853, 10, -1, N'allow_initialize_from_backup', N'publication', @publication, N'true', N'Peer-To-Peer')
        end

        if isnull(@smid, 1) != 0
        begin
			select @smid = 0,
					@sync_method = N'native'
			
			-- Warning : The '%s' property for %s '%s' has been changed to '%s' because it is required by %s.
            raiserror(21853, 10, -1, N'sync_method', N'publication', @publication, N'native', N'Peer-To-Peer')			
        end

        -- set the p2p option bits
        select @options_bitfield = @options_bitfield | @OPT_ENABLED_FOR_P2P
    end

    -- Parameter check: @allow_initialize_from_backup (default to FALSE if the value is NULL)
    SELECT @allow_initialize_from_backup = ISNULL(LOWER(@allow_initialize_from_backup), N'false')
    IF @allow_initialize_from_backup NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_initialize_from_backup')
        RETURN (1)
    END

    SELECT @allow_initialize_from_backup_id =  case when (@allow_initialize_from_backup = N'true') then 1 else 0 end

    -- Verify related parameters if @enabled_for_het_sub = 'true'
    IF @enabled_for_het_sub = N'true'
    BEGIN
        -- Het_sub not supported on Workgroup Edition
        IF(@publisher_engine_edition = 21)
        BEGIN
            RAISERROR(14173, 16, -1,N'@enabled_for_het_sub')
            RETURN (1)
        END

        IF isnull(@enabled_for_internet, N'false') = N'true'
        BEGIN
            -- Parameter '@enabled_for_het_sub' can only be set to 'true' when '@enabled_for_internet' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@enabled_for_internet', N'false')
            return (1)
        END

        IF isnull(@allow_pull, N'false') = N'true'
        BEGIN
            -- Parameter '@enabled_for_het_sub' can only be set to 'true' when '@allow_pull' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@allow_pull', N'false')
            return (1)
        END

        IF isnull(@allow_sync_tran, N'false') = N'true'
        BEGIN
            -- Parameter '@enabled_for_het_sub' can only be set to 'true' when '@allow_sync_tran' is set to 'false'.
            raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@allow_sync_tran', N'false')
            return (1)
        END

        IF isnull(@autogen_sync_procs, N'false') = N'true'
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@autogen_sync_procs' is set to N'false'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@autogen_sync_procs', N'false')
        return (1)
        END

        IF isnull(@allow_queued_tran, N'false') = N'true'
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@allow_queued_tran' is set to N'false'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@allow_queued_tran', N'false')
        return (1)
        END

        IF not isnull(@pre_snapshot_script, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@pre_snapshot_script' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@pre_snapshot_script', 'NULL')
        return (1)
        END

        IF not isnull(@post_snapshot_script, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@post_snapshot_script' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@post_snapshot_script', 'NULL')
        return (1)
        END

        IF not isnull(@ftp_address, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@ftp_address' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@ftp_address', 'NULL')
        return (1)
        END

        IF not isnull(@ftp_subdirectory, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@ftp_subdirectory' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@ftp_subdirectory', 'NULL')
        return (1)
        END

        IF not isnull(@ftp_password, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@ftp_password' is set to 'NULL'.
 raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@ftp_password', 'NULL')
        return (1)
        END

        IF isnull(@allow_subscription_copy, N'false') = N'true'
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@allow_subscription_copy' is set to N'false'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@allow_subscription_copy', N'false')
        return (1)
        END

        IF not isnull(@conflict_policy, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@conflict_policy' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@conflict_policy', 'NULL')
        return (1)
        END

        IF not isnull(@queue_type, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@queue_type' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@queue_type', 'NULL')
        return (1)
        END

        IF not isnull(@qreader_job_name, N'') = N''
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@qreader_job_name' is set to 'NULL'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@qreader_job_name', 'NULL')
        return (1)
        END

        IF isnull(@allow_initialize_from_backup, N'false') = N'true'
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@allow_initialize_from_backup' is set to N'false'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@allow_initialize_from_backup', N'false')
        return (1)
        END

        IF not isnull(@replicate_ddl, 0) = 0
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when '@replicate_ddl' is set to '0'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@replicate_ddl', '0')
        return (1)
        END

        IF isnull(@enabled_for_p2p, N'false') = N'true'
        BEGIN
        -- Parameter N'@enabled_for_het_sub' can only be set to N'true' when N'@enabled_for_p2p' is set to N'false'.
        raiserror(21678, 16, -1, N'@enabled_for_het_sub', N'true', N'@enabled_for_p2p', N'false')
        return (1)
        END

        select @options_bitfield = @options_bitfield | @OPT_ENABLED_FOR_HET_SUB
    END

    if @allow_initialize_from_backup_id = 1
    begin
        -- Publication must allow immediate sync
        -- (Restriction temporarily lifted)
--        if @immediate_sync_id = 0
--        begin
--            raiserror(18788, 16, -1)
--            return (1)
--        end

        -- Publication cannot allow DTS
        -- (Restriction temporarily lifted)
--        if @allow_dts_id = 1
--        begin
--            raiserror(18789, 16, -1)
--            return (1)
--        end

        -- Publication cannot be a snapshot publication
        if @rfid = 1
        begin
            raiserror(18787, 16, -1)
            return (1)
        end

        -- Publication cannot be a non-SQL Server publication
        if @issqlpublisher = 0
        begin
            raiserror(18790, 16, -1)
            return (1)
        end
    end

    -- Parameter check: @replicate_ddl
    if @issqlpublisher = 1
    BEGIN
        if (@replicate_ddl IS NULL)
            select @replicate_ddl = case when (@options_bitfield & @OPT_ENABLED_FOR_HET_SUB = @OPT_ENABLED_FOR_HET_SUB) then 0 else 1 end
	else if(@replicate_ddl not in (0, 1))
	begin
            raiserror (21544, 16, -1)
            return (1)
	end
END
    ELSE
    BEGIN
        IF @replicate_ddl IS NULL
            select @replicate_ddl = 0

        IF @replicate_ddl != 0
        BEGIN
            -- @replicate_ddl is not supported for heterogeneous publications, must be 0
            raiserror (21632, 16, -1, '@replicate_ddl', '0')
            return (1)
        END
    END

    -- Parameter check: @publish_local_changes_only
    SELECT @publish_local_changes_only = LOWER(@publish_local_changes_only)
    IF @publish_local_changes_only IS NULL OR @publish_local_changes_only NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@publish_local_changes_only')
        RETURN (1)
    END

    if @publish_local_changes_only = N'true'
    begin
        select @options_bitfield = @options_bitfield | @OPT_PUBLISH_ONLY_LOCAL_CHANGES
    end

    -- Parameter check: @p2p_conflictdetection
    SELECT @p2p_conflictdetection = LOWER(@p2p_conflictdetection)
    IF @p2p_conflictdetection IS NULL OR @p2p_conflictdetection NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@p2p_conflictdetection')
        RETURN (1)
    END

   IF @p2p_conflictdetection = N'true'
   BEGIN
        IF @enabled_for_p2p = N'false'
        BEGIN
            -- Parameter N'@p2p_conflictdetection' can only be set to N'true' when '@enabled_for_p2p' is set to N'true'.
            raiserror(21678, 16, -1, N'@p2p_conflictdetection', N'true', N'@enabled_for_p2p', N'true')
            RETURN (1)
        END

        -- Parameter check: @p2p_originator_id
        IF @p2p_originator_id IS NOT NULL AND (@p2p_originator_id = 0 or @p2p_originator_id = 0x80000000)
        BEGIN
              -- Originator ID provided is invalid: 0 or 0x80000000.
              select @strval = cast(@p2p_originator_id as nvarchar)
              raiserror(22806, 16, -1, @strval)
              RETURN (1)
        END

	-- Parameter check: @p2p_continue_onconflict
	SELECT @p2p_continue_onconflict = LOWER(@p2p_continue_onconflict)
	IF @p2p_continue_onconflict IS NULL or @p2p_continue_onconflict NOT IN (N'true', N'false')
	BEGIN
       	RAISERROR (14148, 16, -1, N'@p2p_continue_onconflict')
       	RETURN (1)
	END

        -- Parameter check: @conflict_retention
        if (@conflict_retention IS NULL)
            select @conflict_retention = 14
        else if (@conflict_retention < 0)
        begin
            raiserror(20050, 16, -1, 0)
            return (1)
        end

        -- set the p2p option bits
        select @options_bitfield = @options_bitfield | @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION
        if @p2p_continue_onconflict = N'true'
        	select @options_bitfield = @options_bitfield | @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT	
    END

    /*
    ** Parameter Check:  @allow_partition_switch.
    */
    SELECT @allow_partition_switch = LOWER(@allow_partition_switch)
    IF @allow_partition_switch IS NULL OR @allow_partition_switch NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@allow_partition_switch')
        RETURN (1)
    END
	ELSE
	BEGIN
		if @allow_partition_switch = N'true'
		begin
			-- Default: allow implies replicate
			select @options_bitfield = @options_bitfield | @OPT_ALLOW_PARTITION_SWITCH | @OPT_REPLICATE_PARTITION_SWITCH
		end
		else
			select @options_bitfield = (@options_bitfield & (~@OPT_ALLOW_PARTITION_SWITCH & ~@OPT_REPLICATE_PARTITION_SWITCH))
	END

    /*
    ** Parameter Check:  @replicate_partition_switch.
    */
    SELECT @replicate_partition_switch = LOWER(@replicate_partition_switch)
    IF @replicate_partition_switch is not null and @replicate_partition_switch NOT IN (N'true', N'false')
    BEGIN
        RAISERROR (14148, 16, -1, N'@replicate_partition_switch')
        RETURN (1)
    END
	ELSE
	BEGIN
		if @replicate_partition_switch = N'true'
			select @options_bitfield = @options_bitfield | @OPT_REPLICATE_PARTITION_SWITCH
		else if @replicate_partition_switch = N'false'
			select @options_bitfield = @options_bitfield & ~@OPT_REPLICATE_PARTITION_SWITCH
		-- else null, default don't touch freplicate_partition_switch
	END

	/*
	** Check for conflicts between replicate and allow partition switch
	*/
	if (@options_bitfield & @OPT_REPLICATE_PARTITION_SWITCH = @OPT_REPLICATE_PARTITION_SWITCH) and (@options_bitfield & @OPT_ALLOW_PARTITION_SWITCH != @OPT_ALLOW_PARTITION_SWITCH)
	begin
		raiserror(21678, 16, -1, N'@replicate_partition_switch', N'true', N'@allow_partition_switch', N'true')
		RETURN (1)
	end

    /*
    **  Add publication to syspublications.
    */
    begin tran
    save TRAN sp_addpublication

    if @p2p_conflictdetection = N'true'
    begin
    	declare @numids int
    	select @numids = count(*) from dbo.MSpeer_originatorid_history with (holdlock, tablock, xlock) -- X lock the whole table
	IF @@error <> 0
		GOTO UNDO																		

       if @p2p_originator_id is null
       begin
            EXEC sp_MScheckgenerate_originatorid @publication = @publication, @old_originator_id = 0, @new_originator_id = @p2p_originator_id output
            IF @@error <> 0
			GOTO UNDO
       end
       else
       begin
           DECLARE @tmpid int
           EXEC sp_MScheckgenerate_originatorid @publication = @publication, @old_originator_id = @p2p_originator_id, @new_originator_id = @tmpid output
           IF @@error <> 0
			GOTO UNDO
			
           IF @tmpid = 0
           BEGIN
                -- Originator ID provided is invalid: xxx.
		select @strval = cast(@p2p_originator_id as nvarchar)
                raiserror(22806, 16, -1, @strval)
                GOTO UNDO
           END
       end
  end
  else
  	select @p2p_originator_id = null

    /*
    ** Construct Log Reader agent name.
    */
    select @logreader_exists = 0
            ,@qreader_exists = 0
            ,@isyukonsecuritymodelrequired = sys.fn_yukonsecuritymodelrequired(NULL)
    
    -- (CASE A.)
    -- here we are checking for the case where a user performed the following:
    --		1. Running 8.0 server publish a db (without adding a publication)
    --		2. Upgrade server to 9.0
    --		3. Changes published db compat level to 9.0
    --		4. Add a publication
    -- In 8.0 we would expect the logreader to be created at add publication time. Since the
    -- database has been upgraded and the compat level has changed we must error out and
    -- let the user know what steps to take in order to correct the missing agent issue.
    
    -- (CASE B.) -SQLSERVER ONLY
    -- we also need to check if the logreader/queued agent already exists. If it already
    -- exists then we must skip over the creation regardless of whether or not there
    -- are any publications in this database. this handles the following case:
    --		1. Running 9.0 server publish a db (this creates a logreader job)
    --		2. Change the publish db compat level to 8.0
    --		3. Add a publication
    -- In 8.0 we would create a logreader job in step 3 since it is the first publication.
    -- This is not valid since we already have a logreader agent created in step 1 under
    -- the 9.0 rules.
    
    SELECT @distproc =  QUOTENAME(RTRIM(@distributor)) + N'.' + QUOTENAME(@distribdb) + N'.sys.sp_MSreplagentjobexists'
    EXEC @retcode = @distproc @exists = @logreader_exists output,
                                            @type = 2,
                                            @publisher = @loc_publisher,
                                            @publisher_db = @publisher_db
    IF @@ERROR <> 0 or @retcode <> 0
        GOTO UNDO
    
    -- (CASE A.) In strict security mode we expect the agent jobs to exist (>= 90)
    --      Also, the user is not allowed to specify the @job_name parameters
    --      unless it is sysadmin in which case we can revert to 8.0 mode...
    
    IF @isyukonsecuritymodelrequired = 1
    BEGIN
        IF @logreader_job_name IS NOT NULL
        BEGIN
            -- "The %s parameter(s) have been deprecated from this procedure. The value(s) should now be specified when calling '%s'."
    RAISERROR(21838, 16, -1, N'@logreader_job_name', N'sp_addlogreader_agent')
            GOTO UNDO
        END

        IF @qreader_job_name IS NOT NULL
        BEGIN
            -- "The %s parameter(s) have been deprecated from this procedure. The value(s) should now be specified when calling '%s'."
            RAISERROR(21838, 16, -1, N'@qreader_job_name', N'sp_addqreader_agent')
            GOTO UNDO
        END
    END

    IF @issqlpublisher = 1
    BEGIN
        -- (CASE B.) Make sure that we skip adding the agent if it already exists...
        -- Note that there are more of these same cases below (search for string "_exists")
        
        -- Create logreader task for the first log based or allow queued publication.
        -- We need logreader for queued publication to do idenity range management.
        
        IF @isyukonsecuritymodelrequired = 0
            AND (@rfid = 0 OR @allow_queued_tran_id = 1)
            AND @logreader_exists = 0
            AND @repl_freq != N'snapshot'
        BEGIN
            
            -- If we're attempting to add a new logreader job, we'll first drop
            --   the CDC job if CDC is enabled, as the logreader agent will be doing it's work
            
            if [sys].[fn_cdc_is_db_enabled]() = 1
            begin
                exec @retcode = [sys].[sp_cdc_drop_job] @job_type = N'capture'
                if @retcode <> 0 or @@error <> 0
                    GOTO UNDO
            end

            
            -- Clear repl dbtable fields. This will avoid unnecessary log scan in the
            -- logreader at the first time it runs.
            
            -- sp_droppublication will clear the fields when dropping the last publication,
            -- however, it is after the transaction deleting syspublication table being
            -- committed, thus not guaranteed.
            
            -- We also need this logic for upgraded 7.0 databases where we don't clear
            -- distbackuplsn and distlastlsn fields in unpublishing.
            
			--if cdc is enabled, don't call sp_repldone

			if not exists(select * from sys.databases where db_id() = database_id and is_cdc_enabled = 1)
			begin
            	/* ensure we can get in as logreader */
				exec @retcode = sys.sp_replflush
				IF @@ERROR <> 0 or @retcode <> 0
				    GOTO UNDO

				/* unmark all xacts marked for replication */
				exec @retcode = sys.sp_repldone NULL, NULL, 0, 0, 1
				IF @@ERROR <> 0 or @retcode <> 0
				    GOTO UNDO

				/* release our hold on the db as logreader */
				EXEC @retcode = sys.sp_replflush
				IF @@ERROR <> 0 or @retcode <> 0
				    GOTO UNDO
	
				-- Run checkpoint to make sp_repldone result durable (write repl dbtable fields
				-- into the checkpoint record).
				checkpoint
				IF @@ERROR <> 0
					GOTO UNDO
			end

            select @job_existing = case when (@logreader_job_name is null) then 0 else 1 end
            /*
            ** Schedule Log Reader agent for the database
            ** If @logreader_job_name is not null
            */
            SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + N'.dbo.sp_MSadd_logreader_agent'
            EXECUTE @retcode = @distproc
                @name = @logreader_job_name,
                @publisher = @srvname,
                @publisher_db = @publisher_db,
                @publication = N'ALL',
                @local_job = 1,
                @job_existing = @job_existing,
                @internal = N'YUKON',
                @publisher_engine_edition = @publisher_engine_edition

            IF @@ERROR <> 0 or @retcode <> 0
                GOTO UNDO

            -- Warning: The logreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.
            RAISERROR(21452, 10, -1, N'logreader')

            SELECT @logreader_exists = 1
        END --IF @isyukonsecuritymodelrequired = 0 AND  ... @repl_freq != N'snapshot'

        INSERT dbo.syspublications(description, name, repl_freq,
                               status, sync_method, snapshot_jobid, independent_agent,
                               immediate_sync, enabled_for_internet,
                               allow_push, allow_pull, allow_anonymous, immediate_sync_ready,
                               -- SyncTran
                               allow_sync_tran, autogen_sync_procs, retention, allow_queued_tran,
                               snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script,
                               post_snapshot_script, compress_snapshot, ftp_address, ftp_port,
                               ftp_subdirectory, ftp_login, ftp_password, allow_dts,
                               allow_subscription_copy, centralized_conflicts, conflict_retention,
                               conflict_policy, queue_type, backward_comp_level, allow_initialize_from_backup,
                               min_autonosync_lsn, replicate_ddl, options, originator_id)

        VALUES (@description, @publication, @rfid, @statid, @smid, NULL,
                @independent_agent_id,
                @immediate_sync_id, @enabled_for_internet_id, @allow_push_id,
                @allow_pull_id, @allow_anonymous_id, 0,
                -- SyncTran
                @allow_sync_tran_id, @autogen_sync_procs_id, @retention, @allow_queued_tran_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,
                @allow_dts_id, @allow_subscription_copy_id, @centralized_conflicts_bit, @conflict_retention,
                @conflict_policy_id, @queue_type_val, @backward_comp_level, @allow_initialize_from_backup_id,
                NULL, @replicate_ddl, @options_bitfield, @p2p_originator_id)

        IF @@ERROR <> 0
        BEGIN
            RAISERROR (14018, 16, -1)
            GOTO UNDO
        END

        SELECT @pubid = scope_identity()

	if @p2p_conflictdetection = N'true'
	begin
	        exec @retcode = sys.sp_MSgetdbversion @current_version = @current_db_version output
       	 IF @@error <> 0 or @retcode <> 0
            		goto UNDO

		--do not reply on sp_MSaddpeerlsn (called later), which is in a different transaction (which is not good I think);
		--sp_MSaddpeerlsn called later will try to insert the peer ID again, just to find that the ID is already inserted
		--into MSpeer_originatorid_history
       	insert dbo.MSpeer_originatorid_history
	       	 (originator_publication, originator_id, originator_node, originator_db, originator_db_version, originator_version)
		values(@publication, @p2p_originator_id, UPPER(publishingservername()), DB_NAME(), @current_db_version, @@microsoftversion)

		 IF @@ERROR <> 0
	        BEGIN
       	     RAISERROR (14018, 16, -1)
	            GOTO UNDO
       	 END
        end

        -- If this publication is enabled for autonosync, set the initial
        -- min. subscription autonosync lsn
        IF @allow_initialize_from_backup_id = 1
        BEGIN
            EXEC @retcode = sys.sp_MSincrementpublicationminautonosynclsn @publication = @publication
            IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO
        END
    END -- SQL publisher type
    ELSE
    BEGIN -- HREPL publisher type
        -- Make sure that this is the first transactional publication
        -- for this heterogeneous publisher

        -- only allow the addition of logreader agent w/o proxy if SYSADMIN or in 8.0 sec model
        IF @isyukonsecuritymodelrequired = 0
            and @logreader_exists = 0
            and @rfid = 0
        BEGIN
            
            -- If we're attempting to add a new logreader job, we'll first drop
            --   the CDC job if CDC is enabled, as the logreader agent will be doing it's work
            
            if [sys].[fn_cdc_is_db_enabled]() = 1
            begin
                exec @retcode = [sys].[sp_cdc_drop_job] @job_type = N'capture'
                if @retcode <> 0 or @@error <> 0
                    GOTO UNDO
            end

            -- For heterogeneous publishers, add the logreader job
            select @job_existing = case when (@logreader_job_name is null) then 0 else 1 end

            /*
            ** Schedule Log Reader agent for the database
            ** If @logreader_job_name is not null
            */
            SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + N'.' + QUOTENAME(@distribdb) + N'.dbo.sp_MSadd_logreader_agent'
            EXECUTE @retcode = @distproc
                                                @name = @logreader_job_name,
                                                @publisher = @publisher,
                                                @publisher_db = @publisher_db,
                                                @publication = N'ALL',
                                                @local_job = 1,
                                                @job_existing = @job_existing,
                                                @publisher_type = @publisher_type,
                                                @internal = N'YUKON',
                                                @publisher_engine_edition = @publisher_engine_edition

            IF @@ERROR <> 0 or @retcode <> 0
                GOTO UNDO

            -- Warning: The logreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.
            RAISERROR(21452, 10, -1, N'logreader')
            SELECT @logreader_exists = 1
        END
    END -- HREPL publisher type

    -- make sure we have added the logreader agent
    IF @logreader_exists = 0
        AND @repl_freq != N'snapshot'
    BEGIN
        -- The 'logreader' agent job must be added via 'sp_addlogreader_agent' before continuing. Please see the documentation for 'sp_addlogreader_agent'.
        RAISERROR(21798, 16, -1, N'logreader', N'sp_addlogreader_agent', N'sp_addlogreader_agent')
        GOTO UNDO
    END
    
    -- Queued specific processing
    
    IF @allow_queued_tran_id = 1
    BEGIN
        -- Check if Queue reader agent has been created
        SELECT @distproc =  QUOTENAME(RTRIM(@distributor)) + N'.' + QUOTENAME(@distribdb) + N'.sys.sp_MSreplagentjobexists'
        EXEC @retcode = @distproc @exists = @qreader_exists output,
                                                @type = 4
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

        -- only allow the addition of qreader agent w/o proxy if SYSADMIN or in 8.0 sec model
        IF @isyukonsecuritymodelrequired = 0
        BEGIN
            IF @qreader_exists = 0 and @qreader_job_name is not null
            BEGIN
                
                -- Schedule Queue Reader agent for the database
                
                SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + N'.' + QUOTENAME(@distribdb) + N'.dbo.sp_MSadd_qreader_agent'
                EXECUTE @retcode = @distproc @name = @qreader_job_name
                IF @@ERROR <> 0 or @retcode <> 0
                    GOTO UNDO

                -- Warning: The qreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.
                RAISERROR(21452, 10, -1, N'qreader')
                SELECT @qreader_exists = 1
            END
        END -- @isyukonsecuritymodelrequired = 0
        ELSE
        BEGIN
            -- make sure we have added the qreader agent
            IF @qreader_exists = 0
            BEGIN
                -- The 'qreader' agent job must be added via 'sp_addqreader_agent' before continuing. Please see the documentation for 'sp_addqreader_agent'.
                RAISERROR(21798, 16, -1, N'qreader', N'sp_addqreader_agent', N'sp_addqreader_agent')
                GOTO UNDO
            END
        END -- @isyukonsecuritymodelrequired = 1
    END -- IF @allow_queued_tran_id = 1
    /*
    ** Add the publication to the distributor side
    */
    SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + N'.' + QUOTENAME(@distribdb) + N'.dbo.sp_MSadd_publication'
    EXECUTE @retcode =   @distproc
                                    @publisher = @loc_publisher,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @publication_type = @rfid,
                                    @independent_agent = @independent_agent_id,
                                    @immediate_sync = @immediate_sync_id,
                                    @allow_push = @allow_push_id,
                                    @allow_pull = @allow_pull_id,
                                    @allow_anonymous = @allow_anonymous_id,
                                    @description = @description,
                                    @retention = @retention,
                                    @sync_method = @smid,
                                    @allow_subscription_copy = @allow_subscription_copy_id,
                                    @allow_queued_tran = @allow_queued_tran_id,
                                    @queue_type = @queue_type_val,
                                    @publisher_type = @publisher_type,
                                    @options = @options_bitfield,
                                    @publisher_engine_edition = @publisher_engine_edition,
                                    @allow_initialize_from_backup = @allow_initialize_from_backup_id
    IF @@ERROR <> 0 or @retcode <> 0
    GOTO UNDO

    
 
Last revision 2008RTM
See also

  sp_addpublication (Procedure)
sp_addpublication_snapshot (Procedure)
sp_IHaddpublication (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_addpublication_snapshot (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