Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addsubscription

  No additional text.


Syntax
create procedure sys.sp_MSrepl_addsubscription
(
    @publication			sysname,
    @article				sysname = 'all',
    @subscriber				sysname = NULL,
    @destination_db			sysname = NULL,
    @sync_type				nvarchar (255) = 'automatic',
    @status				sysname = NULL,
    @subscription_type			nvarchar(4) = 'push',
    -- SyncTran
    @update_mode			nvarchar(30) = 'read only',		-- Can be 'read only', 'sync tran', 'queued tran', 'failover', 'queued failover'
    @loopback_detection			nvarchar(5) = NULL,				-- 'true' or 'false'
    -- end SyncTran
    @frequency_type			int = NULL, -- defaults to 64
    @frequency_interval			int = NULL, -- defaults to 1
    @frequency_relative_interval	int = NULL, -- defaults to 1
    @frequency_recurrence_factor	int = NULL, -- defaults to 0
    @frequency_subday			int = NULL, -- defaults to 4
    @frequency_subday_interval		int = NULL, -- defaults to 5
    @active_start_time_of_day		int = NULL, -- defaults to 0
    @active_end_time_of_day		int = NULL, -- defaults to 235959
    @active_start_date			int = NULL, -- defaults to 0
    @active_end_date			int = NULL, -- defaults to 99991231
    @optional_command_line		nvarchar(4000) = NULL,

    @reserved				nvarchar(10) = NULL,	-- reserved, used when calling from other system
								-- stored procedures, it will be set to 'internal'.
								-- It should never be used directly
    @enabled_for_syncmgr		nvarchar(5) = NULL,	-- Enabled for SYNCMGR: true or false
    -- Agent offload
    @offloadagent			bit = 0,
    @offloadserver			sysname = NULL,
    -- End of agent offload
    -- DTS package name
    @dts_package_name			sysname  = NULL,	-- value will be sent and validated at distributor
    @dts_package_password		sysname = NULL,
    @dts_package_location		nvarchar(12) = NULL,
    @distribution_job_name		sysname = NULL,
    @publisher				sysname = NULL,
    -- Backup device specification for @sync_method = 'autonosync with backup'
    @backupdevicetype			nvarchar(20) = 'logical',
    @backupdevicename			nvarchar(1000) = null,
    @mediapassword			sysname = null,
    @password				sysname = null,
    @fileidhint				int = null,
    @unload				bit = 1,
    -- No-sync subscription LSN for @sync_method = 'autonosync with lsn'
    @subscriptionlsn			binary(10) = null
    ,@publisher_type			sysname
    -- expose -SubscriptionStreams functionality
    ,@subscriptionstreams tinyint	= null -- value provided should fall between 1 and 64
    ,@subscriber_type			tinyint = 0  -- defaults of SQL Server
)
AS
BEGIN

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
	DECLARE @pubid int
    DECLARE @artid int
    DECLARE @pre_creation_cmd tinyint
    DECLARE @none tinyint
    DECLARE @automatic tinyint
    DECLARE @inactive tinyint
    DECLARE @active tinyint
    DECLARE @subscribed tinyint
    DECLARE @manual tinyint
    DECLARE @srvid smallint
    DECLARE @subscriber_bit smallint
    DECLARE @sync_typeid tinyint
    DECLARE @non_sql_flag bit
    DECLARE @truncate tinyint
    DECLARE @sync_method tinyint
    DECLARE @char_bcp tinyint
    DECLARE @concurrent tinyint
    DECLARE @concurrent_char tinyint
    DECLARE @database_snapshot tinyint
    DECLARE @database_snapshot_character tinyint
    DECLARE @internal nvarchar(10)
	DECLARE @nosync nvarchar(10)
    DECLARE @status_id tinyint
    DECLARE @virtual_id smallint
    DECLARE @subscription_type_id int /* 0 push, 1 pull */
    DECLARE @immediate_sync bit    /* publication type */
    DECLARE @count_subs int
    DECLARE @count_arts int
    DECLARE @distribution_jobid binary(16)
    DECLARE @pubstatus tinyint
    DECLARE @allow_anonymous bit
    DECLARE @immediate_sync_ready bit
    declare @loopback_detection_id bit
    declare @independent_agent_id bit
    DECLARE @platform_nt binary
            ,@artsrctabid int
            ,@distributor sysname
            ,@distribdb sysname

    DECLARE @dsn_dbname sysname
    DECLARE @dts_package_enc_password nvarchar(524)

    -- SyncTran
    DECLARE @allow_sync_tran_id bit
    DECLARE @allow_queued_tran_id bit
    DECLARE @update_mode_id     tinyint -- 0 = read only, 1 = sync tran, 2 = queued tran, 3 = failover
                                        -- 4 = sqlqueued tran, 5 = sqlqueued failover, 6 = sqlqueued qfailover, 7 = qfailover
    DECLARE @publication_queue_type int
    -- end SyncTran

    -- Heterogeneous subscriptions
    DECLARE @enabled_for_het_sub bit
	DECLARE @OPT_ENABLED_FOR_HET_SUB int
	SELECT @OPT_ENABLED_FOR_HET_SUB = 0x4

	DECLARE @OPT_ENABLED_FOR_P2P int
	SELECT @OPT_ENABLED_FOR_P2P = 0x1
	
    -- Heterogeneous publishers
    DECLARE @publisher_local sysname
					,@publisher_db sysname
					
    DECLARE @distproc nvarchar(1000)
	
	set @publisher_db = DB_NAME()

	-- Parameter check: @update_mode
	-- HREPL: Reject non-read-only
	IF NOT @publisher_type = N'MSSQLSERVER' AND NOT @update_mode = 'read only'
	BEGIN
        RAISERROR (21634, 16, -1, '@update_mode', @update_mode, '''read only'' or NULL')
        RETURN (1)
	END

	/*
    ** Parameter Check: @subscription_type
    ** Valid values:
    ** push
    ** pull
    **
    */

    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull')
        BEGIN
            RAISERROR (14128, 16, -1)
            RETURN (1)
        END

    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
    BEGIN
        SELECT @subscription_type_id = 0
    END
    ELSE
    BEGIN
        SELECT @subscription_type_id = 1
    END
	
    /*
    ** Security Check.
    */

    IF @subscription_type_id = 0
    BEGIN
        exec @retcode = sys.sp_MSreplcheck_publish
        if @@ERROR <> 0 or @retcode <> 0
            return(1)
    END
    ELSE
    BEGIN
        exec @retcode = sys.sp_MSreplcheck_pull	@publication = @publication,
        										@publisher	 = @publisher

        if @@ERROR <> 0 or @retcode <> 0
            return(1)
    END

	-- With the new security model a number of parameters are invalid
	IF sys.fn_yukonsecuritymodelrequired(NULL) = 1
	BEGIN
		IF @frequency_type IS NOT NULL
			OR @frequency_interval IS NOT NULL
			OR @frequency_relative_interval IS NOT NULL
			OR @frequency_recurrence_factor IS NOT NULL
			OR @frequency_subday IS NOT NULL
			OR @frequency_subday_interval IS NOT NULL
			OR @active_start_time_of_day IS NOT NULL
			OR @active_end_time_of_day IS NOT NULL
			OR @active_start_date IS NOT NULL
			OR @active_end_date IS NOT NULL
			OR @optional_command_line IS NOT NULL
			OR @enabled_for_syncmgr IS NOT NULL
			OR @dts_package_name IS NOT NULL
			OR @dts_package_password IS NOT NULL
			OR @dts_package_location IS NOT NULL
			OR @distribution_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, 10, -1, 'scheduling, optional command line, sync manager, dts and distribution job name', 'sp_addpushsubscription_agent'' or ''sp_addpullsubscription_agent')

			SELECT @frequency_type = NULL,
					@frequency_interval = NULL,
					@frequency_relative_interval = NULL,
					@frequency_recurrence_factor = NULL,
					@frequency_subday = NULL,
					@frequency_subday_interval = NULL,
					@active_start_time_of_day = NULL,
					@active_end_time_of_day = NULL,
					@active_start_date = NULL,
					@active_end_date = NULL,
					@optional_command_line = NULL,
					@enabled_for_syncmgr = NULL,
					@dts_package_name = NULL,
					@dts_package_password = NULL,
					@dts_package_location = NULL,
					@distribution_job_name = NULL
		END
	END
	ELSE
	BEGIN
		-- Set the original default values for the 8.0 model
		SELECT @enabled_for_syncmgr = ISNULL(@enabled_for_syncmgr , 'false'),
					@dts_package_location = ISNULL(@dts_package_location, N'distributor')
	
		-- Parameter check: @enabled_for_syncmgr
		-- HREPL: not supported
		select @enabled_for_syncmgr = isnull(lower(@enabled_for_syncmgr), 'false')
		IF @publisher_type = N'MSSQLSERVER'
		BEGIN
			IF @enabled_for_syncmgr not in ('true', 'false')
			BEGIN
				-- Invalid '@enabled_for_syncmgr' value. Valid values are 'true' or 'false'.
				RAISERROR (14148, 16, -1, '@enabled_for_syncmgr')
				RETURN (1)
			END
		END
		ELSE
		BEGIN
			IF @enabled_for_syncmgr = 'true'
			BEGIN
		        RAISERROR (21632, 16, -1, '@enabled_for_syncmgr', '''false'' or NULL')
		        RETURN (1)
			END
		END
	END
	
    -- Check to see if database is activated for publication
    IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Initializations.
    */

    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 @database_snapshot = 5 /* Const: database snapshot native sync method */
    SELECT @database_snapshot_character = 6 /* Const: database snapshot character sync method */
    SELECT @virtual_id = -1 /* Const: virtual subscriber id */
    SELECT @internal = 'internal' /* Const: Flag of calling internally from system */
    SELECT @nosync = N'nosync'
                                  /* stored procedures     */

    -- Change it  in 7.5 to avoid confusion, expecially in ole db case
    -- SELECT @dsn_dbname = 'DSN'
    SELECT @dsn_dbname = formatmessage(20586)
    SELECT @platform_nt = 0x1

    -- If sync_method is one of the new autonosync types, re-route the call
    -- immediately to sp_MSaddautonosyncsubscription. Since sp_addsubscription
    -- will be called again inside sp_MSaddautonosyncsubscription with
    -- @sync_method = 'none', there is no need to waste any time doing
    -- parameter validation here.
    select @sync_type = lower(@sync_type collate SQL_Latin1_General_CP1_CS_AS)

    -- HREPL only supports automatic, replication support only, or none for sync_type at this time
    IF	@publisher_type != N'MSSQLSERVER' AND
		@sync_type IN (	N'initialize with backup',
						N'initialize from lsn')
    BEGIN
    	DECLARE @err nvarchar(4000)
    	SELECT @err = '@sync_type value of ''' + @sync_type + ''''
    	RAISERROR (21632, 16, -1, @err, '''automatic'', ''replication support only'', or ''none''')
    	RETURN (1)
    END

    -- Heterogeneous subscribers only support automatic, replication support only, or none for sync_type at this time
    IF	@subscriber_type != 0 AND
		@sync_type IN (	N'initialize with backup',
						N'initialize from lsn')
    BEGIN
    	RAISERROR (21644, 16, -1, '@sync_type', @sync_type, '''automatic'', ''replication support only'', or ''none''')
    	RETURN (1)
    END

	/*
	** Parameter Check: @offloadagent.
	*/
	IF @offloadagent IS NOT NULL
		AND @offloadagent != 0
	BEGIN
		-- "Parameter '@offloadagent' is no longer supported."
		RAISERROR(21698, 16, -1, '@offloadagent')
		RETURN 1
	END

	IF ISNULL(@offloadserver, N'') != N''
	BEGIN
		-- "Parameter '@offloadserver' is no longer supported."
		RAISERROR(21698, 16, -1, '@offloadserver')
		RETURN 1
	END

	/*
    ** If @publisher is NULL, set @publisher_local to publishingservername()
    ** otherwise, set @publisher_local to @publisher
    */
    IF @publisher IS NULL
    BEGIN
        select @publisher_local = publishingservername()
    END
    ELSE
    BEGIN
        select @publisher_local = @publisher
    END
    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    ** set subscription_type for the publication
    */


	-- Get publication id
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)


    IF (@pubid IS NULL)
	BEGIN
		RAISERROR (20026, 11, -1, @publication)
		RETURN (1)
	END
	
	declare @allow_dts bit,
			@options int

	SELECT	@sync_method			= sync_method,
			@immediate_sync			= immediate_sync,
			@pubstatus				= status,
			@allow_anonymous		= allow_anonymous,
			@immediate_sync_ready	= immediate_sync_ready,
			@allow_sync_tran_id		= allow_sync_tran,
			@allow_queued_tran_id	= allow_queued_tran,
			@independent_agent_id	= independent_agent,
			@allow_dts				= allow_dts,
			@publication_queue_type	= queue_type,
			@options				= options
	FROM	syspublications
	WHERE	pubid = @pubid

    select @srvid = srvid from master.dbo.sysservers where UPPER(srvname collate database_default)=UPPER(@subscriber) collate database_default

	-- Only perform the following when not called internally
	-- 		Add Subscriber
	-- 		AND
	-- 		PeerToPeer specific checks
	IF @reserved IS NULL
	BEGIN	
		-- the first call to sp_addsubscription we will
		-- will attempt to add the subscriber first ...
		IF @@TRANCOUNT != 0
		BEGIN
			-- The procedure 'sp_addsubscription' cannot be executed within a transaction.
			RAISERROR(15002, 16, -1, 'sp_addsubscription')
			RETURN 1
		END
		
		EXEC @retcode = sys.sp_MSrepl_addsubscriber @subscriber 						= @subscriber,
														@type 							= @subscriber_type,
														@login 							= NULL,
														@password						= NULL,
														@commit_batch_size				= NULL,
														@status_batch_size				= NULL,
														@flush_frequency				= NULL,
														@frequency_type					= NULL,
														@frequency_interval				= NULL,
														@frequency_relative_interval	= NULL,
														@frequency_recurrence_factor	= NULL,
														@frequency_subday				= NULL,
														@frequency_subday_interval		= NULL,
														@active_start_time_of_day		= NULL,
														@active_end_time_of_day			= NULL,
														@active_start_date				= NULL,
														@active_end_date				= NULL,
														@description					= NULL,
														@security_mode					= NULL,
														@encrypted_password				= NULL,
														@publisher						= @publisher_local,
														@publisher_type					= @publisher_type,
														@internal						= N'YUKON'
		IF @@ERROR <> 0 OR @retcode <> 0		
			RETURN 1

		-- PeerToPeer specific checks
		IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
		BEGIN
			-- only allow full subscriptions
			IF ISNULL(LOWER(@article), N'') != N'all'
			BEGIN
				-- Peer-To-Peer publications only support an @article parameter value of 'all'.
				RAISERROR (21679, 16, -1, '@article', '''all''')
				RETURN (1)
			END

			-- verify it's a valid sync_type for P2P
			IF @sync_type NOT IN (N'replication support only',
									N'initialize with backup',
	                      			N'initialize from lsn')
			BEGIN
				-- Peer-To-Peer publications only support an @sync_type parameter value of 'replication support only', 'initialize with backup' or 'initialize from lsn'.
				RAISERROR (21679, 16, -1, '@sync_type', '''replication support only'', ''initialize with backup'' or ''initialize from lsn''')
				RETURN (1)
			END
		END
	END
	
    IF @sync_type in (N'replication support only',
                N'initialize with backup',
                      N'initialize from lsn')
    BEGIN
        exec @retcode = sys.sp_MSaddautonosyncsubscription	@publication					= @publication,
															@article						= @article,
															@subscriber						= @subscriber,
															@destination_db					= @destination_db,
															@sync_type						= @sync_type,
															@status							= @status,
															@subscription_type				= @subscription_type,
															@update_mode					= @update_mode,
															@loopback_detection				= @loopback_detection,
															@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,
															@reserved						= @reserved,
															@enabled_for_syncmgr			= @enabled_for_syncmgr,
															@dts_package_name				= @dts_package_name,
															@dts_package_password			= @dts_package_password,
															@dts_package_location			= @dts_package_location,
															@distribution_job_name			= @distribution_job_name,
															-- Backup lsn extraction parameters
															@backupdevicetype				= @backupdevicetype,
															@backupdevicename				= @backupdevicename,
															@mediapassword					= @mediapassword,
															@password						= @password,
															@fileidhint						= @fileidhint,
															@unload							= @unload,
															-- Explicitly specified lsn of the subscription
															@subscriptionlsn				= @subscriptionlsn,
															@publisher						= @publisher,
															@publisher_type					= @publisher_type
															,@subscriptionstreams	= @subscriptionstreams
															,@subscriber_type		= @subscriber_type
		IF @@error <> 0
		BEGIN
			SELECT @retcode = 1
		END
		
        RETURN @retcode
    END

    IF EXISTS (select name from sys.objects where name='sysmergesubscriptions')
	BEGIN
		IF EXISTS
			(
				select	name
				from	sysextendedarticlesview
				where	pubid=@pubid
				  and	objid in
				  		(
				  			select	objid
				  			from	sysmergeextendedarticlesview
				  			where	pubid in
									(
				  						select	pubid
				  						from	sysmergesubscriptions
				  						where	db_name = @destination_db
				  						  and	UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default
									)
						)
			)
		BEGIN
			RAISERROR(21281, 16, -1, @publication, @destination_db)
			RETURN (1)
		END
	END


    IF @pubid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@pubid', 'sp_MSrepl_addsubscription')
            RETURN (1)
        END

    /*
    ** If publication is of concurrent sync, then all articles must
    ** be subscribed to
    */
    IF @sync_method IN( @concurrent, @concurrent_char) AND
       LOWER(@article) != 'all' AND
       @reserved NOT IN (@internal, @nosync)
    BEGIN
        RAISERROR( 14100, 16, -1 )
        RETURN (1)
    END

    /*
    ** Check to see if the desired subscription type is allowed
    */
    /*
    ** push
    ** Virtual subscriptions are always push type
    */
    IF @subscription_type_id = 0 AND @subscriber IS NOT NULL
    BEGIN
        IF NOT EXISTS (SELECT * from syspublications where
            allow_push = 1 AND
            pubid = @pubid)
       BEGIN
            RAISERROR (20012, 16, -1, @subscription_type, @publication)
            RETURN (1)
        END
    END

    /* pull */
    IF @subscription_type_id = 1 AND @subscriber IS NOT NULL
    BEGIN
        IF NOT EXISTS (SELECT * from syspublications where
            allow_pull = 1 AND
            pubid = @pubid)
        BEGIN
            RAISERROR (20012, 16, -1, @subscription_type, @publication)
            RETURN (1)
        END
    END

 	/*
    ** Parameter Check: @subscriber.
    **
    ** Check if the server exists and that it is a subscription server.
    **
    ** @subscriber is NULL represent virtual subscription, which is not allowed
    ** in following case:
    ** 1. Non-immediate-sync publication
    ** 2. the stored procedure is not in the internal usage mode
    **        (called by system stored procedures)
    ** 3. non push mode
    **
    */

    IF  @subscriber IS NULL AND (
        @immediate_sync = 0 OR
        @subscription_type_id <> 0 OR
        @reserved NOT IN (@internal, @nosync))
        BEGIN
            RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_addsubscription')
            RETURN (1)
        END

--initialize this to 0, for virtual servers, it will stay 0, for non-hetero ones if there isn't
--server entry in master..sysservers, this flag should stay 0 as well,
  select @non_sql_flag = 0
  IF @subscriber IS NULL
        BEGIN
        /* set virtual subscriber ID */
            SELECT @srvid = @virtual_id
        END
    ELSE
        BEGIN
            /* validate name and get subscriber ID  and server status  */
            EXECUTE @retcode = sys.sp_validname @subscriber

            IF @retcode <> 0
            RETURN (1)

            select @srvid = null
            SELECT @srvid = srvid, @non_sql_flag = nonsqlsub
              FROM master.dbo.sysservers
             WHERE UPPER(srvname collate database_default) = UPPER(@subscriber) collate database_default

			IF @srvid IS NULL
			BEGIN
				--we used to raise 14010 if subscriber is not found
				--removed it now since we try to depend on the subscriber entry in master.dbo.syservers less
				--set @non_sql_flag here so syssubscriptions will have the right value
				IF @subscriber_type > 0
				BEGIN
					select @non_sql_flag = 1
				END
			END
        END

    /*
    ** Parameter Check: @destination_db.
    */
    IF @destination_db IS not NULL
    begin
        
        -- @destination_db cannot be all.
        -- @destination_db cannot be master.
        -- Make sure that the @destination_db conforms to the rules for identifiers.
        
        if LOWER(@destination_db) = 'all'
        BEGIN
            RAISERROR (14032, 16, -1, '@destination_db')
            RETURN (1)
        END

        if LOWER(@destination_db) = 'master'
        BEGIN
            RAISERROR (21481, 16, 1)
            RETURN (1)
        END

        EXECUTE @retcode = sys.sp_validname @destination_db
        IF @retcode <> 0
        RETURN (1)
    end

	-- Parameter check: @destination_db
	-- HREPL: required for SQL subscribers
	IF NOT @publisher_type = N'MSSQLSERVER' AND @destination_db IS NULL AND @non_sql_flag = 0
	BEGIN
        RAISERROR (21637, 16, -1, '@destination_db')
        RETURN (1)
	END

    /*
    ** Parameter Check:  @article
    */

    /* @article can not be null     */
    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_addsubscription')
            RETURN (1)
        END
	
	-- If the subscriber type is not SQL Server, the publication must be
	-- enabled for heterogeneous subscriptions
	IF @subscriber_type <> 0
	   AND (NOT ((@options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB))
	BEGIN
		-- Unable to add a heterogeneous subscription to the publication.  The publication is not enabled for heterogeneous subscriptions.
		RAISERROR (20615, 16, -1)
		RETURN (1)
	END

    
    -- Parameter check: @update_mode
    
    IF @update_mode IS NULL OR LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) NOT IN
            ('read only', 'sync tran', 'queued tran', 'failover', 'queued failover')
    BEGIN
        RAISERROR (20502, 16, -1, '@update_mode')
        RETURN (1)
    END
    -- set @update_mode_id
    IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'sync tran')
    BEGIN
        -- Immediate updating subscription
        -- 1 = synctran
        SELECT @update_mode_id = 1
        -- Check if publication allows this option
        IF @allow_sync_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','sync tran')
            RETURN (1)
        END
    END
    ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued tran')
    BEGIN
        -- Queued updating subscription
        -- 2 = queued, 4 = sqlqueued
        SELECT @update_mode_id = case
            when (@publication_queue_type = 2) then 4
            else 2 end
        -- Check if publication allows this option
        -- If the publication allow synctran, it allows queued tran.
        IF @allow_queued_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'queued tran')
            RETURN (1)
        END
    END
    ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'failover')
    BEGIN
        -- Failover subscription with initial state as Immediate
        -- 3 = failover, 5 = sqlqueued failover
        SELECT @update_mode_id = case
            when (@publication_queue_type = 2) then 5
            else 3 end
        -- Check if publication allows this option
        IF @allow_sync_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'sync tran')
            RETURN (1)
        END
        -- Check if publication allows this option
        IF @allow_queued_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','queued tran')
            RETURN (1)
        END
    END
    ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued failover')
    BEGIN
        -- Failover subscription with initial state as Queued
        -- 6 = sqlqueued qfailover, 7 = qfailover
        SELECT @update_mode_id = case
            when (@publication_queue_type = 2) then 6
            else 7 end
        -- Check if publication allows this option
        IF @allow_sync_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'sync tran')
            RETURN (1)
        END
        -- Check if publication allows this option
        IF @allow_queued_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','queued tran')
            RETURN (1)
        END
    END
    ELSE
    BEGIN
        -- Read only subscription
        SELECT @update_mode_id = 0
    END

	IF sys.fn_yukonsecuritymodelrequired(NULL) = 0
	BEGIN	
	    
	    -- Parameter Check: @dts_package_location
	    -- Valid values:
	    -- distributor
	    -- subscriber
	    
	    
	    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('distributor', 'subscriber')
	    BEGIN
	        RAISERROR(21179, 16, -1)
	        RETURN (1)
	    END

	    declare @dts_package_location_id int

	    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = 'distributor'
	        SELECT @dts_package_location_id = 0
	    ELSE
	        SELECT @dts_package_location_id = 1

	    -- @dts_package_password cannot be non-null if @dts_package_name is not set
	    if (@dts_package_name is null or rtrim(@dts_package_name) = N'') and
	        @dts_package_password is not null
	    begin
	        raiserror(18780, 16, -1)
	        return (1)
	    end

	    -- Have to be a push, non updatable  subscription to set DTS package name
	    if @dts_package_name is not null
	    begin
	        if  @subscription_type_id != 0
	        begin
	            RAISERROR(21181, 16, -1)
	            RETURN (1)
	        end
	        if  @allow_dts = 0
	        begin
	            RAISERROR(21178, 16, -1)
	            RETURN (1)
	        end
	    end
	END
	ELSE
	BEGIN
		SELECT @dts_package_location_id = 0
	END
	
    /** For immediate_sync publication, @article has to be 'all'     */
    IF @reserved NOT IN (@internal, @nosync)
    	AND @immediate_sync = 1
        AND NOT LOWER(@article) = 'all'
    BEGIN
        RAISERROR (14122, 16, -1)
        RETURN (1)
    END

    /*
    ** For full subscription, check to see if  subscriptions
    ** to ALL the articles exist before expanding parameter @article.
    **
    */
    IF LOWER(@article) = 'all'
    	AND @reserved NOT IN (@internal, @nosync)
    	AND EXISTS (SELECT * FROM syspublications WHERE pubid = @pubid)
    BEGIN
        SELECT @count_arts = count(*) FROM sysextendedarticlesview art
            WHERE art.pubid = @pubid

        if @count_arts = 0
        BEGIN
            RAISERROR (14124, 16, -1)
            RETURN(1)
        END

        SELECT @count_subs = count(*) FROM syssubscriptions sub,
                  sysextendedarticlesview art
            WHERE sub.srvname = UPPER(@subscriber)
              AND sub.srvid >= 0
              AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
              AND sub.artid = art.artid
              AND art.pubid = @pubid

        IF @count_arts = @count_subs
        BEGIN
              RAISERROR (14058, 16, -1)
              RETURN (1)
        END
    END

    /*
    ** Real subscription to inactive publicaton is not allowed
    ** Note, subscriptions to the new article will be added automatically
    ** for immediate_sync publications. At that time, the publication may not
    ** be active.
    */

    IF  @srvid <> @virtual_id
    	AND @pubstatus = 0
   		AND @reserved NOT IN (@internal, @nosync)
    BEGIN
        RAISERROR (21000, 16, -1)
        RETURN (1)
    END

	IF sys.fn_yukonsecuritymodelrequired(NULL) = 0
	BEGIN	
	    -- If the publication is 'allow_dts', push subscription has to specify a DTS package.
	    -- Error check that disallow ODBC subscriber to subscriber with DTS package
	    -- is at the distributor.
	    -- Show dts error first, otherwise user will get 21060 below which is confusing
	    IF @allow_dts <> 0
	    	and @dts_package_name is null
	    	and @subscriber IS not NULL
	    	and @reserved NOT IN (@internal, @nosync)
	    	and @subscription_type_id = 0
	    begin
	        raiserror(21213, 16, -1)
	        return(1)
	    end
	END
	
    /*
    ** Do special things for DSN subscribers.
    */
    IF @subscriber IS NOT NULL AND @non_sql_flag <> 0
    BEGIN
        -- DSN or oledb subscriber not using DTS
        -- cannot subscribe to native mode or concurrent snapshot publication
        IF @sync_method NOT IN (@char_bcp, @concurrent_char, @database_snapshot_character) and @dts_package_name is null and @publisher_type = N'MSSQLSERVER'
        BEGIN
            RAISERROR (14095, 16, -1, @publication, @subscriber)
            RETURN (1)
        END

        IF @sync_method NOT IN (@char_bcp, @concurrent_char, @database_snapshot_character) AND @publisher_type != N'MSSQLSERVER'
        BEGIN
        	RAISERROR (21676, 16, -1, @subscriber, @publication)
        	RETURN (1)
        END

        -- DSN subscriber cannot subscribe with 'Sync Update'
        IF @update_mode_id <> 0
        BEGIN
            RAISERROR (21032, 16, -1, @subscriber)
            RETURN (1)
        END
    END

    -- DNS may define db. If no db given, specify that DSN default should be used.
    -- use internal values
    if @subscriber IS NOT NULL and @destination_db is NULL
    begin
        IF @non_sql_flag <> 0
            SELECT @destination_db = @dsn_dbname
        else
            SELECT @destination_db = DB_NAME()
        
        -- @destination_db cannot be master
        
        if LOWER(@destination_db) = 'master'
        BEGIN
            RAISERROR (21481, 16, 1)
            RETURN (1)
        END
    end

    -- if we're subscribing to a dump type publication, error
    -- out if this subscriber has any other subscriptions to publications
    -- other than this one

    IF @sync_method = 2
    BEGIN
        IF EXISTS( SELECT * FROM syssubscriptions sub, sysextendedarticlesview art
                WHERE sub.srvname = UPPER(@subscriber)
                  AND sub.srvid >= 0
                  AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
                  AND sub.artid = art.artid
                  AND art.pubid != @pubid )
        BEGIN
            RAISERROR(21144, 16, -1)
            RETURN 1
        END
    END

    -- else if we're subscribing to a char or native mode publication,
    -- error out if the subscriber is subscribed to any dump type publications
    ELSE
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	syssubscriptions sub,
        			sysextendedarticlesview art,
        			syspublications pub
			WHERE	 sub.srvname = UPPER(@subscriber)
			  AND	sub.srvid		>= 0
			  AND	sub.dest_db 		 = @destination_db
			  AND	sub.artid		 = art.artid
			  AND	art.pubid		!= @pubid
			  AND	pub.pubid		 = art.pubid
			  AND	pub.sync_method	 = 2
		)
        BEGIN
            RAISERROR(21145, 16, -1, @publication )
            RETURN 1
        END
    END


    IF LOWER(@article) = 'all'
    /*
    ** Get all articles in the publication that are not subscribed to
    ** by the @subscriber
    */
    BEGIN
            /*
            ** Make the operation atomic. This is to prevent multiple subscription_type
            ** from one subscriber on an immediate_sync publication
            */
            BEGIN TRAN

            IF @publisher IS NULL
            BEGIN
                DECLARE hCx CURSOR LOCAL FAST_FORWARD FOR
                SELECT DISTINCT a.name
                FROM	sysextendedarticlesview a,
                		syspublications b
                WHERE	a.pubid = b.pubid
				  AND	b.pubid = @pubid
                  AND	NOT EXISTS
                  		(
                  			SELECT	*
                  			FROM	syssubscriptions s
                    		WHERE	s.artid = a.artid
                    		  AND	s.status <> 0
                    		  AND	(@subscriber IS NULL and s.srvid = @srvid or (@subscriber IS not NULL) and s.srvname = UPPER(@subscriber))
                    		  AND	s.dest_db = @destination_db
                    	)
                FOR READ ONLY
            END
            ELSE -- is this hpub? otherwise how can we have syspublications and MSpublications in the same db
            BEGIN
                DECLARE hCx CURSOR LOCAL FAST_FORWARD FOR
                SELECT DISTINCT a.name
                FROM	sysextendedarticlesview a,
                		syspublications b,
                		MSpublications m,
                		master.dbo.sysservers n
                WHERE	a.pubid = b.pubid
				  AND	m.publication_id = a.pubid
				  AND	m.publisher_id = n.srvid
				  AND	UPPER(n.srvname collate database_default) = UPPER(@publisher) collate database_default
				  AND	b.pubid = @pubid
                  AND	NOT EXISTS
                  		(
                  			SELECT	*
                  			FROM	syssubscriptions s,
                  					MSarticles m,
                  					master.dbo.sysservers n
                    		WHERE	s.artid = a.artid
                    		  AND	s.status <> 0
                    		  AND	s.srvid = @srvid
                    		  AND	s.dest_db = @destination_db
                    		  AND	m.article_id = s.artid
                    		  AND	m.publisher_id = n.srvid
							  AND	UPPER(n.srvname collate database_default) = UPPER(@publisher) collate database_default
						)
                FOR READ ONLY
            END

            OPEN hCx
            FETCH hCx INTO @article

            WHILE (@@fetch_status <> -1)
			BEGIN
				EXECUTE @retcode = sys.sp_MSrepl_addsubscription_article
                                @publication       = @publication,
                                @article        = @article,
                                @subscriber     = @subscriber,
                                @destination_db = @destination_db,
                                @sync_type      = @sync_type,
                                @status         = @status,
                                @subscription_type = @subscription_type,
                                @reserved       = @internal,
                                @update_mode    = @update_mode,
                                @loopback_detection = @loopback_detection,
                                @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,
            					@enabled_for_syncmgr = @enabled_for_syncmgr,
                                @dts_package_name = @dts_package_name,
                                @dts_package_password = @dts_package_password,
                                @dts_package_location = @dts_package_location,
                                @distribution_job_name = @distribution_job_name,
                                @publisher = @publisher,
                                @publisher_type = @publisher_type
                                ,@subscriptionstreams = @subscriptionstreams
                                ,@subscriber_type = @subscriber_type
                                ,@pubid = @pubid
                                ,@non_sql_flag = @non_sql_flag
                                ,@publisher_local = @publisher_local
                                ,@immediate_sync = @immediate_sync
                                ,@srvid = @srvid
                                ,@independent_agent_id = @independent_agent_id
                                ,@dts_package_location_id = @dts_package_location_id
                                ,@immediate_sync_ready  = @immediate_sync_ready
                                ,@sync_method = @sync_method
                                ,@subscription_type_id = @subscription_type_id
                                ,@pubstatus = @pubstatus
                                ,@allow_anonymous = @allow_anonymous
                                ,@update_mode_id = @update_mode_id

				IF @@error <> 0 OR @retcode <> 0
				BEGIN
					CLOSE hCx
					DEALLOCATE hCx

					IF @@trancount > 0
						ROLLBACK TRAN

					RETURN (1)
				END
				FETCH hCx INTO @article
			END
            CLOSE hCx
            DEALLOCATE hCx

            COMMIT TRAN

  RETURN (0)
        END

        -- We are adding a single subscription.

        EXECUTE @retcode = sys.sp_MSrepl_addsubscription_article
                                @publication       = @publication,
                                @article        = @article,
                                @subscriber     = @subscriber,
                                @destination_db = @destination_db,
                                @sync_type      = @sync_type,
                                @status         = @status,
                                @subscription_type = @subscription_type,
                                @reserved       = @internal,
                                @update_mode    = @update_mode,
                                @loopback_detection = @loopback_detection,
                                @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,
               					@enabled_for_syncmgr = @enabled_for_syncmgr,
                                @dts_package_name = @dts_package_name,
                                @dts_package_password = @dts_package_password,
                                @dts_package_location = @dts_package_location,
                                @distribution_job_name = @distribution_job_name,
                                @publisher = @publisher,
                                @publisher_type = @publisher_type
                                ,@subscriptionstreams = @subscriptionstreams
                                ,@subscriber_type = @subscriber_type
                                ,@pubid = @pubid
                                ,@non_sql_flag = @non_sql_flag
                                ,@publisher_local = @publisher_local
                                ,@immediate_sync = @immediate_sync
                                ,@srvid = @srvid
                                ,@independent_agent_id = @independent_agent_id
                                ,@dts_package_location_id = @dts_package_location_id
                                ,@immediate_sync_ready = @immediate_sync_ready
                                ,@sync_method = @sync_method
                                ,@subscription_type_id = @subscription_type_id
                                ,@pubstatus = @pubstatus
                                ,@allow_anonymous = @allow_anonymous
                                ,@update_mode_id = @update_mode_id

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

		RETURN (0)
	END

 
Last revision SQL2008SP2
See also

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