Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addsubscriber

  No additional text.


Syntax
create procedure sys.sp_MSrepl_addsubscriber
(
    @subscriber						sysname,
    @type							tinyint,
    @login							sysname,
    @password						nvarchar(524),
    @commit_batch_size				int,
    @status_batch_size				int,
    @flush_frequency				int,
    @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,
    @description					nvarchar (255),
    @security_mode					int,
    @encrypted_password				bit,
    @publisher						sysname,
    @publisher_type					sysname,
    @internal						sysname = N'PRE-YUKON'		-- Can be: 'PRE-YUKON', 'YUKON', 'BOTH'
)
AS
BEGIN
	DECLARE @distributor sysname
	DECLARE @distribdb sysname
	DECLARE @distproc nvarchar (2000)
	DECLARE @retcode int
	
	-- constants
	
	DECLARE @ss_subscriber tinyint
	DECLARE @dsn_subscriber tinyint
	DECLARE @jet_subscriber tinyint
	DECLARE @oledb_subscriber tinyint
	DECLARE @dist_rpcname sysname
	DECLARE @platform_nt binary

    -- Defined in sqlrepl.h
	select @ss_subscriber = 0
	select @dsn_subscriber = 1    /* Const: subscriber type 'dsn' */
	select @jet_subscriber = 2    /* deprecated */
	select @oledb_subscriber = 3
	
	select @platform_nt = 0x1

	/*
	** Check if replication components are installed on this server
	*/
	exec @retcode = sys.sp_MS_replication_installed
	if (@retcode <> 1)
	begin
		return (1)
	end

	
	--  NOTE: 	This security check has been moved to the sp_addsubscriber
	--			procedure above. This is ok since sysadmin is a server levl
	--			role and is never specific to any particular database.
	
	-- /*
	-- ** Security Check: require sysadmin
	-- 	*/
	-- IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
	-- BEGIN
	-- 	RAISERROR(21089,16,-1)
	-- 	RETURN (1)
	-- END
	
	
	-- check if we need to raise a warning message that the following parameters are ignored
	if @internal = 'YUKON'
		AND (@login is not null
		    or @password is not null
		    or @commit_batch_size is not null
		    or @status_batch_size is not null
		    or @flush_frequency is not null
		    or @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 @security_mode is not null
		    or @encrypted_password is not null)
	begin
		-- "Warning: The security, batch size and scheduling parameters have been deprecated and should
		-- no longer be used. See the ''sp_addsubscriber'' documentation for more information."
		raiserror(21818, 10, -1)
	end
	
	if @commit_batch_size is NULL
		select @commit_batch_size = 100
		
	if @status_batch_size is NULL
		select @status_batch_size = 100
		
	if @flush_frequency is NULL
		select @flush_frequency	= 0
		
	if @frequency_type is NULL
		select @frequency_type = 64
		
	if @frequency_interval is NULL
		select @frequency_interval = 1
		
	if @frequency_relative_interval is NULL
		select @frequency_relative_interval	= 1

	if @frequency_recurrence_factor is NULL
		select @frequency_recurrence_factor = 0
		
	if @frequency_subday is NULL
		select @frequency_subday = 4
		
	if @frequency_subday_interval is NULL
		select @frequency_subday_interval = 5
		
	if @active_start_time_of_day is NULL
		select @active_start_time_of_day = 0
		
	if @active_end_time_of_day is NULL
		select @active_end_time_of_day = 235959
		
	if @active_start_date is NULL
		select @active_start_date = 0
		
	if @active_end_date is NULL
		select @active_end_date	= 99991231
		
	if @security_mode is NULL
		select @security_mode = 1
		
	if @encrypted_password is NULL
		select @encrypted_password = 0

	/*
	** Parameter Check: @publisher
	*/
	IF @publisher IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@publisher', 'sp_MSrepl_addsubscriber')
		RETURN (1)
	END

	/*
	** Parameter Check:  @subscriber.
	** Check to make sure that the subscriber doesn't already exist, and
	** that the name is a valid non-null identifier.
	*/
	IF @subscriber IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_addsubscriber')
		RETURN (1)
	END
	
	EXECUTE @retcode = sys.sp_validname @subscriber
	IF @@ERROR <> 0 OR @retcode <> 0
		RETURN (1)

	if LOWER(@subscriber) = 'all'
	BEGIN
		RAISERROR (14032, 16, -1, '@subscriber')
		RETURN (1)
	END

	-- Even if running a pre-yukon script, if this publisher is also a distributor for Oracle
	-- publishing, there may already be an entry in sysservers identifying this server as a subscriber
	-- to an Oracle publisher. In this case, it is not sufficient to check srvstatus of the sysservers
	-- entry in order to conclude that the server is already a subscriber to this publisher. The MSsubscriber_info
	-- table also needs to be queried to see if there is an entry for this publisher/subscriber pair.
	-- This check is being moved to later in the code when it is possible to determine whether the publisher
	-- serves as its own distributor.
	
	-- if we are in pre-yukon mode then check for existence
	--IF @internal = N'PRE-YUKON'
	--BEGIN
	--	IF EXISTS (SELECT *
        --         FROM master..sysservers
        --        WHERE UPPER(srvname) = UPPER(@subscriber) collate database_default
        --          AND srvstatus & 4 <> 0)
	
        --BEGIN
        --    RAISERROR (14040, 16, -1, @subscriber)
        --    RETURN (1)
        --END
	--END
	
	/*
	** Check for valid subscriber type
	*/
	IF (@type NOT IN (@ss_subscriber, @dsn_subscriber, @jet_subscriber, @oledb_subscriber))
	BEGIN
		RAISERROR(21396, 16, -1)
		RETURN (1)
	END

	/*
	** Check for invalid security mode
	*/
	IF @security_mode IS NOT NULL
	BEGIN
		IF @security_mode < 0 OR @security_mode > 1
		BEGIN
			RAISERROR(14109, 16, -1)
			RETURN (1)
		END

		IF (UPPER(@subscriber) = UPPER(@publisher) and @platform_nt != ( platform() & @platform_nt ) and @security_mode = 1)
		BEGIN
			RAISERROR(21038, 16, -1)
			RETURN (1)
		END
		
		-- Check to ensure a login is provided if security mode is SQL Server authentication.
		select @login = rtrim(ltrim(isnull(@login, '')))
		if @security_mode = 0 and @login = ''
		begin
			-- '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
			raiserror(21694, 16, -1, '@login', '@security_mode')
			return 1
		end
	END

	IF @password = N''
		select @password = NULL

	-- We no longer supported passing in encrypted passwords
	IF @encrypted_password = 1
	BEGIN
		-- Parameter '@encrypted_password' is no longer supported.
		RAISERROR(21698, 16, -1, '@encrypted_password')
		RETURN (1)
	END

	/*
	** Get distribution server information for remote RPC
	** subscription calls.
	*/

	EXEC @retcode = sys.sp_MSrepl_getdistributorinfo  @publisher   = @publisher,
							@distributor = @distributor  OUTPUT,
							@rpcsrvname  = @dist_rpcname OUTPUT,
							@distribdb   = @distribdb    OUTPUT
	IF @@error <> 0
	BEGIN
		RAISERROR (14071, 16, -1)
		goto undo
	END

	IF @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
	BEGIN
		RAISERROR (14071, 16, -1)
		goto undo
	END
	
	-- if we are in pre-yukon mode then check for existence
	IF @internal = N'PRE-YUKON'
	BEGIN
		IF EXISTS (SELECT *
                 FROM master..sysservers
                WHERE UPPER(srvname collate database_default) = UPPER(@subscriber) collate database_default
                  AND srvstatus & 4 <> 0)
        BEGIN
			if UPPER(@@SERVERNAME) = UPPER(@distributor)
			BEGIN
				-- If this server is a distributor, check MSsubscriber_info for
				-- an entry.  The srvstatus bit can be set either because the server is a
				-- subscriber to an Oracle publisher sharing this distributor or because
				-- the server is a subscriber to this SQL Server publisher.
				SELECT @distproc = 'IF EXISTS (SELECT * ' +
							'FROM ' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.MSsubscriber_info ' +
							'WHERE UPPER(publisher) = UPPER(' + quotename(@publisher, '''') + ') ' +
							'AND   UPPER(subscriber) = UPPER(' + quotename(@subscriber, '''') + ')) ' +
						'BEGIN ' +				
							'RAISERROR (14040, 16, -1, ' + QUOTENAME(@subscriber, '''') + ')' +
						'END '	
				EXEC (@distproc)
				IF @@error <> 0
				BEGIN
					goto undo
				END
			END
			ELSE
			-- if the sysservers entry exists as a subscriber and the publisher is not also
			-- a distributor, the server is already a subscriber to this publisher
			BEGIN
				RAISERROR (14040, 16, -1, @subscriber)
				goto undo
			END
        END
	END
	
	/*
	**  If no MSsubscriber_info parameters skip update of MSsubscriber_info
	*/
	IF @frequency_type = -1
		GOTO ADDSUB

	DECLARE @zeroint int
	SELECT @zeroint = 0
	/*
	** Insert information into MSsubscriber_info
	*/
	SELECT @distproc = QUOTENAME(RTRIM(@dist_rpcname)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_subscriber_info'
	EXEC @retcode = @distproc
			@publisher,
			@subscriber,
			@type,
			@login,
			@password,
			@commit_batch_size,
			@status_batch_size,
			@flush_frequency,
			@frequency_type,
			@frequency_interval,
			@frequency_relative_interval,
			@frequency_recurrence_factor,
			@frequency_subday,
			@frequency_subday_interval,
			@active_start_time_of_day,
			@active_end_time_of_day,
			@active_start_date,
			@active_end_date,
			/* Work around of server RPC  named parameter problem */
			@retryattempts 		= @zeroint,
			@retrydelay  		= @zeroint,
			@description 		= @description,
			@security_mode 		= @security_mode,
			@encrypted_password = @encrypted_password,
			@internal			= @internal
	IF @@error <> 0 OR @retcode <> 0
	BEGIN
		RAISERROR (14042, 16, -1)
		goto undo
	END

	/*
	** add schedule information into MSsubscriber_schedule for merge agent
	*/
	-- NOTE: We may need better default schedule for merge agent

	SELECT @distproc = QUOTENAME(RTRIM(@dist_rpcname)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_subscriber_schedule'
	EXEC @retcode = @distproc
		@publisher,
		@subscriber,
		0, -- agent_type = 0 means distribution agent
		@frequency_type,
		@frequency_interval,
		@frequency_relative_interval,
		@frequency_recurrence_factor,
		@frequency_subday,
		@frequency_subday_interval,
		@active_start_time_of_day,
		@active_end_time_of_day,
		@active_start_date,
		@active_end_date
	IF @@error <> 0 OR @retcode <> 0
	BEGIN
		RAISERROR (14042, 16, -1)
		goto undo
	END

	EXEC @retcode = @distproc
		@publisher,
		@subscriber,
		1,             --agent_type = 0 means merge agent
		4,             --frequency_type,
		1,             --frequency_interval,
		1,             --frequency_relative_interval,
		0,             --frequency_recurrence_factor,
		8,             --frequency_subday,
		1,             --frequency_subday_interval,
		0,             --active_start_time_of_day,
		235959,        --active_end_time_of_day,
		0,             --active_start_date,
		99991231       --active_end_date

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

ADDSUB:
	EXEC @retcode = sys.sp_MSadd_subserver @subscriber = @subscriber,
											@type = @type
    IF @@error <> 0 OR @retcode <> 0
	BEGIN
		goto undo
	END
	
	RETURN (0)
undo:
	RETURN (1)
END

 
Last revision 2008RTM
See also

  sp_addsubscriber (Procedure)
sp_MSdrop_replcom (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