Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addsubscription_article

  No additional text.


Syntax
create procedure sys.sp_MSrepl_addsubscription_article
(
    @publication					sysname,
    @article						sysname,
    @subscriber						sysname,
    @destination_db					sysname,
    @sync_type						nvarchar(80),
    @status							sysname,
    @subscription_type				nvarchar(4),
    @update_mode					nvarchar(30),
    @loopback_detection				nvarchar(5),
    @frequency_type					int,
    @frequency_interval				int,
    @frequency_relative_interval	int,
    @frequency_recurrence_factor	int,
    @frequency_subday				int,
    @frequency_subday_interval		int,
    @active_start_time_of_day		int,
    @active_end_time_of_day			int,
    @active_start_date				int,
    @active_end_date				int,
    @optional_command_line			nvarchar(4000),
    @reserved						nvarchar(10),
    @enabled_for_syncmgr			nvarchar(5),
    @dts_package_name				sysname,
    @dts_package_password			sysname,
    @dts_package_location			nvarchar(12),
    @distribution_job_name			sysname,
    @publisher					sysname,
    @publisher_type 				sysname
    -- expose -SubscriptionStreams functionality
    ,@subscriptionstreams tinyint	-- values should fall between 1 and 64
    ,@subscriber_type				tinyint
    ,@pubid							int
    ,@non_sql_flag					bit
    ,@publisher_local				sysname
    ,@immediate_sync				bit
    ,@srvid							smallint
    ,@independent_agent_id          bit
    ,@dts_package_location_id		int
    ,@immediate_sync_ready			bit
    ,@sync_method					tinyint
    ,@subscription_type_id			int
    ,@pubstatus						tinyint
    ,@allow_anonymous				bit
    ,@update_mode_id				tinyint
)
AS
BEGIN

    SET NOCOUNT ON

    -- Declarations.
    DECLARE @distproc nvarchar(1000)
    DECLARE @retcode int
    DECLARE @artid int
    DECLARE @pre_creation_cmd tinyint
    DECLARE @none tinyint
    DECLARE @automatic tinyint
    DECLARE @manual tinyint
    DECLARE @inactive tinyint
    DECLARE @subscribed tinyint
    DECLARE @active tinyint
    DECLARE @subscriber_bit smallint
    DECLARE @truncate tinyint
    DECLARE @char_bcp tinyint
    DECLARE @concurrent tinyint
    DECLARE @concurrent_char tinyint
    DECLARE @virtual_id smallint
    DECLARE @internal nvarchar(10)
    DECLARE @sync_typeid tinyint
    DECLARE @loopback_detection_id bit
    DECLARE @dts_package_enc_password nvarchar(524)
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @publisher_db sysname
    DECLARE @distribution_jobid binary(16)
	
    SELECT @none = 2            /* Const: synchronization type 'none' */
    SELECT @automatic = 1       /* Const: synchronization type 'automatic' */
    SELECT @manual = 0          /* Const: synchronization type 'manual' */
    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscribed = 1        /* Const: subscription status 'subscribed' */
    SELECT @active = 2        /* Const: subscription status 'arctive' */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @truncate = 3    /* Const: truncate pre-creation command */
    SELECT @char_bcp = 1    /* Const: character bcp sync method */
    SELECT @concurrent = 3  /* Const: concurrent sync method */
    SELECT @concurrent_char = 4  /* Const: concurrent char mode sync method */
    SELECT @virtual_id = -1 /* Const: virtual subscriber id */
    SELECT @internal = 'internal' /* Const: Flag of calling internally from system */
                                  /* stored procedures     */
    SELECT @publisher_db = DB_NAME()

	IF sys.fn_yukonsecuritymodelrequired(NULL) = 1
	BEGIN
		-- Set defaults for these AFTER ALL RECURSIVE Calls when in 9.0 model
	   	SELECT @enabled_for_syncmgr	= ISNULL(@enabled_for_syncmgr, N'false')
	END
	
    /* After 'all' being expanded, check to make sure that the article exists,
    ** is not NULL, and conforms to the rules for identifiers.
    */
    /*
    EXECUTE @retcode = sys.sp_validname @article
    IF @retcode <> 0
    RETURN (1)
    */

    declare @dest_owner sysname

    SELECT @artid = artid, @pre_creation_cmd = pre_creation_cmd,
        @dest_owner = dest_owner
    FROM sysextendedarticlesview
    WHERE name = @article
    AND pubid = @pubid

    IF NOT EXISTS (SELECT *
                             FROM sysextendedarticlesview
                            WHERE artid = @artid
                              AND pubid = @pubid)
        BEGIN
            RAISERROR (20027, 11, -1, @article)
            RETURN (1)
        END


    /*
    ** If the subscriber is an ODBC DSN, do not allow subscriptions to
    ** articles with a "truncate" pre_creation_cmd.
    */
    IF @non_sql_flag <> 0 AND @pre_creation_cmd = @truncate
        BEGIN
            RAISERROR (14094, 16, -1, @article, @subscriber)
            RETURN (1)
        END

   /*
   ** Parameter Check: @sync_type.
   ** Set sync_typeid based on the @sync_type specified.
   **
   **   sync_typeid     sync_type
   **   ===========     =========
   **             0     manual
   **             1     automatic
   **             2     none
   */


   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('automatic', 'manual', 'none', 'autonosync', 'autonosync with backup', 'autonosync with lsn')
       BEGIN
           RAISERROR (14052, 16, -1)
           RETURN (1)
       END

   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'manual'
       BEGIN
           RAISERROR (14123, 16, -1)
           RETURN (1)
       END


   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'automatic'
   BEGIN
        SELECT @sync_typeid = @automatic
   END
   ELSE
   BEGIN
        SELECT @sync_typeid = @none
   END


    /*
    ** Parameter Check: @status
    ** If the publication is immediate_sync type and sync_type is automatic
    ** the status has to be NULL.
    ** Note for 6x backward compatibility, don't do the check for non immediate_sync
    ** publication
    */
    IF @immediate_sync = 1 and @sync_typeid = @automatic AND
        @status IS NOT NULL
    BEGIN
          RAISERROR (14129, 16, -1)
          RETURN (1)
    END

    /*
    ** Parameter Check:  @loopback_detection
    */
    IF @loopback_detection is not null and LOWER(@loopback_detection collate SQL_Latin1_General_CP1_CS_AS)
        NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@loopback_detection')
        RETURN (1)
    END

    IF  LOWER(@loopback_detection collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @loopback_detection_id = 1
    ELSE IF LOWER(@loopback_detection collate SQL_Latin1_General_CP1_CS_AS) = 'false'
        SELECT @loopback_detection_id = 0
    ELSE
    begin
        -- if the subscriber is SQL Server and @loopback_detection is null, by default we will enable it
		IF @non_sql_flag = 0
			select @loopback_detection_id = 1
			
        -- if the subscriber is not SQL Server and @loopback_detection is null, by default we will disable it
		else
			select @loopback_detection_id = 0
			
        /*
        -- turn on loopback detection for sync and queued
        if @update_mode_id in (1,2,3,4,5,6,7)
            select @loopback_detection_id = 1
        else
            select @loopback_detection_id = 0
        */
    end

    select @dts_package_enc_password = @dts_package_password
    if @dts_package_password is not null
    begin
        EXEC @retcode = sys.sp_MSreplencrypt @dts_package_enc_password OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            return 1
    end

    /*
    ** Do special things for DSN subscribers.
    */
    IF @subscriber IS NOT NULL AND @non_sql_flag <> 0
    BEGIN
        --IF EXISTS ( select * from sysextendedarticlesview sa, syspublications sp
                    --where sa.pubid = sp.pubid
                    --and sp.name = @publication
       --and sa.name = @article
                    --and ( ins_cmd like '%call%' or upd_cmd like '%call%' or del_cmd like '%call%' ) )
        --BEGIN
            --RAISERROR(21051, 16, -1, @subscriber)
            --RETURN (1)
        --END

        declare @art_status tinyint
		SELECT	@art_status = sa.status
		FROM	sysarticles sa,
				syspublications sp
		WHERE	sa.pubid = sp.pubid
		  AND	sa.name  = @article
		  AND	sa.pubid = @pubid

        -- OLEDB or ODBC subscribers can not subscriber to article with subscriber managed
        -- timestamp column
        if @art_status & 32 = 32
        begin
            raiserror(21249, 16, -1, @article, @publication)
            return (1)
        end
    END

    
    -- For updating subscriptions (immediate)
    -- Check if there exists an subscription to the same
    -- dest_db which contain at least one article which has
    -- the same source_table as the current article 
    -- If yes then raise a warning
    -- NOTE : this restriction is due to the fact that loopback
    -- detection happens at database level and hence for updating
    -- subscriptions, we can lose the updates made in one subscription
    -- w.r.t. the other subscription
    
    if (@update_mode_id = 1)
    begin
        if exists (select * from dbo.syssubscriptions
            where srvid = @srvid and dest_db = @destination_db and artid in
                (select artid from dbo.sysarticles
                    where objid = (select objid from sysarticles where artid = @artid)))
        begin
            raiserror(21293, 10, 1, @article, @destination_db)
        end
    end

    /*
    ** Add subscription to syssubscriptions
    */
    begin tran
    save TRAN addsubscription
    /*
    ** If no subscription exists, add it to syssubscriptions.
    */
    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions s
                    WHERE (@subscriber IS NULL and s.srvid = @srvid or (@subscriber IS not NULL) and s.srvname = UPPER(@subscriber))
                      AND artid = @artid
                      AND dest_db = @destination_db )--or @non_sql_flag <> 0))
        BEGIN
       INSERT syssubscriptions (artid,
                                    srvid,
                                    dest_db,
                                    login_name,
                                    status,
                                    sync_type,
                                    subscription_type,
                                    distribution_jobid,
                                    update_mode,
                                    loopback_detection,
                                    queued_reinit
                                    ,srvname
                                    )
       VALUES (@artid,
                   isnull(@srvid,0),
                   @destination_db,
                   suser_sname(suser_sid()),
                   @inactive,
                   @sync_typeid,
                   @subscription_type_id,
                   0,
                   @update_mode_id,
                   @loopback_detection_id,
                   1
                   ,isnull(UPPER(@subscriber), N'')
                   )

       IF @@ERROR <> 0
           BEGIN
                RAISERROR (14057, 16, -1)
                goto UNDO
           END
        END
    ELSE
       BEGIN
          RAISERROR (14058, 16, -1)
          goto UNDO
       END

    /*
    ** If the @status was not provided determine the default value.
    ** If the @sync_type = 'none' then the subscription defaults to 'active'.
    ** Else the subscription defaults to 'subscribed'.
    */
    IF @status IS NULL
    BEGIN
        IF @sync_typeid = @none
            SELECT @status = 'active'
        ELSE
            SELECT @status = 'subscribed'
    END


    /*
    ** Set publication subscription status.
    */
    -- NOTE:  Internally, we can call sp_MSrepl_changesubstatus rather than
    --        sp_changesubstatus. In all cases, we are already executing in
    --        the correct database context.  Calling sp_changesubstatus is not
    --        needed to possibly force a context change for heterogeneous publishers.
    EXEC @retcode = sys.sp_MSrepl_changesubstatus
    @publication = @publication,
    @article     = @article,
    @subscriber  = @subscriber,
    @status      = @status,
    @destination_db = @destination_db,
    @frequency_type  = @frequency_type,
    @frequency_interval  = @frequency_interval,
    @frequency_relative_interval  = @frequency_relative_interval,
    @frequency_recurrence_factor  = @frequency_recurrence_factor,
    @frequency_subday  = @frequency_subday,
    @frequency_subday_interval  = @frequency_subday_interval,
    @active_start_time_of_day  = @active_start_time_of_day,
    @active_end_time_of_day  = @active_end_time_of_day,
    @active_start_date  = @active_start_date,
    @active_end_date  = @active_end_date,
    @optional_command_line = @optional_command_line,
    @distribution_jobid = @distribution_jobid OUTPUT,
    @dts_package_name = @dts_package_name,
    @dts_package_password = @dts_package_enc_password,
    @dts_package_location = @dts_package_location_id,
    @distribution_job_name = @distribution_job_name,
    @publisher = @publisher,
    @publisher_type = @publisher_type

    IF @@error <> 0 OR @retcode <> 0
    BEGIN
       RAISERROR (14057, 16, -1)
		goto UNDO
    END

    UPDATE syssubscriptions SET
        distribution_jobid = @distribution_jobid where
        artid = @artid AND
        srvname = UPPER(@subscriber) AND
        dest_db = @destination_db

    IF @@error <> 0
    BEGIN
		goto UNDO
    END

    -- For heterogeneous subscribers, the MSdistribution_agents table needs to be updated with the correct
    -- subscriber type.   	
   	IF @subscriber_type <> 0
	BEGIN	
   		-- Update the corresponding entry in MSdistribution_agents table with the
   		-- subscriber type.

		EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher_local,
													@rpcsrvname		= @distributor OUTPUT,
													@distribdb		= @distribdb   OUTPUT

		IF @@error <> 0 OR @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
		BEGIN
			RAISERROR (14071, 16, -1)
			goto UNDO
		END
		
		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.sys.sp_MSchange_distribution_agent_properties'	
		EXEC @retcode = @distproc @publisher		= @publisher_local,
									@publisher_db	= @publisher_db,
									@publication 	= @publication,
									@subscriber 	= @subscriber,
									@subscriber_db	= @destination_db,
									@property		= N'subscriber_type',
									@value			= @subscriber_type
		IF @@error <> 0 OR @retcode <> 0
		BEGIN
			-- Unable to update subscriber_type in MSdistribution_agents table.
			RAISERROR (14045, 16, -1)
			goto UNDO
		END
	END	

	
	--by now there should be a corresponding entry in MSdistribution_agents table
	--let's see if we need to change the subscriptionstreams property
	
	if (@subscriptionstreams is not null)
	BEGIN
		declare @strStreams varchar(12)
		
		set @strStreams = cast(@subscriptionstreams as varchar(12))

		-- Subscriptions streams now supported for Oracle publishing
		--IF NOT @publisher_type = N'MSSQLSERVER'
		--begin
		--	RAISERROR (21634, 16, -1, '@subscriptionstreams', @strStreams, 'NULL or 0')
		--	goto UNDO
		--end

		if (@subscriptionstreams < 1 or @subscriptionstreams > 64)
		begin
			RAISERROR(14198, 16, -1, '@subscriptionstreams', '1..64')
			goto UNDO
		end

		
		EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher_local,
													@rpcsrvname		= @distributor OUTPUT,
													@distribdb		= @distribdb   OUTPUT

		IF @@error <> 0 OR @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
		BEGIN
			RAISERROR (14071, 16, -1)
			goto UNDO
		END

		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.sys.sp_MSchange_distribution_agent_properties'	
		EXEC @retcode = @distproc @publisher		= @publisher_local,
									@publisher_db	= @publisher_db,
									@publication 	= @publication,
									@subscriber 	= @subscriber,
									@subscriber_db	= @destination_db,
									@property		= N'subscriptionstreams',
									@value			= @strStreams
		IF @@error <> 0 OR @retcode <> 0
		BEGIN
			RAISERROR (14071, 16, -1)
			goto UNDO
		END
	END

    /*
    ** If possible, activate the real subscriptions on immediate_sync publication
    ** immediately. Also, activate the virtual subscriptions on
    ** anonymous publications immediately.
    ** We change the subscription status from 'subscribed' to 'active' so that
    ** sp_MSupdate_subscription will be called, which will set the subscription's
    ** xactid_ts to the snapshot xactid_ts of virtual subscriptions. This means that
    ** we have to call sp_changesubstatus again here. We can not combine two calls
    ** into ONE !!!
    **
    ** Activate the subscription immediately if
    ** 1. The publication is immediate_sync type
    ** 2. sync_type is 'automatic'
    ** AND
    ** 1. The subscription is real
    ** 2. The snapshot has completed once
    ** 3. The subscription is the last subscription added to the publication (subscription for
    ** the last article). This is to guarantee the subscription status of all the articles
    ** in the publication be activate in one transaction at the distributor. This is
    ** to prevent the distribution agent from picking up partial subscriptions.
    ** Note that this SP will be called with @article = 'all'
    ** OR
    ** 1. The publication is active
    ** 2. The publication is allow_anonymous
    ** 3. The subscription is virtual
    **
    */

    IF  @sync_typeid = @automatic AND @immediate_sync = 1 AND

        ((@srvid <> @virtual_id AND
        @immediate_sync_ready = 1 AND
        NOT EXISTS (select * from sysextendedarticlesview art where
                    art.pubid = @pubid and
                    not exists (select * from syssubscriptions sub
                        where sub.artid = art.artid and
                              sub.srvname = UPPER(@subscriber) and
                              ((@destination_db = N'%') OR (sub.dest_db = @destination_db))))) OR

        (@pubstatus = 1 and @srvid = @virtual_id and @allow_anonymous = 1))
    BEGIN
        DECLARE @article_ex sysname
        IF @srvid <> @virtual_id
            SELECT @article_ex = '%'
        ELSE
            SELECT @article_ex = @article

        /*
        ** Set publication subscription status.
        */
        EXEC @retcode = sys.sp_MSrepl_changesubstatus
        @publication = @publication,
        @article     = @article_ex,
        @subscriber  = @subscriber,
        @status      = 'active',
        @destination_db = @destination_db,
        @publisher = @publisher,
        @publisher_type = @publisher_type
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN  addsubscription
                commit tran
            end
           RAISERROR (14057, 16, -1)
           RETURN (1)
        END
    END

    /*
    ** If the virtual subscriptions have been initiated for the
    ** "first" time, try initiating this "real" subscription as well.
    */
    if @immediate_sync = 1 and @sync_typeid = @automatic and @sync_method in (3,4) and
       (@srvid <> @virtual_id and
        @immediate_sync_ready <> 1 and
        not exists
            (select *
               from sysextendedarticlesview art
              where art.pubid = @pubid
            and exists
                    (select *
                       from syssubscriptions sub
                      where sub.artid = art.artid
                        and sub.srvid = @virtual_id
                        and sub.status <> 3)) and  -- All virtual subscriptions have been initiated
        not exists
            (select *
               from sysextendedarticlesview art
              where art.pubid = @pubid
                and not exists
                    (select *
                       from syssubscriptions sub
                      where sub.artid = art.artid
                        and sub.srvname = UPPER(@subscriber)
                        and ((@destination_db = N'%') OR (sub.dest_db = @destination_db)))))
    begin
        exec @retcode = sys.sp_MSrepl_changesubstatus
                @publication    = @publication,
                @article        = N'%',
                @subscriber     = @subscriber,
                @status         = 'initiated',
                @destination_db = @destination_db,
                @publisher      = @publisher,
                @publisher_type = @publisher_type

        if @@error <> 0 or @retcode <> 0
        begin
            if @@trancount > 0
            begin
                rollback tran addsubscription
                commit tran
            end
            raiserror (14057, 16, -1)
            return (1)
       end
    end

	IF sys.fn_yukonsecuritymodelrequired(NULL) = 0
	BEGIN	
	    /* Conditional support for MobileSync */
	    if LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'
	    BEGIN
	        /* MobileSync Support */
	        declare @distributor_server                 sysname
	        declare @distributor_security_mode          int
	        declare @distributor_login                  sysname
	        declare @distributor_password               sysname
	        /*
	        ** The registry entry needs to be created only for push subscriptions 
	        ** i.e - need not be called when a pull subscription is created at the
	        ** subscriber and sp_addmergesubscription is being called then.
	        */
	        IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
	        BEGIN
	            EXECUTE @retcode = sys.sp_MSrepl_getdistributorinfo
					@publisher   = @publisher,
	                @distributor = @distributor_server OUTPUT               /* Distributor RPC server name */

	            IF @@ERROR <> 0 or @retcode <> 0
	                BEGIN
	                    if @@trancount > 0
	                        ROLLBACK TRAN  addsubscription
	                    RAISERROR (14057, 16, -1)
	                    RETURN (1)
	                END

	            -- Always use integrated security on winNT
	            set @distributor_security_mode = 1

	            /* Call sp_MSregistersubscription so that the subscription can be synchronized via Onestop etc. */
	            declare @subscription_id uniqueidentifier
	            set @subscription_id = convert(uniqueidentifier, @distribution_jobid)
	            exec @retcode = sys.sp_MSregistersubscription @replication_type = 1,
	                                    @publisher = @publisher_local,
	                                    @publisher_db = @publisher_db,
	                                    @publication = @publication,
	                                    @subscriber = @subscriber,
	                                    @subscriber_db = @destination_db,
	                                    @distributor = @distributor_server,
	                                    @distributor_security_mode = @distributor_security_mode,
	                                    @distributor_login = @distributor_login,
	                                    @distributor_password = @distributor_password,
	                                    @subscription_id = @subscription_id,
	                             @independent_agent = @independent_agent_id,
	                                    @subscription_type = @subscription_type_id


	            IF @@ERROR <> 0 or @retcode <> 0
	                BEGIN
	                    if @@trancount > 0
	                        ROLLBACK TRAN  addsubscription
	                    RAISERROR (14057, 16, -1)
	                    RETURN (1)
	                END
	        END
	    END
	END
	
    COMMIT TRAN
	return (0)

UNDO:
	if @@trancount > 0
	begin
		rollback tran addsubscription
		commit tran
	end
	return (1)
END

 
Last revision 2008RTM
See also

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