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