Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddinitialpublication

  No additional text.


Syntax

create procedure sys.sp_MSaddinitialpublication(
    @publisher              sysname,
    @publisher_db           sysname,
    @publication            sysname,            /* Name of the publication */
    @description            nvarchar(255),      /* Description of the publication */
    @pubid                  uniqueidentifier,   /* Publication ID */
    @retention              int,                /* Retention period of the publication */
    @sync_mode              int,                /* Sync mode of the publication */
    @allow_push             int,                /* does publication allow push ? */
    @allow_pull             int,                /* does publication allow pull ? */
    @allow_anonymous        int,                /* does publication allow anonymous ? */
    @conflict_logging       int,                /* publication does centralized conflicts ? */
    @status                 int,                /* publication's status */
    @snapshot_ready         int,                /* publication snapshto_ready flag ? */
    @enabled_for_internet   int,                /* publication enabled_for_internet flag ? */
    @publication_type       int,                /* a full publication or a partial one */
    @conflict_retention     int = 60,               /* the retention period for conflict table */
    @allow_subscription_copy int = 0,               /* does publication allow subscription copies to sync ? */
    @allow_synctoalternate  int = 0,                /* does publication allow subscription to sync to alternates ? */
    @backward_comp_level    int = 10,            /* default to 7.0 server */
    @replicate_ddl                      int  = 0,                       /* by default no ALTER TABLE event to catch */
    @retention_period_unit      tinyint = 0,
    @replnickname                       binary(6) = NULL,
    @generation_leveling_threshold int = NULL,
    @automatic_reinitialization_policy bit = 0 -- no upload first
    ) AS

    SET NOCOUNT ON
    declare @retcode            int
    declare @subid              uniqueidentifier
    declare @REPOLEVersion_90   int
    declare @subscriber_db                              sysname

    select @publication = RTRIM(@publication)
    select @publisher_db = RTRIM(@publisher_db)
    set @REPOLEVersion_90= 90
    set @subscriber_db= db_name()

    /*
    ** Check for subscribing permission
    */
    exec @retcode=sys.sp_MSreplcheck_subscribe
    if @retcode<>0 or @@ERROR<>0 return (1)

   -- The database cannot subscribe to both Yukon and pre-Yukon publications.
   if @REPOLEVersion_90 = @backward_comp_level and
      exists (select * from dbo.sysmergepublications where
                   backward_comp_level < @REPOLEVersion_90 and
                   (upper(publisher collate SQL_Latin1_General_CP1_CS_AS) <> upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) or
                        publisher_db <> @publisher_db or
                        name <> @publication))
    begin
       raiserror(21527, 16, -1, @publication, @subscriber_db)
       return (1)
    end
    else if @REPOLEVersion_90 > @backward_comp_level and
      exists (select * from dbo.sysmergepublications where
                   backward_comp_level >= @REPOLEVersion_90 and
                   status is not null and       -- Pull subscribers have dummy backward_comp_level until they sync the first time. Skip those.
                   (upper(publisher collate SQL_Latin1_General_CP1_CS_AS) <> upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) or
                        publisher_db <> @publisher_db or
                        name <> @publication))
    begin
       raiserror(21528, 16, -1, @publication, @subscriber_db)
       return (1)
   end

    /*
    ** Populate the local copy of dbo.sysmergepublications
    */
    BEGIN TRAN
    save TRAN MSaddinitialpublication

    -- this proc gets called when we are adding the publication on the subscriber when a merge for
    -- this subscriber with the given publication happens for the first time. However, we could have
    -- a publication entry already if we know about this publication as a "global replica". To understand
    -- why a publication entry could already exists check sp_MScreateglobalreplica where we create a
    -- sysmergepublications entry for each publication replica (pubid = subid) we see
    -- however, this subscriber could be subscribing to this publication for the first time.
    if exists (select * from dbo.sysmergepublications
            where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db)
    begin
        declare @pubid_local uniqueidentifier

        /* select the local pubid only if it has a valid parentid */
        select @pubid_local = pubid from dbo.sysmergepublications
            where name = @publication and
                UPPER(publisher)=UPPER(@publisher) and
                publisher_db=@publisher_db
        if @pubid_local <> @pubid
        begin
            if exists (select * from dbo.sysmergesubscriptions
                where pubid = @pubid and
                UPPER(subscriber_server) collate database_default = UPPER(@publisher) and db_name = @publisher_db)
            begin
                select @subid = subid from dbo.sysmergesubscriptions
                    where pubid = @pubid and UPPER(subscriber_server) collate database_default = UPPER(@publisher) and db_name = @publisher_db
                delete from dbo.MSmerge_replinfo where repid = @subid

				-- delete supportability settings for the subscriptions that we are about to delete.
			    delete from dbo.MSmerge_supportability_settings where subid = @subid       	
			    delete from dbo.MSmerge_log_files where subid = @subid
			
                delete from dbo.sysmergesubscriptions where subid = @subid
                IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14057, 16, -1)
                    goto FAILURE
                END

                                exec sys.sp_MScleanup_subscriber_history @subid=@subid
                                if @@ERROR<>0
                                        goto FAILURE

                -- If the only remaining subscriptions are old entries (before restore),
                -- we remove them now.
                if not exists (select * from dbo.sysmergesubscriptions
                                                        where status <> 7) -- REPLICA_STATUS_BeforeRestore
                begin
                        delete from dbo.sysmergesubscriptions
                        truncate table dbo.MSmerge_supportability_settings
                        truncate table dbo.MSmerge_log_files
                        truncate table dbo.MSrepl_errors
                                        truncate table dbo.MSmerge_history
                                        truncate table dbo.MSmerge_articlehistory
                                        truncate table dbo.MSmerge_sessions
                        delete from dbo.MSmerge_replinfo
                end
            end
        end
        update dbo.sysmergesubscriptions set pubid= @pubid, replicastate= newid() where pubid = @pubid_local
        IF @@ERROR <> 0
            BEGIN
                RAISERROR (14057, 16, -1)
                goto FAILURE
            END
        if @pubid <> @pubid_local
        begin
			-- delete supportability settings for the subscriptions that we are about to delete.
		    delete from dbo.MSmerge_supportability_settings where subid = @pubid       	
		    delete from dbo.MSmerge_log_files where subid = @pubid

            delete from dbo.sysmergesubscriptions where subid = @pubid
            exec sys.sp_MScleanup_subscriber_history @subid=@pubid
                        if @@ERROR<>0
                                goto FAILURE
            delete from dbo.MSmerge_replinfo where repid = @pubid

            -- If the only remaining subscriptions are old entries (before restore),
            -- we remove them now.
            if not exists (select * from dbo.sysmergesubscriptions
                                                where status <> 7) -- REPLICA_STATUS_BeforeRestore
            begin
                delete from dbo.sysmergesubscriptions
                truncate table dbo.MSmerge_supportability_settings
                truncate table dbo.MSmerge_log_files
                truncate table dbo.MSrepl_errors
                                truncate table dbo.MSmerge_history
                                truncate table dbo.MSmerge_articlehistory
                                truncate table dbo.MSmerge_sessions
                delete from dbo.MSmerge_replinfo
            end
        end

        update dbo.sysmergesubscriptions SET subid = @pubid, replnickname = isnull(@replnickname, replnickname) where subid = @pubid_local
        IF @@ERROR <> 0
        BEGIN
            RAISERROR (14057, 16, -1)
            goto FAILURE
        END
        delete from dbo.MSmerge_replinfo where repid = @pubid_local
        IF @@ERROR <> 0
        BEGIN
            RAISERROR (14057, 16, -1)
            goto FAILURE
        END
        update dbo.sysmergepublications
            SET pubid = @pubid,
                name = @publication,
                description = @description,
                designmasterid = @pubid,
                retention = @retention,
                retention_period_unit = @retention_period_unit,
                parentid = pubid,
                sync_mode = sync_mode,
                allow_push = @allow_push,
                allow_pull = @allow_pull,
                allow_anonymous = @allow_anonymous,
                centralized_conflicts= case @conflict_logging
                   when 1 then 1
                   when 2 then 1
                   else 0
                end,
                decentralized_conflicts= case @conflict_logging
                   when 1 then 0
                   when 2 then 1
                   else 1
                end,
                status = @status,
                snapshot_ready = @snapshot_ready,
                enabled_for_internet = @enabled_for_internet,
                publication_type = @publication_type,
                conflict_retention = @conflict_retention,
                allow_subscription_copy = @allow_subscription_copy,
                allow_synctoalternate = @allow_synctoalternate ,
                backward_comp_level = @backward_comp_level,
                replicate_ddl=@replicate_ddl,
                generation_leveling_threshold = @generation_leveling_threshold,
                automatic_reinitialization_policy = @automatic_reinitialization_policy
            where name = @publication
                  and UPPER(publisher) = UPPER(@publisher)
                  and publisher_db = @publisher_db

        	-- Fix the replnick to 4 bytes + 0x0000 if the compatlevel is less than 80.
        	-- This prevents us from having real 6 byte replnicks when there are 80 subscribers
        	-- Having real 6 byte replnicks in mixed mode can cause non-convergence because the
        	-- lineages can not be converted from 90 to 80 and back to 90 without losing last two bytes of replnick.
			if @REPOLEVersion_90 > @backward_comp_level
			begin
				update dbo.sysmergesubscriptions set replnickname = substring(replnickname,1,4) + 0x0000
					where pubid <> subid and pubid in (select pubid from sysmergepublications where name = @publication
    					              and UPPER(publisher) = UPPER(@publisher) and publisher_db = @publisher_db)
			end

    end
    else
    begin
        insert dbo.sysmergepublications(publisher, publisher_db,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,
            conflict_retention, allow_subscription_copy, allow_synctoalternate, backward_comp_level, replicate_ddl, distributor,
            retention_period_unit, generation_leveling_threshold, automatic_reinitialization_policy)
        values(@publisher, @publisher_db, @pubid, @publication, @description, @pubid,
            @retention, @pubid, @sync_mode, @allow_push, @allow_pull, @allow_anonymous,
            case @conflict_logging -- centralized
               when 1 then 1
               when 2 then 1
               else 0
            end,
            case @conflict_logging -- decentralized
               when 1 then 0
               when 2 then 1
               else 1
            end,
            @status, @snapshot_ready, @enabled_for_internet, @publication_type,
            @conflict_retention, @allow_subscription_copy, @allow_synctoalternate, @backward_comp_level, @replicate_ddl, @publisher,
            @retention_period_unit, @generation_leveling_threshold, @automatic_reinitialization_policy)
    end
    IF @@ERROR <> 0
        BEGIN
            RAISERROR (14057, 16, -1)
            goto FAILURE
        END
    COMMIT TRAN

    RETURN (0)

FAILURE:
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION MSaddinitialpublication
        COMMIT TRANSACTION
    end
    RETURN (1)

 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (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