-- 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