Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSgeneratenosyncsubscriptionsetupscript

  No additional text.


Syntax

-- Name: sp_MSgeneratenosyncsubscriptionsetupscript

-- Description: This procedure generates the setup script for a backup
--              subscription which may include any of the following:
--              (NYI)
--              ? -1) TSQL stataments for scrubbing the restored database
--                      i) convert all timestemp columns to varbinary(16)?
--                      ii) identity columns fixups?
--                      iii) horizontal partitioning?
--                      iv) vertical partitioning ?
--              (NYI)
--              ? 0) Pull subscription setup ?
--              1) article custom procedures
--              2) conflict tables for decentralized conflict logging
--              3) immediate/queued sync triggers creation and setup
--                 commands if the subscription is queued or immediate
--                 updating.

-- Parameters: (Subscription setup parameters)
--             @publication      sysname (mandatory)
--             @article          sysname (optional, default = N'all')
--             @update_mode      nvarchar(30) (optional, default = 'read only')
--             @script_file_path nvarchar(260) (mandatory)

-- Note: 1) Since this is supposed to be an internal system procedure,
--          parameter checking is intentionally kept minimal.

-- Security: This is an internal system procedure.

create procedure sys.sp_MSgeneratenosyncsubscriptionsetupscript
(
    @publication		sysname,
    @article			sysname = N'all',
    @update_mode		nvarchar(30) = 'read only',
    @publisher			sysname = NULL,
    @publisher_type		sysname = N'MSSQLSERVER',
    @nvarcharmaxscript  nvarchar(max) output
)
as
begin
    set nocount on

    declare @retcode int,
            @table_created bit

    select @retcode = 0,
           @table_created = 0

    if @@error<> 0
    	return 1

    -- Pre-create #scripttext for scripting procs
    create table #scripttext
    (
        line_no int identity(1,1) primary key,
        line nvarchar(4000)
    )

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

    select @table_created = 1

    -- Custom procs
    exec @retcode = sys.sp_MSscriptpublicationcustomprocs	@publication	= @publication,
        													    @article		= @article,
        													    @publisher		= @publisher,
        													    @publisher_type	= @publisher_type

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

    -- If publication supports decentralized conflict logging for queue
    -- HREPL: Supports READ ONLY only
    IF (@publisher_type = N'MSSQLSERVER')
    	AND LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) in ('queued tran', 'failover')
    	AND EXISTS	(	SELECT	*
						FROM	syspublications
						WHERE	name = @publication
                		  AND	centralized_conflicts = 0
					)
    BEGIN
        EXEC @retcode = sys.sp_MSscriptpublicationconflicttables	@publication	= @publication,
																	@article		= @article

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

    -- Put synctran commands into the setup script if the subscription's
    -- update mode is not read-only
    -- HREPL - no support for anything other than READ ONLY
    IF (@publisher_type = N'MSSQLSERVER') AND
    	LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) <> N'read only'
	BEGIN
        insert into #scripttext(line)
		exec @retcode = sys.sp_script_synctran_commands	@publication = @publication,
                    									@article = @article

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

        -- Put a go at the end of the command batch
        insert #scripttext(line) values('go')

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

	-- Add commands for Setting NFR
	DECLARE @dest_owner sysname,
			@dest_table	sysname
			
	DECLARE #hArticles CURSOR LOCAL FAST_FORWARD FOR
		SELECT sysa.dest_owner,
				sysa.dest_table
		FROM syspublications sysp
			JOIN sysarticles sysa
				ON sysp.pubid = sysa.pubid
		WHERE sysp.pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

	OPEN #hArticles

	FETCH #hArticles INTO @dest_owner, @dest_table

	WHILE @@FETCH_STATUS != -1
	BEGIN
		insert #scripttext(line) values(N' IF (@@microsoftversion >= 0x09000000)'
										+ N' BEGIN'
										+ 		N' EXEC sys.sp_MSrepl_setNFR @schema = N' + QUOTENAME(@dest_owner, '''') + ','
										+ 									N' @object_name = N' + QUOTENAME(@dest_table, '''')
										+ N' END')
		IF (@retcode != 0) OR (@@ERROR != 0)
		BEGIN
			SELECT @retcode = 1
			GOTO Failure
		END
		FETCH #hArticles INTO @dest_owner, @dest_table
	END

	CLOSE #hArticles
	DEALLOCATE #hArticles
	
    -- Dump the content of the #scripttext table to the output nvarchar(max)
    -- variable
    EXEC @retcode = sys.sp_MSdumpscripttabletonvarcharmax @nvarcharmaxscript = @nvarcharmaxscript output

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

    if @table_created = 1
    begin
        drop table #scripttext
    end
    return @retcode

end

 
Last revision 2008RTM
See also

  sp_MSdrop_asm_tranrepl (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSscriptpublicationcustomprocs (Procedure)
sp_MSsetupnosyncsubscriptionwithlsn (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