Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddautonosyncsubscription

  No additional text.


Syntax

-- Name: sp_MSaddautonosyncsubscription

-- Description: This procedure represents a separate code path in
--              sp_addsubscription for adding a nosync subscription using the
--              automated nosync subscription setup support.
--              sp_addsubscription will invoke this procedure when it
--              recognizes that the value of the @sync_type parameter is one
--              of 'replication support only', 'initialize with backup', or
--              'initialize from lsn'.

-- Parameters: (Subscription setup parameters)
--             @publication                 sysname (mandatory)
--             @article                     sysname (optional, default = N'all')
--             @subscriber                  sysname (optional, default = NULL)
--             @destination_db              sysname (optional, default = NULL)
--             @sync_type                   nvarchar(80) (optional, default = N'replication support only')
--             @status                      sysname (optional, default = NULL, must be NULL or 'active')
--             @subscription_type           nvarchar(4) (optional, default = N'push')
--             @update_mode                 nvarchar(30) (optional, default = 'read only')
--             @loopback_detection          nvarchar(5) (optional, default = NULL)
--             @frequency_type              int (optional, default = NULL)
--             @frequency_interval          int (optional, default = NULL)
--             @frequency_relative_interval int (optional, default = NULL)
--             @frequency_recurrence_factor int (optional, default = NULL)
--             @frequency_subday            int (optional, default = NULL)
--             @frequency_subday_interval   int (optional, default = NULL)
--             @active_start_time_of_day    int (optional, default = NULL)
--             @active_end_time_of_day      int (optional, default = NULL)
--             @active_start_date           int (optional, default = NULL)
--             @active_end_date             int (optional, default = NULL)
--             @optional_command_line       nvarchar(4000) (optional, default = NULL)
--             @enabled_for_syncmgr         nvarchar(5) (optional, default = 'false')
--             @offloadserver               sysname (optional, default = NULL)
--             @dts_package_name            sysname (optional, default = NULL)
--             @dts_package_password        sysname (optional, default = NULL)
--             @dts_package_location        nvarchar(12) (optional, default = N'distributor')
--             @distribution_job_name       sysname (optional, default = NULL)

--             (Backup last LSN extraction properties)
--             @backupdevicetype   nvarchar(20) (optional, default = 'logical')
--             @backupdevicename   nvarchar(1000) (optional, default = null)
--             @mediapassword      sysname (optional, default = null)
--             @password           sysname (optional, default = null)
--             @fileidhint         int     (optional, default = null)
--             @unload             bit     (optional, default = 0)

--             (Explicitly specified LSN for this subscription)
--             @subscription_lsn   binary(10) (optional, default = null)
--             @subscriber_type    tinyint	(optional, default = 0)					

-- Notes: 1) Current restrictions on using the (partially) automated
--           nosync subscription setup support:
--            1) Partial subscription to a publication is not allowed.
--            2) The publication must allow initialize with backup
--               if sync_type is 'initialize with backup' or 'initialize from
--               lsn'  (i.e. allow_initialize_from_backup = 1).
--        2) The various operations performed in this procedure is not
--           fully atomic and they cannot be made as atomic. It may be
--          necessary to manually clean up the script directory
--           in certain failure scenarios (failed to commit).
--        3) SQL Server account at the publisher must have write access
--           to the distributor's working folder.
--        4) The LSN returned by sp_replincrementlsn is not validated against
--           the publication's min. autonosync lsn because it is not
--           necessary to do so.
--        5) No error will be raised if backup parameters are specified
--           for non-'autonosync with backup' subscriptions and vice
--           versa.

-- Security: This is an internal system procedure.
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSaddautonosyncsubscription(
    -- Subscription setup parameters
    @publication                 sysname,
    @article                     sysname = N'all',
    @subscriber                  sysname = NULL,
    @destination_db              sysname = NULL,
    @sync_type                   nvarchar(80) = N'replication support only',
    @status                      sysname = NULL,
    @subscription_type           nvarchar(4) = N'push',
    @update_mode                 nvarchar(30) = N'read only',
    @loopback_detection          nvarchar(5) = NULL,
    @frequency_type              int = NULL,
    @frequency_interval          int = NULL,
    @frequency_relative_interval int = NULL,
    @frequency_recurrence_factor int = NULL,
    @frequency_subday            int = NULL,
    @frequency_subday_interval   int = NULL,
    @active_start_time_of_day    int = NULL,
    @active_end_time_of_day      int = NULL,
    @active_start_date           int = NULL,
    @active_end_date             int = NULL,
    @optional_command_line       nvarchar(4000) = NULL,
    @reserved                    nvarchar(10) = NULL,
    @enabled_for_syncmgr         nvarchar(5) = 'false',
    @dts_package_name            sysname = NULL,
    @dts_package_password        sysname = NULL,
    @dts_package_location        nvarchar(12) = N'distributor',
    @distribution_job_name       sysname = NULL,
    -- Backup lsn extraction parameters
    @backupdevicetype            nvarchar(20) = 'logical',
    @backupdevicename            nvarchar(1000),
    @mediapassword               sysname = null,
    @password                    sysname = null,
    @fileidhint                  int = null,
    @unload                      bit = 1,
    -- Explicitly specified lsn for this nosync subscription
    @subscriptionlsn             binary(10) = null,
	@publisher 					 sysname = NULL,
	@publisher_type				 sysname = N'MSSQLSERVER'
	,@subscriptionstreams	tinyint = NULL
	,@subscriber_type			tinyint = 0
    )
as
begin

    DECLARE @retcode int
    DECLARE @transactionopened bit
    DECLARE @currentdatabase sysname
    DECLARE @active tinyint
	DECLARE @allow_ftp bit
	DECLARE @cleanupsubscription bit
	DECLARE @repl_freq tinyint
	DECLARE @allow_initialize_from_backup bit
	DECLARE @min_autonosync_lsn binary(10)
	DECLARE @lsnsource tinyint
	DECLARE @pubid int
	DECLARE @ispeer bit
	DECLARE @OPT_ENABLED_FOR_P2P int = 0x1

    SET NOCOUNT ON

    select @retcode = 0,
           @transactionopened = 0,
           @currentdatabase = db_name(),
           @active = 2,
           @cleanupsubscription = 0,
           @allow_initialize_from_backup = null

    -- Check if the current database is published
	IF (sys.fn_MSrepl_istranpublished(db_name(),1) = 0)
	BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    -- Validate publication name
    if @publication is null or rtrim(@publication) = N''
    begin
        raiserror (14043, 16, -1, '@publication', 'sp_MSaddautonosyncsubscription')
        return (1)
    end

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

    -- Publication existence check
    SELECT	@allow_initialize_from_backup	= allow_initialize_from_backup,
			@min_autonosync_lsn				= min_autonosync_lsn,
			@repl_freq						= repl_freq,
			@ispeer	= case (options & @OPT_ENABLED_FOR_P2P) when @OPT_ENABLED_FOR_P2P then 1 else 0 end
	FROM	syspublications
	WHERE	pubid = @pubid

    IF (@@ERROR != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO	Failure
    END

    -- allow_initialize_from_backup cannot be null, if it is, then the publication
    -- does not exist
    if @allow_initialize_from_backup is null
    begin
        raiserror(20026, 11, -1, @publication)
        return 1
    end

    -- Publication cannot be a snapshot publication
    if @repl_freq <> 0
    begin
        raiserror(18787, 16, -1)
        return 1
    end

    -- Initial subscription status must be active or null (default)
    select @status = lower(@status collate SQL_Latin1_General_CP1_CS_AS)
    if @status is not null and @status <> N'active'
    begin
        raiserror(21408, 16, -1)
        return 1
    end

    -- Obtain the autonosync subscription lsn depending on the
    -- @sync_type value. Note that @sync_type has already
    -- been validated as one of the autonosync types in sp_addsubscription

    select @sync_type = lower(@sync_type collate SQL_Latin1_General_CP1_CS_AS)
    select @lsnsource =	case
    						when @sync_type = N'initialize from lsn' then 2
							when @sync_type = N'initialize with backup' then 1
							-- @sync_type = N'replication support only'
							else 0
						end

    IF (@@ERROR != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO	Failure
    END

    -- Publication must be enabled for automated nosync support for the
    -- 'initialize with backup' and 'initialize from lsn' sync types.
    if @lsnsource <> 0
    begin
        if @allow_initialize_from_backup = 0
        begin
            raiserror(18786, 16, -1)
            return 1
        end
    end

    -- The entire publication must be subscribed at once unless
    -- this is a "replication support only" subscription
    if lower(@article collate SQL_Latin1_General_CP1_CS_AS) <> N'all'
    begin
        if @lsnsource <> 0
        begin
            raiserror(21407, 16, -1)
            return 1
        end
    end
    else
    begin
        select @article = N'all'
    end

    if @lsnsource = 2
    begin
        -- Just make sure that the given lsn is not null
        if @subscriptionlsn is null
        begin
            raiserror(14043, 16, -1, '@subscriptionlsn', 'sp_MSaddautonosyncsubscription')
            select @retcode = 1 goto Failure
        end
    end
    else if @lsnsource = 1
    begin
        -- Extract lsn from backup
        exec @retcode = sys.sp_MSextractlastlsnfrombackup
                            @backupdevicetype = @backupdevicetype,
                            @backupdevicename = @backupdevicename,
                            @mediapassword = @mediapassword,
                            @password = @password,
                            @fileidhint = @fileidhint,
                            @unload = @unload,
                            --avoid replacing this into publishingservername for now, it's used to find the physical server name stored in file header
                            @backupservername = @@servername,
                            @backupdatabase = @currentdatabase,
                            @lastlsn = @subscriptionlsn output
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
    end

    -- Make sure that the subscription lsn is greater than the publication's
    -- min autonosync lsn, don't need to check the one obtained from
    -- sp_replincrementlsn
    if @subscriptionlsn < @min_autonosync_lsn and @lsnsource <> 0
    begin
        if @lsnsource = 2 and @ispeer = 0
	--bug 122094 (PS ID 540867): in case @lsnsource = 2 and @ispeer = 1, do not raise an error, because for p2p, it is possible that @subscriptionlsn is less than @min_autonosync_lsn
	 begin
            raiserror(21399, 16, -1)
            return 1
        end
        else if @lsnsource = 1
        begin
            raiserror(21397, 16, -1)
	     return 1
	 end
    end

    -- Add nosync subscription in a subscribed state

    exec @retcode = sys.sp_addsubscription
            @publication = @publication,
            @article = @article,
            @subscriber = @subscriber,
            @destination_db = @destination_db,
            @sync_type = N'none',
            @status = N'subscribed',
            @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 = N'nosync',
            @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
			,@subscriptionstreams = @subscriptionstreams
			,@subscriber_type = @subscriber_type
    if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

	
    select @cleanupsubscription = 1

    -- If the specified @destination_db is null, try to select it
    -- back from syssubscriptions
    IF @destination_db IS NULL
    BEGIN
        IF EXISTS
        (
			SELECT	*
			FROM	syssubscriptions sub
				WHERE	sub.srvname = upper(@subscriber)
					and (sub.srvname is not null and len(sub.srvname)> 0)
			  AND	sub.dest_db = db_name()
			  AND	artid in
							(
								SELECT	artid
								FROM	dbo.sysextendedarticlesview sa,
										dbo.syspublications sp
								WHERE	sa.pubid = sp.pubid
								  AND	sp.pubid = @pubid
								  AND	(@article = N'all' or @article = sa.name)
							)
		)
        BEGIN
            SELECT	@destination_db = db_name()
        END
        ELSE
        BEGIN
            SELECT	@destination_db = formatmessage(20586)
        END
    END

    begin tran
    save tran sp_MSaddautonosyncsubscription

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT @retcode = 1
    	GOTO Failure
    END

    SELECT @transactionopened = 1

    -- For a sync type of 'replication support only'  we perform just-in-time
    -- activation of article objects
    IF @lsnsource = 0
    BEGIN
        EXEC @retcode = sys.sp_MSenableautonosync	@publication = @publication,
        											@activate_articles_only = 1,
        											@publisher = @publisher,
        											@publisher_type = @publisher_type
        											
		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT @retcode = 1
			GOTO Failure
		END

        EXEC @retcode = sys.sp_replincrementlsn	@xact_seqno = @subscriptionlsn OUTPUT,
        										@publisher	= @publisher

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT @retcode = 1
			GOTO Failure
		END
    END

    -- Update the subscription status in dbo.syssubscriptions directly
    -- to active. Activation of subscription at the distributor will
    -- be handled by sp_MSsetupnosyncsubscriptionwithlsn
    UPDATE	syssubscriptions
       SET	status = @active,
			queued_reinit =	CASE
								WHEN LOWER(@update_mode COLLATE SQL_Latin1_General_CP1_CS_AS) = N'read only' then  1
                                ELSE 0
							END,
			nosync_type = @lsnsource + 1
	WHERE	srvname = UPPER(@subscriber)
	and (srvname is not null and len(srvname)> 0)
	AND	dest_db = @destination_db
	  AND	artid IN (	SELECT	artid
						FROM	sysextendedarticlesview sa,
								syspublications sp
						WHERE	sa.pubid = sp.pubid
						  AND	sp.pubid = @pubid
						  AND	(@article = N'all' OR @article = sa.name))

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT @retcode = 1
    	GOTO Failure
    END

    EXEC @retcode = sys.sp_MSsetupnosyncsubscriptionwithlsn	@publication		= @publication,
															@article			= @article,
															@subscriber			= @subscriber,
															@destination_db		= @destination_db,
															@update_mode		= @update_mode,
															@subscriptionlsn	= @subscriptionlsn,
															@lsnsource			= @lsnsource,
															@publisher			= @publisher,
															@publisher_type		= @publisher_type
														

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT @retcode = 1
    	GOTO Failure
    END

				
    commit tran sp_MSaddautonosyncsubscription

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT @retcode = 1
    	GOTO Failure
    END

    SELECT	@transactionopened   = 0,
			@cleanupsubscription = 0

Failure:
    IF @transactionopened = 1
    BEGIN
        ROLLBACK TRAN sp_MSaddautonosyncsubscription
        COMMIT TRAN sp_MSaddautonosyncsubscription
    END

    IF @cleanupsubscription = 1
    BEGIN
        -- Ignore errors
        EXEC sp_dropsubscription	@publication	= @publication,
									@article		= N'all',
									@subscriber		= @subscriber,
									@destination_db	= @destination_db,
									@publisher		= @publisher
    END

    RETURN @retcode
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