April 17, 2012

sp_configure_peerconflictdetection (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_configure_peerconflictdetection(nvarchar @publication
, nvarchar @action
, int @originator_id
, int @conflict_retention
, nvarchar @continue_onconflict
, nvarchar @local
, int @timeout)

MetaData:

 create procedure sys.sp_configure_peerconflictdetection  
(
@publication sysname,
@action nvarchar(32), -- values:enable, disable, continue_enable, continue_disable
@originator_id int = NULL, -- valid only when @local is true
@conflict_retention int = 14, -- valid only when @action is enable or continue_enable
@continue_onconflict nvarchar(5) = N'false', -- valid only when @action is enable or continue_enable
@local nvarchar(5) = N'true',
@timeout int = 60 -- seconds, valid only when @local is false
)
as
begin
set NOCOUNT on

declare @retcode int
,@db_name sysname
,@pubid int
,@options int
,@OPT_ENABLED_FOR_P2P int = 0x1
,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int = 0x8
,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT int = 0x10
,@enabling bit
,@peercmdtxt nvarchar(max)
,@artid int
,@current_db_version int
,@strval nvarchar(32)

select @db_name=db_name()

-- Security Check

exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return(1)

-- Check to see if the database has been activated for publication.

if sys.fn_MSrepl_istranpublished(@db_name, 1) <> 1
begin
raiserror (14013, 16, -1)
return (1)
end

-- Parameter Check: @publication.
-- The @publication name cannot be NULL and must conform to the rules
-- for identifiers.

if @publication is NULL
begin
raiserror (14043, 16, -1, N'@publication', N'sp_configure_peerconflictdetection')
return (1)
end

execute @retcode = sys.sp_validname @publication
if @retcode <> 0
return (1)


select @pubid = pubid, @options = options
from syspublications
where name = @publication

if @pubid is NULL
begin
raiserror (20026, 11, -1, @publication)
return (1)
end

if (@options & @OPT_ENABLED_FOR_P2P) <> @OPT_ENABLED_FOR_P2P
begin
raiserror (22808, 16, -1, N'sp_configure_peerconflictdetection', @publication)
return (1)
end

-- parameter check: @local
if (LOWER(@local) not in (N'true', N'false'))
begin
raiserror (14148, 16, -1, N'@local')
return (1)
end
select @local = LOWER(@local)


if @local = N'true'
begin
-- parameter check: @action
if @action is NULL or (LOWER(@action) not in (N'enable', N'disable'))
begin
raiserror (22810, 16, -1)
return (1)
end
select @action = LOWER(@action)

if @action = N'enable'
begin

if (@options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION
begin
raiserror(22828, 16, -1, @publication, N'enabled')
return (1) -- already enabled
end

-- parameter check: @conflict_retention
if (@conflict_retention < 0)
begin
raiserror(20050, 16, -1, 0)
return (1)
end

-- parameter check: @continue_onconflict
if LOWER(@continue_onconflict) not in (N'true', N'false')
begin
raiserror (14148, 16, -1, N'@continue_onconflict')
return (1)
end

-- parameter check: @originator_id
if @originator_id is not NULL and (@originator_id = 0 or @originator_id = 0x80000000)
begin
-- Originator ID provided is invalid: 0 or 0x80000000.
select @strval = cast(@originator_id as nvarchar)
raiserror(22806, 16, -1, @strval)
return (1)
end

begin tran
save tran tr_sp_configure_p2pcd

declare @numids int
select @numids = count(*) from dbo.MSpeer_originatorid_history with (holdlock, tablock, xlock) -- X lock the whole table
if @@error <> 0
goto UNDO

-- check/generate peer ID
if @originator_id is null
begin
exec sp_MScheckgenerate_originatorid @publication = @publication, @old_originator_id = 0, @new_originator_id = @originator_id output
if @@error <> 0
goto UNDO
end
else
begin
declare @tmpid int
exec sp_MScheckgenerate_originatorid @publication = @publication, @old_originator_id = @originator_id, @new_originator_id = @tmpid output
if @@error <> 0
goto UNDO

if @tmpid = 0
begin
-- Originator ID provided is invalid: xxx.
select @strval = cast(@originator_id as nvarchar)
raiserror(22806, 16, -1, @strval)
goto UNDO
end
end

if LOWER(@continue_onconflict) = N'false'
select @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT = 0x0

-- update options and originator_id
update syspublications
set options = options | @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION | @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT,
originator_id = @originator_id,
conflict_retention = @conflict_retention
where pubid = @pubid

select @enabling = 1

end -- @action: enable
else
begin
if (@options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) = 0
begin
raiserror(22828, 16, -1, @publication, N'disabled')
return (1) -- already disabled
end

begin tran
save tran tr_sp_configure_p2pcd

-- update options and originator_id
update syspublications
set options = options & ~(@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) & ~(@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT),
originator_id = NULL,
conflict_retention = NULL
where pubid = @pubid

select @originator_id = 0

select @enabling = 0

delete from dbo.MSpeer_originatorid_history
where originator_publication = @publication

end -- @action: disable

-- ensure there are articles in the publication
select @artid = MIN(artid)
from sysarticles
where pubid = @pubid

if (@artid is not NULL)
begin
-- generate the script of processing hidden column, table persistent property and conflict table for all articles
-- for this peer, processing (add/remove, create/drop, set/reset) hidden column, table persistent property and conflict table for all articles
exec @retcode = sp_MSscriptpeerconflictdetection_tableaug @pubid = @pubid, @publication = @publication,
@enabling = @enabling, @originator_id = @originator_id, @cmdtxt = @peercmdtxt output, @execscript = 1
if @@error <> 0 or @retcode <> 0
begin
raiserror(21542, 16, 1, @@error, 'sp_configure_peerconflictdetection')
goto UNDO
end

if @peercmdtxt is not NULL
begin
exec @retcode = sys.sp_replflush
if @@error <> 0 or @retcode <> 0
begin
goto UNDO
end

-- post to log, which will be applied to the immediate subscribers
exec @retcode = sys.sp_replpostcmd 0-- not partial -- , @pubid, @artid, 1-- @sql_cmd_type = SQL_CMD -- , @peercmdtxt
if @retcode <> 0 or @@error <> 0
goto UNDO

-- generate the script of custom procs for all articles
select @peercmdtxt = N''
exec @retcode = sp_MSscriptpeerconflictdetection_customprocs @publication = @publication, @cmdtxt = @peercmdtxt output
if @@error <> 0 or @retcode <> 0
goto UNDO

-- post to log, which will be applied to the immediate subscribers, SQL_NOSYNCSETUPSCRIPT allows comments and "go" in the script
exec @retcode = sys.sp_replpostcmd 0-- not partial -- , @pubid, @artid, 104-- @sql_cmd_type = SQL_NOSYNCSETUPSCRIPT -- , @peercmdtxt
if @retcode <> 0 or @@error <> 0
goto UNDO
end


end -- @artid is not NULL


-- by sp_MSpeertopeerfwdingexec: executes and posts sp_MSchange_originatorid, which inserts the new ID
-- into MSpeer_lsns and MSpeer_originatorid_history
exec @retcode = sys.sp_MSgetdbversion @current_version = @current_db_version output
if @@error <> 0 or @retcode <> 0
goto UNDO

-- execute and post 'sp_MSchange_originatorid' by sp_MSpeertopeerfwdingexec
select @peercmdtxt = N'if (@@microsoftversion >= 0x0A000000) ' +
N'begin' +
N' exec sys.sp_MSchange_originatorid @originator_node = N' + quotename(publishingservername(), N'''') +
N',@originator_db = N' + quotename(DB_NAME(), N'''') +
N',@originator_publication= N' + quotename(@publication, N'''') +
N',@originator_publication_id = ' + cast(@pubid as nvarchar) +
N',@originator_db_version = ' + cast(@current_db_version as nvarchar) +
N',@originator_id = ' + cast(@originator_id as nvarchar) +
N',@originator_version = ' + cast(@@microsoftversion as nvarchar) +
N' end'

-- execute locally first then posted to log
exec @retcode = sys.sp_MSpeertopeerfwdingexec @command = @peercmdtxt, @publication = @publication
if @@error <> 0 or @retcode <> 0
goto UNDO

commit tran
return (0)

end -- @local is true
else -- @local is false
begin
-- parameter check: @action
if @action is NULL or (LOWER(@action) not in (N'enable', N'disable', N'continue_enable', N'continue_disable'))
begin
raiserror (22810, 16, -1)
return (1)
end
select @action = LOWER(@action)

if @action = N'enable' or @action = N'continue_enable'
begin
-- parameter check: @conflict_retention
if (@conflict_retention < 0)
begin
raiserror(20050, 16, -1, 0)
return (1)
end

-- parameter check: @continue_onconflict
if LOWER(@continue_onconflict) not in (N'true', N'false')
begin
raiserror (14148, 16, -1, N'@continue_onconflict')
return (1)
end
end

-- parameter check: @timeout
if @timeout <= 0
begin
raiserror (22811, 16, -1)
return (1)
end

return (0)
end -- @local is false


UNDO:
rollback tran tr_sp_configure_p2pcd
commit tran
return (1)

end

No comments:

Post a Comment

Total Pageviews