April 13, 2012

sp_addmergepublication (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_addmergepublication(nvarchar @publication
, nvarchar @description
, int @retention
, nvarchar @sync_mode
, nvarchar @allow_push
, nvarchar @allow_pull
, nvarchar @allow_anonymous
, nvarchar @enabled_for_internet
, nvarchar @centralized_conflicts
, nvarchar @dynamic_filters
, nvarchar @snapshot_in_defaultfolder
, nvarchar @alt_snapshot_folder
, nvarchar @pre_snapshot_script
, nvarchar @post_snapshot_script
, nvarchar @compress_snapshot
, nvarchar @ftp_address
, int @ftp_port
, nvarchar @ftp_subdirectory
, nvarchar @ftp_login
, nvarchar @ftp_password
, int @conflict_retention
, nvarchar @keep_partition_changes
, nvarchar @allow_subscription_copy
, nvarchar @allow_synctoalternate
, nvarchar @validate_subscriber_info
, nvarchar @add_to_active_directory
, int @max_concurrent_merge
, int @max_concurrent_dynamic_snapshots
, nvarchar @use_partition_groups
, nvarchar @publication_compatibility_level
, int @replicate_ddl
, nvarchar @allow_subscriber_initiated_snapshot
, nvarchar @allow_web_synchronization
, nvarchar @web_synchronization_url
, nvarchar @allow_partition_realignment
, nvarchar @retention_period_unit
, int @generation_leveling_threshold
, bit @automatic_reinitialization_policy
, nvarchar @conflict_logging)

MetaData:

   
create procedure sys.sp_addmergepublication (
@publication sysname, -- Publication name --
@description nvarchar(255)= NULL, -- Publication description --
@retention int = 14, -- Retention period of 14 days --
@sync_mode nvarchar(10) = 'native', -- (bcp)native, (bcp)character --
@allow_push nvarchar(5) = 'true', -- Pulication allows push subscriptions --
@allow_pull nvarchar(5) = 'true', -- Pulication allows pull subscriptions --
@allow_anonymous nvarchar(5) = 'true', -- Pulication allows anonymous subscriptions --
@enabled_for_internet nvarchar(5) = 'false', -- Pulication is enabled for internet --
@centralized_conflicts nvarchar(5) = null, -- deprecated, use @conflict_logging instead --
@dynamic_filters nvarchar(5) = 'false', -- Will publication be filtered on dynamic clause? --
@snapshot_in_defaultfolder nvarchar(5) = 'true', -- Will keep a copy of the snapshot files to the default location if an alternate folder is specified --
@alt_snapshot_folder nvarchar(255) = NULL, -- Alternate folder for putting the snapshot file for this publication --
@pre_snapshot_script nvarchar(255) = NULL, -- Pre snapshot commands --
@post_snapshot_script nvarchar(255) = NULL, -- Post snapshot commands --
@compress_snapshot nvarchar(5) = 'false', -- Snapshot compression --
@ftp_address sysname = NULL, -- Post 7.0 FTP Properties --
@ftp_port int = 21, -- Post 7.0 FTP Properties --
@ftp_subdirectory nvarchar(255) = 'ftp', -- Post 7.0 FTP Properties --
@ftp_login sysname = N'anonymous', -- Post 7.0 FTP Properties --
@ftp_password sysname = NULL, -- Post 7.0 FTP Properties --
@conflict_retention int = 14, -- Conflict retention period --
@keep_partition_changes nvarchar(5) = 'true', -- Optimized Partition Updates/Deletes --
@allow_subscription_copy nvarchar(5) = 'false', -- Allow the subscription to be copied --
@allow_synctoalternate nvarchar(5) = 'false', -- Allow the subscription to be synchronize to alternate partners --
@validate_subscriber_info nvarchar(500) = NULL, -- Should we validate that subscriber is using right params? --
@add_to_active_directory nvarchar(5) = 'false',
@max_concurrent_merge int = 0, -- value of 0 means no such limit exists --
@max_concurrent_dynamic_snapshots int = 0, -- Maximum number of concurrent dynamic snapshot sessions --
@use_partition_groups nvarchar(5) = NULL, -- NULL = Let system decide, otherwise manually set to 'true' or 'false' --
@publication_compatibility_level nvarchar(6) = '80SP3', -- backward compatibility level, default=Sphinx
@replicate_ddl int = NULL, -- for now we only use value 1 for alter table add/drop columns and other alter table sub-statements.
-- we may need more bits for "create table, drop table, " and others
@allow_subscriber_initiated_snapshot nvarchar(5) = 'false',
@allow_web_synchronization nvarchar(5) = 'false',
@web_synchronization_url nvarchar(500) = null, -- Default value of InternetURL for subscriptions
@allow_partition_realignment nvarchar(5) = 'true', -- true = send deletes for rows that leave partition, false=do not send deletes for rows that leave partition.
@retention_period_unit nvarchar(10) = 'day', -- possible values are 'day', 'days', 'dd', 'year', 'years', 'yy', 'yyyy', 'month', 'months', 'mm', 'week', 'weeks', 'wk', 'hour', 'hours', 'hh', 'minute', 'minutes', 'mi'
@generation_leveling_threshold int = 1000, -- determines approximately how many row changes at most will be put in one generation. 0 = no limit.
@automatic_reinitialization_policy bit = 0, -- 0=no upload first on automatic reinit; 1=upload first
@conflict_logging nvarchar(15) = NULL -- 'publisher', 'subscriber', or 'both'. Default is 'publisher' for <90 compat level and 'both' for 90 compat level
)
as

set nocount on

--
-- Declarations.
--

declare @retcode int -- return code value for procedure execution --
declare @push tinyint -- subscription type is push --
declare @statid tinyint -- status id based on @status --
declare @sync_modeid tinyint -- sync mode id based on @sync_mode --
declare @global tinyint -- subscriber type of loop-back subscription --
declare @db_name sysname -- database name --
declare @replnick binary(6) -- replica nickname --
declare @tranpublish_bit smallint -- online publish bit (flag) in sysdatabases --
declare @mergepublish_bit smallint -- merge publish bit (flag) in sysdatabases --
declare @found int -- flag indicating if publication is found --
declare @pubid uniqueidentifier -- Publication identifier --
declare @allow_push_id bit
declare @allow_pull_id bit
declare @allow_anonymous_id bit
declare @dynamic_filters_id bit
declare @allow_subscription_copy_id bit
declare @allow_synctoalternate_id bit
declare @allow_web_synchronization_id bit
declare @enabled_for_internet_id bit
declare @conflogging int
declare @priority real
declare @automatic tinyint
declare @false bit
declare @true bit
declare @distributor sysname
declare @distproc nvarchar(300)
declare @distribdb sysname
declare @distpubid int
declare @full int
declare @snapshot_in_defaultfolder_bit bit
declare @compress_snapshot_bit bit
declare @keep_before_values_int int
declare @enc_ftp_password nvarchar(524)
declare @ad_guidname sysname
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(2000)
declare @artid uniqueidentifier
declare @distservername sysname
declare @use_partition_groups_id smallint
declare @allow_subscriber_initiated_snapshot_id bit
declare @allow_partition_realignment_id bit
declare @REPOLEVersion_70RTM int
declare @REPOLEVersion_70SP1 int
declare @REPOLEVersion_70SP2 int
declare @REPOLEVersion_80RTM int
declare @REPOLEVersion_90 int
declare @REPOLEVersion_100 int
declare @REPOLEVersion_70RTM_string nvarchar(5)
declare @REPOLEVersion_70SP1_string nvarchar(5)
declare @REPOLEVersion_70SP2_string nvarchar(5)
declare @REPOLEVersion_70SP3_string nvarchar(5)
declare @REPOLEVersion_70SP4_string nvarchar(5)
declare @REPOLEVersion_80RTM_string nvarchar(5)
declare @REPOLEVersion_80SP1_string nvarchar(5)
declare @REPOLEVersion_80SP2_string nvarchar(5)
declare @REPOLEVersion_80SP3_string nvarchar(5)
declare @REPOLEVersion_90RTM_string nvarchar(5)
declare @REPOLEVersion_100RTM_string nvarchar(6)
declare @pubcompat_level_id int
declare @retention_period_unit_id tinyint
declare @working_directory nvarchar(255)
,@publishingservername sysname
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0


if (sys.fn_MSrepl_editionid() in (22, 40)) -- SQL Express or SQL_Web
begin
raiserror(21106, 16, -1) -- This edition of SQL Server does not support publications.
return (1)
end

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @mergepublish_bit = 4
select @tranpublish_bit = 1
select @priority = 100.0
select @automatic = 1 -- Const: synchronization type 'automatic' --
select @true = 1
select @false = 0
select @full = 0 -- Const: publication type 'full' --

set @REPOLEVersion_70RTM= 10
set @REPOLEVersion_70SP1= 20
set @REPOLEVersion_70SP2= 30
set @REPOLEVersion_80RTM= 40
set @REPOLEVersion_90= 90

set @REPOLEVersion_100= 100

set @REPOLEVersion_70RTM_string = N'70RTM'
set @REPOLEVersion_70SP1_string = N'70SP1'
set @REPOLEVersion_70SP2_string = N'70SP2'
set @REPOLEVersion_70SP3_string = N'70SP3'
set @REPOLEVersion_70SP4_string = N'70SP4'
set @REPOLEVersion_80RTM_string = N'80RTM'
set @REPOLEVersion_80SP1_string = N'80SP1'
set @REPOLEVersion_80SP2_string = N'80SP2'
set @REPOLEVersion_80SP3_string = N'80SP3'
set @REPOLEVersion_90RTM_string = N'90RTM'
set @REPOLEVersion_100RTM_string = N'100RTM'


--
-- Set the status to Active (1)
--
select @statid = 1
select @global = 1
select @push = 0
select @db_name = DB_NAME()
select @ad_guidname = NULL
,@publishingservername = publishingservername()

--
-- Security Check
--

EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

-- Parameter check: @publication_compatibility_level: Allowed range.
if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70RTM_string
begin
select @pubcompat_level_id = @REPOLEVersion_70RTM
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP1_string
begin
select @pubcompat_level_id = @REPOLEVersion_70SP1
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP2_string
begin
select @pubcompat_level_id = @REPOLEVersion_70SP2
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP3_string
begin
select @pubcompat_level_id = @REPOLEVersion_70SP2
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_70SP4_string
begin
select @pubcompat_level_id = @REPOLEVersion_70SP2
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80RTM_string
begin
select @pubcompat_level_id = @REPOLEVersion_80RTM
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP1_string
begin
select @pubcompat_level_id = @REPOLEVersion_80RTM
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP2_string
begin
select @pubcompat_level_id = @REPOLEVersion_80RTM
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_80SP3_string
begin
select @pubcompat_level_id = @REPOLEVersion_80RTM
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_90RTM_string
begin
select @pubcompat_level_id = @REPOLEVersion_90
end
else if UPPER(@publication_compatibility_level collate SQL_Latin1_General_CP1_CS_AS) = @REPOLEVersion_100RTM_string
begin
select @pubcompat_level_id = @REPOLEVersion_100
end
else
begin
raiserror(21402, 16, -1, '@publication_compatibility_level')
return (1)
end

if (@pubcompat_level_id not in
(
@REPOLEVersion_70RTM,
@REPOLEVersion_70SP1,
@REPOLEVersion_70SP2,
@REPOLEVersion_80RTM,
@REPOLEVersion_90,
@REPOLEVersion_100
)
)
begin
raiserror(21402, 16, -1, '@publication_compatibility_level')
return (1)
end



-- Parameter check: @publication_compatibility_level: A Katmai publication db can
-- only have Katmai compatible publications for the new data types.
-- We will remove this constraint during the next phase of NewDateTime improvement
-- yiche 12.7.2006
if @REPOLEVersion_100 = @pubcompat_level_id and
exists (select * from dbo.sysmergepublications where
backward_comp_level < @REPOLEVersion_100)
begin
raiserror(21527, 16, -1, @publication, @db_name)
return (1)
end
-- Parameter check: @publication_compatibility_level: A Yukon publication db can either have
-- downlevel or Yukon compatible publications, but not both.
else if @REPOLEVersion_90 = @pubcompat_level_id and
exists (select * from dbo.sysmergepublications where
backward_comp_level < @REPOLEVersion_90)
begin
raiserror(21527, 16, -1, @publication, @db_name)
return (1)
end
else if @REPOLEVersion_90 > @pubcompat_level_id and
exists (select * from dbo.sysmergepublications where
backward_comp_level >= @REPOLEVersion_90)
begin
raiserror(21528, 16, -1, @publication, @db_name)
return (1)
end

--
-- Parameter Check: @publication.
-- The @publication name must conform to the rules for identifiers,
-- and must not be the keyword 'all'.
--
exec @retcode = sys.sp_MSreplcheck_name @publication, '@publication', 'sp_addmergepublication'
if @@ERROR <> 0 or @retcode <> 0
return(1)

if LOWER (@publication) = 'all'
begin
raiserror (14034, 16, -1)
return (1)
end

if @max_concurrent_merge<0
begin
raiserror(21402, 16, -1, '@max_concurrent_merge')
return (1)
end

--
-- Parameter Check: @retention.
--

if @retention_period_unit is null
or lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) not in ('day', 'days', 'dd', 'year', 'years', 'yy', 'yyyy', 'month', 'months', 'mm', 'week', 'weeks', 'wk', 'hour', 'hours', 'hh', 'minute', 'minutes', 'mi')
begin
raiserror(22527, 16, -1, @retention_period_unit)
return 1
end

select @retention_period_unit_id =
case when lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) in ('day', 'days', 'dd')
then 0
when lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) in ('week', 'weeks', 'wk')
then 1
when lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) in ('month', 'months', 'mm')
then 2
when lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) in ('year', 'years', 'yy', 'yyyy')
then 3
when lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) in ('hour', 'hours', 'hh')
then 4
when lower(@retention_period_unit collate SQL_Latin1_General_CP1_CI_AS) in ('minute', 'minutes', 'mi')
then 5
end

if @retention_period_unit_id <> 0 and @pubcompat_level_id < @REPOLEVersion_90
begin
raiserror(22528, 16, -1, @publication)
return 1
end

if @retention is NULL
begin
select @retention = 0
end
else if @retention < 0 or @retention > sys.fn_units_until_maxdate(@retention_period_unit_id, getdate())
begin
raiserror(20050, 16, -1, 0)
return(1)
end

--
-- Parameter Check: @conflict_retention.
--

if @conflict_retention is not NULL and @conflict_retention<0
begin
raiserror(20050, 16, -1, 0)
return(1)
end

--
-- if it is NULL, use the default value of 14 days.
--
if @conflict_retention is NULL
select @conflict_retention = 14

--
-- Parameter Check: @sync_mode.
-- Make sure that the sync_mode is one of the following:
--
-- id sync_mode
-- == ==========
-- 0 (bcp)native
-- 1 (bcp)character
--

if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS)='portable' select @sync_mode='character'

if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) is NULL OR LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('bcp native', 'bcp character', 'native', 'character')
begin
raiserror (20076, 16, -1)
return (1)
end

if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) = 'native' or
LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS)='bcp native'
select @sync_modeid = 0
else
select @sync_modeid = 1

if @retention_period_unit_id <> 0 and @sync_modeid = 1
begin
raiserror(22555, 16, -1, @publication)
return 1
end

--
-- Parameter Check: @allow_push.
--

if @allow_push IS NULL OR LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_push')
RETURN (1)
END

if LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) = 'true'
select @allow_push_id = 1
else
select @allow_push_id = 0

--
-- Parameter Check: @allow_pull.
--

if @allow_pull IS NULL OR LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_pull')
RETURN (1)
END
if LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) = 'true'
select @allow_pull_id = 1
else
select @allow_pull_id = 0

--
-- Parameter Check: @allow_anonymous.
--

if @allow_anonymous IS NULL OR LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_anonymous')
RETURN (1)
END
if LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) = 'true'
select @allow_anonymous_id = 1
else
select @allow_anonymous_id = 0


if @generation_leveling_threshold is null
set @generation_leveling_threshold=0

if (@generation_leveling_threshold < 0)
begin
raiserror(21119, 16, -1, @generation_leveling_threshold, '@generation_leveling_threshold')
return 1
end

--
-- Parameter Check: @enabled_for_internet.
--

IF @enabled_for_internet IS NULL OR LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@enabled_for_internet')
RETURN (1)
END

IF LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @enabled_for_internet_id = 1
ELSE
SELECT @enabled_for_internet_id = 0

--
-- Parameter Check: @conflict_logging, @centralized_conflicts.
--

-- Pick defaults based on pubcompat_level
if @conflict_logging is null
begin
if @pubcompat_level_id < @REPOLEVersion_90
set @conflict_logging = 'publisher'
else
set @conflict_logging = 'both'
end

if @centralized_conflicts is not null
begin
raiserror (21767, 10, 1, '@centralized_conflicts', '@conflict_logging')

set @conflogging=
case LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS)
when 'true' then 1
when 'false' then 0
else null
end

if @conflogging is null
begin
RAISERROR (14148, 16, -1, '@centralized_conflicts')
RETURN (1)
end
end
else if @conflict_logging is not null
begin
set @conflogging=
case LOWER(@conflict_logging collate SQL_Latin1_General_CP1_CS_AS)
when 'publisher' then 1
when 'subscriber' then 0
when 'both' then 2
else null
end

if @conflogging is null
begin
RAISERROR (14103, 16, -1, '@conflict_logging')
RETURN (1)
end
end
else
begin
RAISERROR (14103, 16, -1, '@conflict_logging')
RETURN (1)
end

if @conflogging = 2 and
@pubcompat_level_id < @REPOLEVersion_90
begin
RAISERROR (20705, 16, -1, @publication)
RETURN (1)
end
else if @conflogging = 0 and
@pubcompat_level_id < @REPOLEVersion_70SP2
begin
raiserror(21349, 10, -1, @publication)
set @pubcompat_level_id= @REPOLEVersion_70SP2
end

--
-- Parameter Check: @dynamic_filter.
--

IF @dynamic_filters IS NULL OR LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@dynamic_filters')
RETURN (1)
END

IF LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @dynamic_filters_id = 1
ELSE
SELECT @dynamic_filters_id = 0

if @validate_subscriber_info is not NULL
begin
if @dynamic_filters_id = 0
begin
raiserror(21313, 16, -1)
return (1)
end
end
else if @pubcompat_level_id >= @REPOLEVersion_90
select @validate_subscriber_info = N'' -- Empty string = auto-detect. NULL = don't use the feature.
-- NULL is not available for new 90 compat publications any more.

--
-- Parameter Check: @allow_partition_realignment.
--

IF @allow_partition_realignment IS NULL OR LOWER(@allow_partition_realignment collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_partition_realignment')
RETURN (1)
END

IF LOWER(@allow_partition_realignment collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @allow_partition_realignment_id = 1
ELSE
SELECT @allow_partition_realignment_id = 0

-- Portable snapshot
IF @snapshot_in_defaultfolder IS NULL OR LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder')
RETURN (1)
END

IF LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) = 'true'
BEGIN
SELECT @snapshot_in_defaultfolder_bit = 1
END
ELSE
BEGIN
SELECT @snapshot_in_defaultfolder_bit = 0
END

-- Pre/Post snapshot commands
-- If @sync_method is character mode bcp, this would indicate that
-- this publication may support non-SQL Server subscribers. In this
-- case, pre- and post- snapshot commands are not allowed.
IF @sync_modeid = 1 AND
((@pre_snapshot_script IS NOT NULL AND @pre_snapshot_script <> N'' ) OR
(@post_snapshot_script IS NOT NULL AND @post_snapshot_script <> N''))
BEGIN
RAISERROR (21151, 16, -1)
RETURN (1)
END

-- Parameter check - @compress_snapshot
-- @compress_snapshot can be 1 if @alt_snapshot_folder is non-null
IF @compress_snapshot IS NULL OR LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@compress_snapshot')
RETURN (1)
END

IF LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) = 'true'
BEGIN
SELECT @compress_snapshot_bit = 1
END
ELSE
BEGIN
SELECT @compress_snapshot_bit = 0
END


-- Only bump up the compatibility level if only a compressed snapshot
-- is generated at the alternate snapshot folder
if @snapshot_in_defaultfolder_bit = 0 and
@compress_snapshot_bit = 1
begin
if @pubcompat_level_id < @REPOLEVersion_80RTM
begin
raiserror(21350, 10, -1, @publication)
set @pubcompat_level_id= @REPOLEVersion_80RTM
end
end

-- Snapshot compression can only be enabled if an alternate
-- snapshot generation folder exists.
IF (@compress_snapshot_bit = 1 AND
(@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N''))
BEGIN
RAISERROR (21157, 16, -1)
RETURN (1)
END

-- Parameter check: ftp_address
-- If the publication is enabled for internet, ftp_address cannot be null
IF @enabled_for_internet_id = 1 AND (@ftp_address IS NULL OR @ftp_address = N'')
BEGIN
RAISERROR (21158, 16, -1)
RETURN (1)
END

-- Parameter check: ftp_port
IF @ftp_port IS NULL OR @ftp_port < 0 OR @ftp_port > 65535
BEGIN
RAISERROR (21160, 16, -1)
return 1
END

if @ftp_address IS not NULL and @ftp_address <> N''
begin
select @enabled_for_internet_id = 1
end

-- Encrypt ftp password before putting it into the dbo.sysmergepublications
-- table if one is provided
SELECT @enc_ftp_password = NULL
IF @ftp_password IS NOT NULL
BEGIN
SELECT @enc_ftp_password = @ftp_password
EXEC @retcode = sys.sp_MSreplencrypt @enc_ftp_password OUTPUT
IF @retcode <> 0
BEGIN
RETURN (1)
END
END

--
-- Parameter Check: @keep_partition_changes.
--

if LOWER(@keep_partition_changes collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@keep_partition_changes')
RETURN (1)
END

if LOWER(@keep_partition_changes collate SQL_Latin1_General_CP1_CS_AS) = 'true'
begin
set @keep_before_values_int = 1
end
else
set @keep_before_values_int = 0

--
-- Parameter Check: @allow_subscription_copy_id.
--

if LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_subscription_copy')
RETURN (1)
END

IF LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @allow_subscription_copy_id = 1
ELSE
SELECT @allow_subscription_copy_id = 0

--
-- Parameter Check: @use_partition_groups.
--

if @use_partition_groups is not null and
LOWER(@use_partition_groups collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@use_partition_groups')
RETURN (1)
END

IF LOWER(@use_partition_groups collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @use_partition_groups_id = 2
ELSE IF LOWER(@use_partition_groups collate SQL_Latin1_General_CP1_CS_AS) = 'false'
SELECT @use_partition_groups_id = 0
ELSE
SELECT @use_partition_groups_id = NULL -- Let system decide whether it should be true or false.

-- Keep-before-image functionality is not supported if partition id's are in use and backward
-- compat level is 90 or higher.
if @use_partition_groups_id = 2 and @pubcompat_level_id >= @REPOLEVersion_90
and @keep_before_values_int = 1
begin
select @keep_before_values_int = 0
-- raiserror(21729, 16, -1)
end

--
-- Parameter Check: @allow_synctoalternate_id.
--

if LOWER(@allow_synctoalternate collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_synctoalternate')
RETURN (1)
END

IF LOWER(@allow_synctoalternate collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @allow_synctoalternate_id = 1
ELSE
SELECT @allow_synctoalternate_id = 0

if LOWER(@allow_web_synchronization collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_web_synchronization')
RETURN (1)
END

IF LOWER(@allow_web_synchronization collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @allow_web_synchronization_id = 1
ELSE
SELECT @allow_web_synchronization_id = 0
--
-- Parameter Check: @max_concurrent_dynamic_snapshots
--

if @max_concurrent_dynamic_snapshots < 0 or @max_concurrent_dynamic_snapshots is null
begin
raiserror(21403, 16, -1)
return (1)
end

--
-- Parameter Check: @allow_subscriber_initiated_snapshot
--

if LOWER(@allow_subscriber_initiated_snapshot collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@allow_subscriber_initiated_snapshot')
RETURN (1)
END

IF LOWER(@allow_subscriber_initiated_snapshot collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @allow_subscriber_initiated_snapshot_id = 1
ELSE
SELECT @allow_subscriber_initiated_snapshot_id = 0

--
-- Parameter Check: @replicate_ddl
--
-- the default for @replicate_ddl is NULL. Choose a default of 0 or 1 based on the pub compat level
if @replicate_ddl is NULL
begin
if @pubcompat_level_id < @REPOLEVersion_90
select @replicate_ddl = 0
else
select @replicate_ddl = 1
end
else
begin
if @replicate_ddl <> 0 and @pubcompat_level_id < @REPOLEVersion_90
begin
RAISERROR (20650, 16, -1, @publication)
RETURN (1)
end
if @replicate_ddl > 1
select @replicate_ddl = 1
end

--
-- Check to see if the publication name is already used.
-- 1. check merge pubs
-- 2. check online publications
--
if exists (select * from dbo.sysmergepublications
where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name())
begin
RAISERROR (20025, 16, -1, @publication)
RETURN (1)
end

if (select category & @tranpublish_bit from master.dbo.sysdatabases where name = @db_name collate database_default) <> 0
begin
EXEC @retcode = sys.sp_helppublication @publication, @found output

if @@ERROR <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END

if @found <> 0
BEGIN
RAISERROR (20025, 16, -1, @publication)
RETURN (1)
END
end


--
-- Add the publication as the designmaster of the replica set.
--

-- Generate a guid for the publication ID --
set @pubid = newid()

-- Look for existing nickname from any other subscription --
exec @retcode= sys.sp_MSgetreplnick NULL, NULL , NULL, @replnick out
if (@@error <> 0) or @retcode <> 0
begin
RETURN(1)
end

-- Generate a new replica nickname from the @pubid --
if (@replnick is null)
begin
execute @retcode = sys.sp_MSgenreplnickname
@srcguid= @pubid,
@replnick= @replnick output,
@compatlevel= @pubcompat_level_id
IF @@ERROR <>0 OR @retcode <> 0
BEGIN
RAISERROR (20077, 16, -1)
RETURN (1)
END
end
else
begin
-- if replnick is not null it means that this is a republisher
-- check to see if there are any anonymous or local subscriptions
if exists (select 1 from dbo.sysmergesubscriptions
where db_name=@db_name and
UPPER(subscriber_server) collate database_default = UPPER(publishingservername()) collate database_default and
subscriber_type > 1 and
status <> 7) -- REPLICA_STATUS_BeforeRestore
begin
RAISERROR(21087, 16, -1)
return (1)
end

-- check to see if there are global subscriptions with prority 0
select @priority=max(priority) from dbo.sysmergesubscriptions
where db_name=@db_name and
UPPER(subscriber_server) collate database_default = UPPER(publishingservername()) collate database_default and
status <> 7 -- REPLICA_STATUS_BeforeRestore
if @priority = 0
begin
RAISERROR(21091, 16, -1)
return (1)
end
end


--
-- Get distributor information
--
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT
,@distributor = @distservername OUTPUT
,@distribdb = @distribdb OUTPUT
,@working_directory = @working_directory OUTPUT
if @@error <> 0 OR @retcode <> 0 or @distributor IS NULL OR @distribdb IS NULL or @working_directory is null
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

--
-- add an entry into dbo.sysmergepublications
--
begin tran
save tran sp_addmergepublication

exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(20713, 16, -1, 'sp_addmergepublication', @publication)
goto FAILURE
end

select @got_merge_admin_applock = 1

-- Add row in the publications table --
insert dbo.sysmergepublications
(pubid,
name,
description,
designmasterid,
retention,
parentid,
sync_mode,
allow_push,
allow_pull,
allow_anonymous,
centralized_conflicts,
decentralized_conflicts,
status,
snapshot_ready,
enabled_for_internet,
publication_type,
dynamic_filters,
snapshot_in_defaultfolder,
alt_snapshot_folder,
pre_snapshot_script,
post_snapshot_script,
compress_snapshot,
ftp_address,
ftp_port,
ftp_subdirectory,
ftp_login,
ftp_password,
conflict_retention,
keep_before_values,
allow_subscription_copy,
allow_synctoalternate,
validate_subscriber_info,
ad_guidname,
backward_comp_level,
max_concurrent_merge,
max_concurrent_dynamic_snapshots,
use_partition_groups,
replicate_ddl,
allow_subscriber_initiated_snapshot,
distributor,
allow_web_synchronization,
web_synchronization_url,
allow_partition_realignment,
retention_period_unit,
generation_leveling_threshold,
automatic_reinitialization_policy)
values
(@pubid,
@publication,
@description,
@pubid,
@retention,
@pubid,
@sync_modeid,
@allow_push_id,
@allow_pull_id,
@allow_anonymous_id,
case @conflogging -- centralized
when 1 then 1
when 2 then 1
else 0
end,
case @conflogging -- decentralized
when 1 then 0
when 2 then 1
else 1
end,
@statid,
@false,
@enabled_for_internet_id,
@full,
@dynamic_filters_id,
@snapshot_in_defaultfolder_bit,
@alt_snapshot_folder,
@pre_snapshot_script,
@post_snapshot_script,
@compress_snapshot_bit,
@ftp_address,
@ftp_port,
@ftp_subdirectory,
@ftp_login,
@enc_ftp_password,
@conflict_retention,
@keep_before_values_int,
@allow_subscription_copy_id,
@allow_synctoalternate_id,
@validate_subscriber_info,
@ad_guidname,
@pubcompat_level_id,
@max_concurrent_merge,
@max_concurrent_dynamic_snapshots,
@use_partition_groups_id,
@replicate_ddl,
@allow_subscriber_initiated_snapshot_id,
@distservername,
@allow_web_synchronization_id,
@web_synchronization_url,
@allow_partition_realignment_id,
@retention_period_unit_id,
@generation_leveling_threshold,
@automatic_reinitialization_policy)
if @@ERROR <> 0
begin
goto FAILURE
end

-- Add row to represent reciprocal subscription --
INSERT dbo.sysmergesubscriptions (subscriber_server, db_name, pubid, datasource_type,
subid, replnickname, replicastate, status, subscriber_type, subscription_type,
sync_type, description, priority, last_validated, replica_version)
VALUES (publishingservername(), @db_name, @pubid, 0,
@pubid, @replnick, newid(), @statid, @global, @push,
@automatic, @description, @priority, getdate(), 90)
if @@ERROR <> 0
begin
goto FAILURE
end
--
-- Add row for merge publication to dbo.MSmerge_replinfo.
--
insert dbo.MSmerge_replinfo(repid, login_name)
values (@pubid, suser_sname(suser_sid()))
if @@ERROR <> 0
begin
goto FAILURE
end


--
-- Add the publication to the distributor side
--
SELECT @distpubid = @replnick

declare @publisher_engine_edition int

select @publisher_engine_edition = sys.fn_MSrepl_editionid()

select @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
'.dbo.sp_MSadd_publication'
EXECUTE @retcode = @distproc
@publisher = @publishingservername,
@publisher_db = @db_name,
@publication = @publication,
-- @publication_id = NULL,
@publication_type = 2, -- 0 = Trans, 1 = Snapshot, 2 = Merge
@independent_agent = @true,
@immediate_sync = @true,
@allow_push = @allow_push_id,
@allow_pull = @allow_pull_id,
@allow_anonymous = @allow_anonymous_id,
@description = @description,
@retention = @retention,
@allow_subscription_copy = @allow_subscription_copy_id,
@retention_period_unit = @retention_period_unit_id,
@publisher_engine_edition = @publisher_engine_edition

IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO FAILURE
END

-- Populate the initial list.
exec @retcode = sys.sp_grant_publication_access
@publication = @publication,
@login = null,
@reserved = 'init'
IF @@error <> 0 OR @retcode <> 0
GOTO FAILURE


exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
commit tran

return (0)

FAILURE:
RAISERROR (14018, 16, -1)
-- UNDONE : This code is specific to 6.X nested transaction semantics --
if @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
ROLLBACK TRANSACTION sp_addmergepublication
COMMIT TRANSACTION
end
return (1)

No comments:

Post a Comment

Total Pageviews