May 24, 2012

sp_MSquerysubtype (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_MSquerysubtype(uniqueidentifier @pubid
, nvarchar @subscriber
, nvarchar @subscriber_db)

MetaData:

   
create procedure sys.sp_MSquerysubtype (@pubid uniqueidentifier,@subscriber sysname, @subscriber_db sysname)
as
-- Security check
if (1 <> {fn ISPALUSER(@pubid)} and
1 <> is_member('db_owner'))
begin
RAISERROR (15247, 11, -1)
return (1)
end
declare @subtype int
declare @deleted int

set @deleted = 2

select @subtype = NULL
select @subtype = subscription_type from dbo.sysmergesubscriptions where pubid=@pubid
and db_name=@subscriber_db
and UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default
and status <> @deleted

if @subtype is NULL
select @subtype=2
select @subtype

sp_MSreinitoverlappingmergepublications (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_MSreinitoverlappingmergepublications(uniqueidentifier @pubid
, bit @upload_before_reinit)

MetaData:

 create procedure sys.sp_MSreinitoverlappingmergepublications  
@pubid uniqueidentifier,
@upload_before_reinit bit
as
declare @publisher sysname
declare @publisher_db sysname
declare @publication_current sysname
declare @pubid_current uniqueidentifier
declare @pubid_previous uniqueidentifier
declare @retcode int

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

set @publisher= publishingservername()
set @publisher_db= db_name()
set @retcode= 0

while 1=1
begin
set @pubid_previous= @pubid_current
set @pubid_current= null

select top 1 @publication_current= repub_smp.name,
@pubid_current= repub_smp.pubid
from dbo.sysmergearticles as pub_sma
inner join
dbo.sysmergearticles as repub_sma on pub_sma.artid=repub_sma.artid
inner join
dbo.sysmergepublications as repub_smp on repub_sma.pubid=repub_smp.pubid
where pub_sma.pubid=@pubid and
pub_sma.pubid<>repub_sma.pubid and
repub_smp.publisher collate database_default=@publisher collate database_default and
repub_smp.publisher_db=@publisher_db and
(repub_smp.pubid > @pubid_previous or @pubid_previous is null)
order by repub_smp.pubid asc

if @pubid_current is null
begin
break
end

exec @retcode= sys.sp_MSreinitmergepublication
@publication= @publication_current,
@upload_first= @upload_before_reinit

if @@error<>0 or @retcode<>0 return 1

end

return @retcode

sp_MSreleasemergeadminapplock (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_MSreleasemergeadminapplock(nvarchar @lockowner)

MetaData:

 create procedure sys.sp_MSreleasemergeadminapplock  
@lockowner nvarchar(32) = N'Session'
as
begin
set nocount on

declare @retcode smallint
declare @lock_resource nvarchar(255)
declare @publisher sysname
declare @publisher_db sysname
declare @DbPrincipal sysname

select @publisher = publishingservername()
select @publisher_db = DB_NAME(DB_ID())

--
-- Security Check.
--
exec @retcode = sys.sp_MSreplcheck_publish
if @retcode<>0 or @@error<>0
return 1

select @retcode = 0

-- use the dbowner role
select @DbPrincipal = N'db_owner'
select @lock_resource = N'MSinternal_repl_merge_admin_' + convert(nvarchar(6), db_id())

exec @retcode = sp_releaseapplock @Resource = @lock_resource, @LockOwner = @lockowner, @DbPrincipal = @DbPrincipal
if @@error<>0 or @retcode<0
return 1
else
return 0
end

sp_MSreenable_check (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_MSreenable_check(nvarchar @objname
, nvarchar @objowner)

MetaData:

 create procedure sys.sp_MSreenable_check @objname sysname, @objowner sysname = NULL  
as
set nocount on

declare @cnstname sysname
declare @cnstid int
declare @objid int
declare @enable_cmd nvarchar(4000)
declare @dest nvarchar(514)

declare @retcode int
IF @objname IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@objname', 'sp_MSreenable_check')
RETURN (1)
END

if(@objowner is not null)
select @dest = quotename(@objowner) + N'.' + quotename(@objname)
else
select @dest = quotename(@objname)

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

select @objid = object_id(@objname)

declare ms_crs_cnst cursor local static for
select name, object_id
from sys.objects
where parent_object_id = @objid
and OBJECTPROPERTY(object_id, 'CnstIsDisabled') = 1
and OBJECTPROPERTY(object_id, 'CnstIsNotRepl') = 0
and (OBJECTPROPERTY(object_id, 'IsCheckCnst') = 1
or OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
union
select name, object_id
from sys.triggers
where parent_id = @objid
and OBJECTPROPERTY(object_id, 'IsTrigger') = 1
and OBJECTPROPERTY(object_id, 'ExecIsTriggerNotForRepl') = 0
and OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled') = 1
for read only

open ms_crs_cnst
fetch ms_crs_cnst into @cnstname, @cnstid
while @@fetch_status >= 0
begin
if(ObjectProperty(@cnstid, 'IsTrigger') = 1)
select @enable_cmd = N'alter table ' + @dest + N' enable trigger ' + quotename(@cnstname)
else
select @enable_cmd = N'alter table ' + @dest + N' check constraint ' + quotename(@cnstname)

execute(@enable_cmd)
fetch ms_crs_cnst into @cnstname, @cnstid
end -- of major loop
deallocate ms_crs_cnst
return 0

sp_MSreleasemakegenerationapplock (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_MSreleasemakegenerationapplock()

MetaData:

   
create procedure sys.sp_MSreleasemakegenerationapplock
as
set nocount on

declare @retcode smallint
declare @lock_resource nvarchar(255)
declare @DbPrincipal sysname

-- Security check
exec @retcode = sys.sp_MSrepl_PAL_rolecheck
if (@retcode <> 0) or (@@error <> 0)
return 1

select @retcode = 0
select @lock_resource = N'MSinternal_makegeneration_inprogress' +
convert(nvarchar(11), db_id())

if exists (select * from sys.database_principals where name=N'MSmerge_PAL_role' and type = 'R')
select @DbPrincipal = N'MSmerge_PAL_role'
else
select @DbPrincipal = N'db_owner'

exec @retcode = sp_releaseapplock @Resource = @lock_resource, @LockOwner = N'Session', @DbPrincipal = @DbPrincipal
if @@error <> 0 or @retcode < 0
return (1)
else
return (0)

sp_MSreleasedynamicsnapshotapplock (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_MSreleasedynamicsnapshotapplock(nvarchar @publication
, int @partition_id)

MetaData:

 create procedure sys.sp_MSreleasedynamicsnapshotapplock(  
@publication sysname,
@partition_id int)
as
begin
set nocount on

declare @retcode smallint
declare @lock_resource nvarchar(255)
declare @pubnumber smallint
declare @publisher sysname
declare @publisher_db sysname
declare @pubid uniqueidentifier
declare @PALRole sysname

select @publisher = publishingservername()
select @publisher_db = DB_NAME(DB_ID())

--
-- Security Check.
--
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @publication = @publication
if @retcode<>0 or @@error<>0
return 1

-- partition id can be >= 0
if @partition_id = -1
return 0

select @retcode = 0
select @pubnumber = publication_number, @pubid = pubid from dbo.sysmergepublications
where name=@publication and upper(publisher)=upper(@publisher) and publisher_db=@publisher_db
if @pubnumber is NULL or @pubnumber = 0
begin
RAISERROR (20026, 16, -1, @publication)
return 1
end

select @lock_resource = N'MSinternal_dynamic_snapshot_' +
convert(nvarchar(6), db_id()) + '_' +
convert(nvarchar(6), @pubnumber) + '_' +
convert(nvarchar(11), @partition_id)

-- Since we have gone through the PAL check already, the PAL role
-- should have been set. The following call to fn_MSmerge_GetPALRole
-- is nothing more than a way to retrieve the PAL role name
select @PALRole = NULL
select @PALRole = sys.fn_MSmerge_GetPALRole(@pubid)
if @PALRole is NULL
return 1

exec @retcode = sp_releaseapplock @Resource = @lock_resource, @LockOwner = N'Session', @DbPrincipal = @PALRole
if @@error <> 0 or @retcode < 0
return 1
else
return 0
end

sp_MSreinit_subscription (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_MSreinit_subscription(nvarchar @publisher_name
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber_name
, nvarchar @subscriber_db)

MetaData:

 CREATE PROCEDURE sys.sp_MSreinit_subscription  
(
@publisher_name sysname,
@publisher_db sysname,
@publication sysname = 'all',
@subscriber_name sysname = 'all',
@subscriber_db sysname = 'all'
)
as
begin
set nocount on
declare @proc nvarchar(2048)
declare @retcode int

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end

if @publisher_name IS NULL or NOT EXISTS (select * from master.dbo.sysservers as ss, dbo.MSpublications as msp
where lower(ss.srvname) = lower(@publisher_name) and msp.publisher_id = ss.srvid)
begin
return (1)
end

if @publisher_db IS NULL
select @publisher_db = ''

select @proc = quotename(ltrim(rtrim(@publisher_name))) + '.' + quotename(@publisher_db) + '.dbo.sp_reinitsubscription '
exec @retcode = @proc @publication, 'all', @subscriber_name, @subscriber_db

return (@retcode)
end

sp_MSreinit_hub (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_MSreinit_hub(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, bit @upload_first)

MetaData:

 create procedure sys.sp_MSreinit_hub  
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@upload_first bit
AS
-- Lightweight subscribers never are republishers.
if 1 = sys.fn_MSuselightweightreplication
(@publisher, @publisher_db, @publication, null, null, null, null)
begin
return 0
end

declare @retcode int
declare @pubid uniqueidentifier
declare @hub_pubname sysname
declare @hub_publisher sysname
declare @hub_publisher_db sysname
declare @hub_pubid uniqueidentifier
declare @schematext nvarchar(4000)
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @compatlevel int

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

select @pubid=pubid, @compatlevel = backward_comp_level from dbo.sysmergepublications
where name=@publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
BEGIN TRAN
SAVE TRAN reinithub

-- Find all publications that are being republished at the subscriber --
declare reinit_hub CURSOR LOCAL FAST_FORWARD FOR select DISTINCT p.pubid, p.name, p.publisher, p.publisher_db FROM dbo.sysmergepublications p
where UPPER(p.publisher)=UPPER(publishingservername()) and p.publisher_db=db_name()
and exists (select * from dbo.sysmergearticles where (objid in
(select objid from dbo.sysmergearticles where pubid=@pubid)) and (objid in
(select objid from dbo.sysmergearticles where pubid=p.pubid))) and p.pubid<>@pubid
FOR READ ONLY
open reinit_hub
fetch reinit_hub into @hub_pubid, @hub_pubname, @hub_publisher, @hub_publisher_db
while (@@fetch_status<>-1)
begin
if @upload_first=1
begin
update dbo.sysmergesubscriptions set status=5 where pubid=@hub_pubid
if @@ERROR<>0
goto FAILURE
end
else
begin
-- if they have previously requested reinit with upload first=true, status
-- has been changed to 5. We have lost the info about the status before the
-- previous reinit. Use last_sync_status to determine whether the new status
-- should be 0 or 1.
update dbo.sysmergesubscriptions set status =
case when last_sync_status is null then 0 else 1 end
where pubid=@hub_pubid
and status = 5

if @@ERROR<>0
goto FAILURE
end
select @schematext = 'exec dbo.sp_MSreinit_hub '+ QUOTENAME(@hub_publisher) + ', ' + QUOTENAME(@hub_publisher_db) + ', ' + QUOTENAME(@hub_pubname) + ', ' + convert(nvarchar, @upload_first)
select @schemaversion = schemaversion from dbo.sysmergeschemachange
if (@schemaversion is NULL)
set @schemaversion = 1
else
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
set @schemaguid = newid()
if @upload_first=0
set @schematype = 12 -- reinit_all --
else
set @schematype = 14 -- reinitwithupload --
exec @retcode=sys.sp_MSinsertschemachange @hub_pubid, null, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0
begin
select @retcode = 1
GOTO FAILURE
end
if @compatlevel < 40
begin
raiserror(21354, 10, -1, @hub_pubname)
exec @retcode=sys.sp_MSBumpupCompLevel @hub_pubid, 40
if @@ERROR<>0 or @retcode<>0
GOTO FAILURE
end

update dbo.sysmergepublications set snapshot_ready=2 where pubid=@hub_pubid
if @@ERROR<>0
goto FAILURE
fetch next from reinit_hub into @hub_pubid, @hub_pubname, @hub_publisher, @hub_publisher_db
end
close reinit_hub
deallocate reinit_hub
COMMIT TRAN
return (0)
FAILURE:
close reinit_hub
deallocate reinit_hub
raiserror('Error occurred when applying reinit-all command at subscribers', 16, -1)
if @@TRANCOUNT >0
begin
ROLLBACK TRANSACTION reinithub
COMMIT TRAN
end
return (0)

sp_MSreinit_failed_subscriptions (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_MSreinit_failed_subscriptions(int @failure_level)

MetaData:

 --  This stored procedure is used as a response to the Replication Validation Failure Alert.  
-- It will reinit the failed subscription. If the publisher is remote, it must be configured as a remote server
-- for this procedure to work.
create procedure sys.sp_MSreinit_failed_subscriptions
@failure_level int = 0 -- 0 All failure 1 Validation failures
as

declare @publisher sysname
declare @publisher_db sysname
declare @publication sysname
declare @article sysname
declare @publication_type int
declare @subscriber sysname
declare @subscriber_db sysname
declare @agent_type int
declare @alert_id int
declare @proc nvarchar(100)
declare @message nvarchar(4000)
declare @retcode int
declare @found bit
declare @return_value int
,@current_principal sysname

set nocount on

set @found = 0 -- set if cursor returns a row
set @return_value = 0 -- set to success

-- Security Check: require sysadmin
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(21089,16,-1)
return (1)
end

-- For each publication validation failure, resync the subscription
declare hc CURSOR LOCAL FAST_FORWARD for select publisher, publisher_db, publication, publication_type, article, subscriber,
subscriber_db, alert_id from
msdb.dbo.sysreplicationalerts where
(@failure_level = 0 or (@failure_level = 1 and alert_error_code = 20574)) and -- 20574 = validation failure
status = 0
for read only

open hc
fetch hc into @publisher, @publisher_db, @publication, @publication_type, @article, @subscriber, @subscriber_db, @alert_id
while (@@fetch_status <> -1)
begin

set @found = 1

BEGIN TRY
set @proc = QUOTENAME(@publisher) + '..sys.sp_MSGetCurrentPrincipal'
exec @retcode = @proc
@db_name = @publisher_db
,@current_principal = @current_principal output
END TRY
BEGIN CATCH
select @current_principal = @publisher
END CATCH

select @publisher = isnull(@current_principal, @publisher)

-- Reinit snapshot or transactional subscription (article level)
if @publication_type = 0 or @publication_type = 1
begin
set @proc = QUOTENAME(@publisher) + '.' + QUOTENAME(@publisher_db) + '.dbo.sp_reinitsubscription'
exec @retcode = @proc
@publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @subscriber_db
-- Ignore failures, update status bit if successful
if @retcode = 0
begin
-- Change status to 1 which means the alerts has been serviced
update msdb.dbo.sysreplicationalerts set status = 1 where alert_id = @alert_id

-- Raiserror that subscription was reinitialized
if @failure_level = 0
-- 'Subscriber ''%s'' subscription to article ''%s'' in publication ''%s'' has been reinitialized after a synchronization failure.'
raiserror(20576, 10,-1, @subscriber, @article, @publication)
else if @failure_level = 1
-- 'Subscriber ''%s'' subscription to article ''%s'' in publication ''%s'' has been reinitialized after a validation failure.'
raiserror(20572, 10,-1, @subscriber, @article, @publication)
end
else -- failure
set @return_value = 1
end
-- Reinit merge subscription (full publication)
else if @publication_type = 2
begin
set @proc = QUOTENAME(@publisher) + '.' + QUOTENAME(@publisher_db) + '.dbo.sp_reinitmergesubscription'
exec @retcode = @proc
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriber_db
-- Ignore failures, update status bit if successful
if @retcode = 0
begin
-- Change status to 1 which means the alerts has been serviced
update msdb.dbo.sysreplicationalerts set status = 1 where alert_id = @alert_id

-- Raiserror that subscription was reinitialized
if @failure_level = 0
-- 'Subscriber ''%s'' subscription to to article ''%s'' in publication ''%s'' has been reinitialized after a synchronization failure.'
raiserror(20576, 10,-1, @subscriber, @article, @publication)
else if @failure_level = 1
-- 'Subscriber ''%s'' subscription to to article ''%s'' in publication ''%s'' has been reinitialized after a validation failure.'
raiserror(20572, 10,-1, @subscriber, @article, @publication)
end
else -- failure
set @return_value = 1
end

fetch hc into @publisher, @publisher_db, @publication, @publication_type, @article, @subscriber, @subscriber_db, @alert_id
end

close hc
deallocate hc

-- Return a message stating no entries where found
if @found = 0
begin
-- 'No entries were found in msdb.dbo.sysreplicationalerts.'
raiserror(20577, 10,-1)

-- There is most likely a problem, set failure return value
set @return_value = 1
end

return @return_value

sp_MSregistersubscription (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_MSregistersubscription(int @replication_type
, nvarchar @publisher
, nvarchar @publisher_db
, int @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, nvarchar @distributor
, int @distributor_security_mode
, nvarchar @distributor_login
, nvarchar @distributor_password
, uniqueidentifier @subscription_id
, int @independent_agent
, int @subscription_type
, int @use_interactive_resolver
, int @failover_mode
, bit @use_web_sync
, nvarchar @hostname)

MetaData:

   
--
-- Name: sp_MSregistersubscription
--
-- Descriptions:
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MSregistersubscription (
@replication_type int, -- Transactional = 1, Merge = 2 --
@publisher sysname,
@publisher_db sysname,
@publisher_security_mode int = NULL, -- 0 standard; 1 integrated --
@publisher_login sysname = NULL,
@publisher_password nvarchar(524) = NULL,
@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@subscriber_security_mode int = NULL, -- 0 standard; 1 integrated --
@subscriber_login sysname = NULL,
@subscriber_password nvarchar(524) = NULL,
@distributor sysname,
@distributor_security_mode int = NULL,
@distributor_login sysname = NULL,
@distributor_password nvarchar(524) = NULL,
@subscription_id uniqueidentifier ,
@independent_agent int = NULL,
@subscription_type int,
@use_interactive_resolver int = NULL,
@failover_mode int = NULL,
@use_web_sync bit = 0,
@hostname sysname = NULL
) AS

SET NOCOUNT ON

-- MobileSync Support --
declare @regkey nvarchar(1000)
declare @subidstr nvarchar(38)
declare @profile_name nvarchar(100)
declare @retcode int

select @retcode = 0
-- Security check: Sysadmin and DBO only
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
begin
return 1
end

set @regkey = sys.fn_replgetsubscriptionregkey(@publisher, @publisher_db, @publication, @subscriber, @subscriber_db)
set @subidstr = '{' + convert ( nchar(36), @subscription_id) + '}'
set @profile_name = N'Windows Synchronization Manager profile' -- SyncMgr Profile

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'ProfileName',
'REG_SZ',
@profile_name
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'ReplicationType',
'REG_DWORD',
@replication_type
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'SubscriptionType',
'REG_DWORD',
@subscription_type
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'Subid',
'REG_SZ',
@subidstr
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'Publisher',
'REG_SZ',
@publisher
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'PublisherDb',
'REG_SZ',
@publisher_db
if @retcode <> 0 OR @@ERROR <> 0
return 1


IF @use_interactive_resolver IS NOT NULL
BEGIN
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'UseInteractiveResolver',
'REG_DWORD',
@use_interactive_resolver
if @retcode <> 0 OR @@ERROR <> 0
return 1
END

-- If Publisher security mode is NOT NULL, write out the entries --
if @publisher_security_mode IS NOT NULL
begin
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'PublisherSecurityMode',
'REG_DWORD',
@publisher_security_mode
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'PublisherLogin',
'REG_SZ',
@publisher_login
if @retcode <> 0 OR @@ERROR <> 0
return 1

end
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'Publication',
'REG_SZ',
@publication
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'Subscriber',
'REG_SZ',
@subscriber
if @retcode <> 0 OR @@ERROR <> 0
return 1

-- If Subscriber security mode is NOT NULL, write out the entries --
if @subscriber_security_mode IS NOT NULL
begin
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'SubscriberSecurityMode',
'REG_DWORD',
@subscriber_security_mode
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'SubscriberLogin',
'REG_SZ',
@subscriber_login
if @retcode <> 0 OR @@ERROR <> 0
return 1
end

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'SubscriberDb',
'REG_SZ',
@subscriber_db
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'Distributor',
'REG_SZ',
@distributor
if @retcode <> 0 OR @@ERROR <> 0
return 1

-- If Distributor security mode is NOT NULL, write out the entries --
if @distributor_security_mode IS NOT NULL
begin
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'DistributorSecurityMode',
'REG_DWORD',
@distributor_security_mode
if @retcode <> 0 OR @@ERROR <> 0
return 1

EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'DistributorLogin',
'REG_SZ',
@distributor_login
if @retcode <> 0 OR @@ERROR <> 0
return 1
end

if @independent_agent IS NOT NULL
begin
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'IndependentAgent',
'REG_DWORD',
@independent_agent
if @retcode <> 0 OR @@ERROR <> 0
return 1
end

IF @failover_mode IS NOT NULL
begin
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'FailoverMode',
'REG_DWORD',
@failover_mode
if @retcode <> 0 OR @@ERROR <> 0
return 1

end

IF @use_web_sync IS NOT NULL
begin
declare @use_web_sync_int int
select @use_web_sync_int = convert(int, @use_web_sync)
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'WebSync',
'REG_DWORD',
@use_web_sync_int
if @retcode <> 0 OR @@ERROR <> 0
return 1
end

if @hostname is not null
begin
EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
@regkey,
'HostName',
'REG_SZ',
@hostname
if @retcode <> 0 OR @@ERROR <> 0
return 1
end

-- Mark enabled_for_syncmgr bit if every thing succeeded.
-- If the row exists in MSreplication_properties table,
-- set enabled_for_syncmgr bit
-- The logic need to be here because UI call this sp directly.
if object_id('MSsubscription_properties') is not NULL
begin
update MSsubscription_properties set enabled_for_syncmgr = 1 where
UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
if @retcode <> 0 OR @@ERROR <> 0
return 1
end

return 0

sp_MSregistermergesnappubid (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_MSregistermergesnappubid(nvarchar @snapshot_session_token
, uniqueidentifier @pubid)

MetaData:

 --   
-- Name: sp_MSregistermergesnappubid
--
-- Description: This procedure is used by the merge agent to register the
-- pubid of the publication for which the current snapshot is
-- being delivered in the snapshot delivery progress table. By
-- registering the pubid and the snapshot session token in the
-- snapshot delivery progress table, the merge agent will be
-- able to detect the case where a different snapshot is
-- being delivered over a previously interrupted snapshot.
-- If a different snapshot is being delivered over an
-- interrupted snapshot, this procedure will perform the
-- necessary cleanup in the merge meta-data tables to ensure that
-- the new snapshot can be delivered successfully.
--
-- Parameters: @snapshot_session_token nvarchar(260) (mandatory)
-- @pubid uniqueidentifier (mandatory)
--
-- Note: This procedure should only be called by the merge agent at the
-- subscriber database.
--
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: This is a public interface object, security check is performed
-- inside this procedure to restrict access to sysadmins and
-- db_owners of the subscriber database.
--
create procedure sys.sp_MSregistermergesnappubid (
@snapshot_session_token nvarchar(260),
@pubid uniqueidentifier
)
as
begin
set nocount on
declare @retcode int
declare @pubidprefix nvarchar(100)
declare @transaction_opened bit
declare @snapshot_progress_token nvarchar(500)
declare @snapshot_progress_token_hash nvarchar(500)
declare @previous_snapshot_session_token nvarchar(260)

select @retcode = 0
select @pubidprefix = N'<MergePubId>:'
select @transaction_opened = 0
select @snapshot_progress_token = @pubidprefix + convert(nvarchar(100), @pubid)
select @snapshot_progress_token_hash = sys.fn_repl32bitstringhash(@snapshot_progress_token)
select @previous_snapshot_session_token = null

-- Security check
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

begin transaction
save transaction sp_MSregistermergesnappubid
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
select @transaction_opened = 1

-- Try to pick up the session token of a previously interrupted snapshot
-- delivery session for this publication
if object_id('dbo.MSsnapshotdeliveryprogress', 'U') is not null
begin

select @previous_snapshot_session_token = session_token
from dbo.MSsnapshotdeliveryprogress
where progress_token_hash = @snapshot_progress_token_hash
and progress_token = @snapshot_progress_token
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

-- The current snapshot is different from the one interrupted before,
-- need to do cleanup of the interrupted snapshot
if @previous_snapshot_session_token is not null and
@previous_snapshot_session_token <> @snapshot_session_token
begin
exec @retcode = sys.sp_MSpurgepartialmergesnapshot
@pubid = @pubid
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
@snapshot_session_token = @snapshot_session_token,
@snapshot_progress_token = @snapshot_progress_token
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
end
else if @previous_snapshot_session_token is null
begin
exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
@snapshot_session_token = @snapshot_session_token,
@snapshot_progress_token = @snapshot_progress_token
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
end
end
else
begin
exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
@snapshot_session_token = @snapshot_session_token,
@snapshot_progress_token = @snapshot_progress_token
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
end

commit transaction
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
select @transaction_opened = 0

Failure:
if @transaction_opened = 1
begin
rollback transaction sp_MSregistermergesnappubid
commit transaction
end
return @retcode
end

sp_MSrefresh_publisher_idrange (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_MSrefresh_publisher_idrange(nvarchar @qualified_object_name
, uniqueidentifier @subid
, uniqueidentifier @artid
, tinyint @ranges_needed
, bit @refresh_check_constraint)

MetaData:

 --  this proc will refresh the local publisher's range  
create procedure sys.sp_MSrefresh_publisher_idrange
@qualified_object_name nvarchar(517),
@subid uniqueidentifier, -- pubid of the publisher whose range is to be refreshed.
@artid uniqueidentifier, -- though the artid can be derived from the @qualified_object_name, we will take it as a parameter to do one less query
@ranges_needed tinyint, -- 0=none needed, 1=one range needed, 2=both ranges needed
@refresh_check_constraint bit
as
declare @range_begin numeric(38,0)
declare @range_end numeric(38,0)
declare @next_range_begin numeric(38,0)
declare @next_range_end numeric(38,0)
declare @retcode int

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

if @ranges_needed=0
return 0

if @artid is NULL
begin
select @artid=artid from dbo.sysmergearticles where objid=object_id(@qualified_object_name)
if @artid is NULL
begin
RAISERROR (20027, 16, -1, @qualified_object_name)
RETURN (1)
end
end

select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end
from dbo.MSmerge_identity_range where artid=@artid and subid=@subid and is_pub_range=0

exec @retcode = sys.sp_MSget_new_idrange
@qualified_object_name,
@artid,
@range_begin output,
@range_end output,
@next_range_begin output,
@next_range_end output,
2, -- subscriber range. This is the publisher's local range.
@ranges_needed
if @@error<>0 or @retcode<>0
begin
raiserror(21197, 16, -1)
return 1
end

if @refresh_check_constraint=1 and @qualified_object_name is not NULL
begin
exec @retcode = sys.sp_MSrefresh_idrange_check_constraint @qualified_object_name, @artid, @range_begin, @range_end, @next_range_begin, @next_range_end, @ranges_needed
if @@error<>0 or @retcode<>0
begin
raiserror(21197, 16, -1)
return 1
end
end

update dbo.MSmerge_identity_range
set range_begin = @range_begin,
range_end = @range_end,
next_range_begin = @next_range_begin,
next_range_end = @next_range_end
where subid=@subid and artid=@artid and is_pub_range=0
if @@ERROR<>0
begin
raiserror(21197, 16, -1)
return 1
end

-- also update on the distributor the identity range that was just allocated to the publisher
-- we will do this only for the root publisher. When a republisher gets restored from backup
-- it should merge with its root publisher to get the information on what ranges were allocated.
exec @retcode = sys.sp_MSmerge_log_idrange_alloc_on_distributor
@subid,
@artid,
0, -- is_pub_range 0 because in Yukon publisher's local allocation is the size of the sub range.
@ranges_needed,
@range_begin,
@range_end,
@next_range_begin,
@next_range_end

return 0

Total Pageviews