May 7, 2012

sp_MSaddinitialpublication (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_MSaddinitialpublication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @description
, uniqueidentifier @pubid
, int @retention
, int @sync_mode
, int @allow_push
, int @allow_pull
, int @allow_anonymous
, int @conflict_logging
, int @status
, int @snapshot_ready
, int @enabled_for_internet
, int @publication_type
, int @conflict_retention
, int @allow_subscription_copy
, int @allow_synctoalternate
, int @backward_comp_level
, int @replicate_ddl
, tinyint @retention_period_unit
, binary @replnickname
, int @generation_leveling_threshold
, bit @automatic_reinitialization_policy)

MetaData:

   
create procedure sys.sp_MSaddinitialpublication(
@publisher sysname,
@publisher_db sysname,
@publication sysname, -- Name of the publication --
@description nvarchar(255), -- Description of the publication --
@pubid uniqueidentifier, -- Publication ID --
@retention int, -- Retention period of the publication --
@sync_mode int, -- Sync mode of the publication --
@allow_push int, -- does publication allow push ? --
@allow_pull int, -- does publication allow pull ? --
@allow_anonymous int, -- does publication allow anonymous ? --
@conflict_logging int, -- publication does centralized conflicts ? --
@status int, -- publication's status --
@snapshot_ready int, -- publication snapshto_ready flag ? --
@enabled_for_internet int, -- publication enabled_for_internet flag ? --
@publication_type int, -- a full publication or a partial one --
@conflict_retention int = 60, -- the retention period for conflict table --
@allow_subscription_copy int = 0, -- does publication allow subscription copies to sync ? --
@allow_synctoalternate int = 0, -- does publication allow subscription to sync to alternates ? --
@backward_comp_level int = 10, -- default to 7.0 server --
@replicate_ddl int = 0, -- by default no ALTER TABLE event to catch --
@retention_period_unit tinyint = 0,
@replnickname binary(6) = NULL,
@generation_leveling_threshold int = NULL,
@automatic_reinitialization_policy bit = 0 -- no upload first
) AS

SET NOCOUNT ON
declare @retcode int
declare @subid uniqueidentifier
declare @REPOLEVersion_90 int
declare @subscriber_db sysname

select @publication = RTRIM(@publication)
select @publisher_db = RTRIM(@publisher_db)
set @REPOLEVersion_90= 90
set @subscriber_db= db_name()

--
-- Check for subscribing permission
--
exec @retcode=sys.sp_MSreplcheck_subscribe
if @retcode<>0 or @@ERROR<>0 return (1)

-- The database cannot subscribe to both Yukon and pre-Yukon publications.
if @REPOLEVersion_90 = @backward_comp_level and
exists (select * from dbo.sysmergepublications where
backward_comp_level < @REPOLEVersion_90 and
(upper(publisher collate SQL_Latin1_General_CP1_CS_AS) <> upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) or
publisher_db <> @publisher_db or
name <> @publication))
begin
raiserror(21527, 16, -1, @publication, @subscriber_db)
return (1)
end
else if @REPOLEVersion_90 > @backward_comp_level and
exists (select * from dbo.sysmergepublications where
backward_comp_level >= @REPOLEVersion_90 and
status is not null and -- Pull subscribers have dummy backward_comp_level until they sync the first time. Skip those.
(upper(publisher collate SQL_Latin1_General_CP1_CS_AS) <> upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) or
publisher_db <> @publisher_db or
name <> @publication))
begin
raiserror(21528, 16, -1, @publication, @subscriber_db)
return (1)
end

--
-- Populate the local copy of dbo.sysmergepublications
--
BEGIN TRAN
save TRAN MSaddinitialpublication

-- this proc gets called when we are adding the publication on the subscriber when a merge for
-- this subscriber with the given publication happens for the first time. However, we could have
-- a publication entry already if we know about this publication as a "global replica". To understand
-- why a publication entry could already exists check sp_MScreateglobalreplica where we create a
-- sysmergepublications entry for each publication replica (pubid = subid) we see
-- however, this subscriber could be subscribing to this publication for the first time.
if exists (select * from dbo.sysmergepublications
where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db)
begin
declare @pubid_local uniqueidentifier

-- select the local pubid only if it has a valid parentid --
select @pubid_local = pubid from dbo.sysmergepublications
where name = @publication and
UPPER(publisher)=UPPER(@publisher) and
publisher_db=@publisher_db
if @pubid_local <> @pubid
begin
if exists (select * from dbo.sysmergesubscriptions
where pubid = @pubid and
UPPER(subscriber_server) collate database_default = UPPER(@publisher) and db_name = @publisher_db)
begin
select @subid = subid from dbo.sysmergesubscriptions
where pubid = @pubid and UPPER(subscriber_server) collate database_default = UPPER(@publisher) and db_name = @publisher_db
delete from dbo.MSmerge_replinfo where repid = @subid

-- delete supportability settings for the subscriptions that we are about to delete.
delete from dbo.MSmerge_supportability_settings where subid = @subid
delete from dbo.MSmerge_log_files where subid = @subid

delete from dbo.sysmergesubscriptions where subid = @subid
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
goto FAILURE
END

exec sys.sp_MScleanup_subscriber_history @subid=@subid
if @@ERROR<>0
goto FAILURE

-- If the only remaining subscriptions are old entries (before restore),
-- we remove them now.
if not exists (select * from dbo.sysmergesubscriptions
where status <> 7) -- REPLICA_STATUS_BeforeRestore
begin
delete from dbo.sysmergesubscriptions
truncate table dbo.MSmerge_supportability_settings
truncate table dbo.MSmerge_log_files
truncate table dbo.MSrepl_errors
truncate table dbo.MSmerge_history
truncate table dbo.MSmerge_articlehistory
truncate table dbo.MSmerge_sessions
delete from dbo.MSmerge_replinfo
end
end
end
update dbo.sysmergesubscriptions set pubid= @pubid, replicastate= newid() where pubid = @pubid_local
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
goto FAILURE
END
if @pubid <> @pubid_local
begin
-- delete supportability settings for the subscriptions that we are about to delete.
delete from dbo.MSmerge_supportability_settings where subid = @pubid
delete from dbo.MSmerge_log_files where subid = @pubid

delete from dbo.sysmergesubscriptions where subid = @pubid
exec sys.sp_MScleanup_subscriber_history @subid=@pubid
if @@ERROR<>0
goto FAILURE
delete from dbo.MSmerge_replinfo where repid = @pubid

-- If the only remaining subscriptions are old entries (before restore),
-- we remove them now.
if not exists (select * from dbo.sysmergesubscriptions
where status <> 7) -- REPLICA_STATUS_BeforeRestore
begin
delete from dbo.sysmergesubscriptions
truncate table dbo.MSmerge_supportability_settings
truncate table dbo.MSmerge_log_files
truncate table dbo.MSrepl_errors
truncate table dbo.MSmerge_history
truncate table dbo.MSmerge_articlehistory
truncate table dbo.MSmerge_sessions
delete from dbo.MSmerge_replinfo
end
end

update dbo.sysmergesubscriptions SET subid = @pubid, replnickname = isnull(@replnickname, replnickname) where subid = @pubid_local
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
goto FAILURE
END
delete from dbo.MSmerge_replinfo where repid = @pubid_local
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
goto FAILURE
END
update dbo.sysmergepublications
SET pubid = @pubid,
name = @publication,
description = @description,
designmasterid = @pubid,
retention = @retention,
retention_period_unit = @retention_period_unit,
parentid = pubid,
sync_mode = sync_mode,
allow_push = @allow_push,
allow_pull = @allow_pull,
allow_anonymous = @allow_anonymous,
centralized_conflicts= case @conflict_logging
when 1 then 1
when 2 then 1
else 0
end,
decentralized_conflicts= case @conflict_logging
when 1 then 0
when 2 then 1
else 1
end,
status = @status,
snapshot_ready = @snapshot_ready,
enabled_for_internet = @enabled_for_internet,
publication_type = @publication_type,
conflict_retention = @conflict_retention,
allow_subscription_copy = @allow_subscription_copy,
allow_synctoalternate = @allow_synctoalternate ,
backward_comp_level = @backward_comp_level,
replicate_ddl=@replicate_ddl,
generation_leveling_threshold = @generation_leveling_threshold,
automatic_reinitialization_policy = @automatic_reinitialization_policy
where name = @publication
and UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db

-- Fix the replnick to 4 bytes + 0x0000 if the compatlevel is less than 80.
-- This prevents us from having real 6 byte replnicks when there are 80 subscribers
-- Having real 6 byte replnicks in mixed mode can cause non-convergence because the
-- lineages can not be converted from 90 to 80 and back to 90 without losing last two bytes of replnick.
if @REPOLEVersion_90 > @backward_comp_level
begin
update dbo.sysmergesubscriptions set replnickname = substring(replnickname,1,4) + 0x0000
where pubid <> subid and pubid in (select pubid from sysmergepublications where name = @publication
and UPPER(publisher) = UPPER(@publisher) and publisher_db = @publisher_db)
end

end
else
begin
insert dbo.sysmergepublications(publisher, publisher_db,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,
conflict_retention, allow_subscription_copy, allow_synctoalternate, backward_comp_level, replicate_ddl, distributor,
retention_period_unit, generation_leveling_threshold, automatic_reinitialization_policy)
values(@publisher, @publisher_db, @pubid, @publication, @description, @pubid,
@retention, @pubid, @sync_mode, @allow_push, @allow_pull, @allow_anonymous,
case @conflict_logging -- centralized
when 1 then 1
when 2 then 1
else 0
end,
case @conflict_logging -- decentralized
when 1 then 0
when 2 then 1
else 1
end,
@status, @snapshot_ready, @enabled_for_internet, @publication_type,
@conflict_retention, @allow_subscription_copy, @allow_synctoalternate, @backward_comp_level, @replicate_ddl, @publisher,
@retention_period_unit, @generation_leveling_threshold, @automatic_reinitialization_policy)
end
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
goto FAILURE
END
COMMIT TRAN

RETURN (0)

FAILURE:
-- UNDONE : This code is specific to 6.X nested transaction semantics --
if @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION MSaddinitialpublication
COMMIT TRANSACTION
end
RETURN (1)

No comments:

Post a Comment

Total Pageviews