Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSsetupnosyncsubscriptionwithlsn

  No additional text.


Syntax

-- Name: sp_MSsetupnosyncsubscriptionwithlsn

-- Description: This is an internal helper for 1) generating a setup script
--              for a nosync subscription and 2) setting up the RPC call for
--              activating the nosync subscription at the distributor.

-- Parameters: @publication     sysname (mandatory)
--             @article         sysname (optional, default = N'all')
--             @subscriber      sysnane (optional, default = null)
--             @destination_db  sysname (optional, default = null)
--             @update_mode     nvarchar(30) (optional, default = 'read only')
--             @subscriptionlsn binary(10) (mandatory)
--             @lsnsource       tinyint (optional, default = 0)
--                              0: sp_replincrementlsn, 1: backup, 2: user specified
--             @publisher       sysname (optional, default = NULL)
--             @publisher_type  sysname (optional, default = N'MSSQLSERVER')

-- Notes: 1) Since this is an internal procedure, all subscription
--           related parameters are assumed to have been validated
--           already.

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

create procedure sys.sp_MSsetupnosyncsubscriptionwithlsn
(
	@publication		sysname,
	@article			sysname = N'all',
	@subscriber			sysname = NULL,
	@destination_db		sysname = NULL,
	@update_mode		nvarchar(30) = 'read only',
	@subscriptionlsn	binary(10),
	@lsnsource			int = 0,
	@publisher			sysname = NULL,
	@publisher_type		sysname = N'MSSQLSERVER'
)
AS
BEGIN
    DECLARE @retcode					int,
            @currentdatabase			sysname,
            -- Distributor properties
            @distributor				sysname,
            @distribdb					sysname,
            -- Publication properties
            @pubid						int,
            @distproc					varchar(8000),
    		@script_txt					nvarchar(max),
    		@publisher_db_version		int,
    		@publisher_local			sysname,
            @nosync_setup_script        nvarchar(max) = N'',
            @next_valid_lsn             binary(10)

    SET NOCOUNT ON

    -- Initializations
    select @retcode = 0,
           @currentdatabase = db_name()

	IF (@publisher IS NULL) AND (@publisher_type = N'MSSQLSERVER')
	BEGIN
		SELECT	@publisher_local = publishingservername()
	END
	ELSE
	BEGIN
		SELECT	@publisher_local = @publisher
	END

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

    -- Get publication information. Note that we want to use
    -- the case-variation of the publication name as persisted in
    -- syspublications because the name shortening algo. is case-sensitive.
    -- Since all the subscription parameters are assumed to have been
    -- validated before, the publication must exist
    SELECT	@publication = name
	FROM	syspublications
	WHERE	pubid = @pubid

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

    -- Get distributor information, just need the working directory
    exec @retcode = sys.sp_helpdistributor	@publisher	= @publisher,
    										@rpcsrvname	= @distributor OUTPUT,
											@distribdb	= @distribdb OUTPUT

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

    -- Generate subscription setup script (custom procedures, sync trigger
    -- creation commands etc.)
    -- Note: Make sure that sp_MSgeneratenosyncsubscriptionsetupscript
    --       is called after sp_addsubscription so all the subscription
    --       parameters are validated.
    IF (@publisher_type = N'MSSQLSERVER')
    BEGIN
        --generate the script of processing hidden column, table persistent property and conflict table for all articles
        --for read-only subscriptions
        declare @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
        select @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8
        declare @p2pcdscript nvarchar(max)

        if exists (select * from syspublications where pubid = @pubid and
        	options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION)
        begin
        	--@article must be N'all'
	 	exec @retcode = sp_MSscriptpeerconflictdetection_tableaug @pubid = @pubid, @publication = @publication,
							@enabling = 1, @originator_id = 0x80000000, @cmdtxt = @p2pcdscript output
		if @@error <> 0 or @retcode <> 0
		begin
			select @retcode = 1
			goto Failure
		end
	end
	else
		select @p2pcdscript = NULL

        exec @retcode = sys.sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr	@publication		= @publication,
			    														        @article			= @article,
		        		    													@update_mode		= @update_mode,
				        	    												@publisher			= @publisher,
						            											@publisher_type		= @publisher_type,
                                                                                @nvarcharmaxscript  = @nosync_setup_script output
        IF (@@ERROR != 0) OR (@retcode != 0)
        BEGIN
    	    SELECT	@retcode = 1
    	    GOTO Failure
        END

	if @p2pcdscript is not NULL
		select @nosync_setup_script = @p2pcdscript + N' ' + @nosync_setup_script

    END
    ELSE
    BEGIN
        exec @retcode = sys.sp_MSgeneratenosyncsubscriptionsetupscript	@publication		= @publication,
			    														@article			= @article,
				    													@update_mode		= @update_mode,
					    												@publisher			= @publisher,
						    											@publisher_type		= @publisher_type,
                                                                        @nvarcharmaxscript  = @nosync_setup_script output
        IF (@@ERROR != 0) OR (@retcode != 0)
        BEGIN
    	    SELECT	@retcode = 1
    	    GOTO Failure
        END
    END
	-- Get the PeerToPeer meta-data commands... if the publication is not enabled for
	-- PeerToPeer then the procedure will set script_txt to NULL and will then exit.
	IF (@publisher_type = N'MSSQLSERVER')
	BEGIN
		select @script_txt = N'', @publisher_db_version = NULL

		exec sys.sp_MSgetpeertopeercommands @publication 	= @publication,
											@article        = @article,
											@script_txt 	= @script_txt output

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


	if @script_txt is not NULL
	BEGIN
		exec @retcode = sys.sp_MSgetdbversion @current_version = @publisher_db_version output

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

    -- Make RPC call to the distributor to set up the subscription
    select @distproc = quotename(rtrim(ltrim(@distributor))) + N'.' +
                       quotename(ltrim(rtrim(@distribdb))) + N'.' +
                       'dbo.sp_MSsetupnosyncsubwithlsnatdist'


    -- Grab the next valid LSN
    --   This is done in case the repl_commands table is empty and we need somewhere to place the
    --     setup commands in an init from lsn case.
    exec @retcode =  sys.sp_replincrementlsn	@xact_seqno = @next_valid_lsn OUTPUT,
        										@publisher	= @publisher

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

    exec @retcode = @distproc	@publisher					= @publisher_local,
								@publisher_db				= @currentdatabase,
								@publication				= @publication,
								@article					= @article,
								-- Subscription properties
								@subscriber					= @subscriber,
								@destination_db				= @destination_db,
								-- Subscription LSN
								@subscriptionlsn			= @subscriptionlsn,
								@lsnsource					= @lsnsource,
								-- Peer To Peer
								@originator_publication_id	= @pubid,
								@originator_db_version		= @publisher_db_version,
								@originator_meta_data		= @script_txt,
                                @nosync_setup_script        = @nosync_setup_script,
                                @next_valid_lsn             = @next_valid_lsn

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

Failure:

    RETURN @retcode
END

 
Last revision 2008RTM
See also

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