May 29, 2012

sp_MSsetsubscriberinfo (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_MSsetsubscriberinfo(uniqueidentifier @pubid
, nvarchar @expr)

MetaData:

 create procedure sys.sp_MSsetsubscriberinfo   
(@pubid uniqueidentifier, @expr nvarchar(500))
as
-- Security Checking
-- PAL users have access
if ({fn ISPALUSER(@pubid)} <> 1)
begin
if (@pubid is NULL)
begin
RAISERROR (21723, 16, -1, 'sp_MSsetsubscriberinfo')
return 1
end
else
begin
RAISERROR (14126, 11, -1)
return 1
end
end

update dbo.sysmergepublications set validate_subscriber_info = @expr where pubid = @pubid

sp_MSSetServerProperties (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_MSSetServerProperties(int @auto_start)

MetaData:

   
create proc sys.sp_MSSetServerProperties
@auto_start INT = NULL -- 1 or 0, while 1 = auto start, 0 = manual start
as
set nocount on

-- only sysadmins are allowed to execute this stored procedure
if( is_srvrolemember(N'sysadmin') = 0 )
begin
RAISERROR (15003, -1, -1, N'sysadmin')
return 1
end

-- Make sure values (if supplied) are good
IF (@auto_start IS NOT NULL)
BEGIN
-- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
SELECT @auto_start = CASE @auto_start
WHEN 0 THEN 3
WHEN 1 THEN 2
ELSE 3 -- Assume non auto-start if passed a junk value
END
END

-- Write out the values
IF (@auto_start IS NOT NULL)
BEGIN
IF ((PLATFORM() & 0x1) = 0x1) -- NT
EXECUTE sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
N'Start',
N'REG_DWORD',
@auto_start
ELSE
RAISERROR(14546, 16, 1, '@auto_start')
END

sp_MSsetrowmetadata (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_MSsetrowmetadata(int @tablenick
, uniqueidentifier @rowguid
, bigint @generation
, varbinary @lineage
, varbinary @colv
, tinyint @type
, int @compatlevel
, bit @isinsert
, uniqueidentifier @pubid
, smallint @publication_number
, int @partition_id
, tinyint @partition_options)

MetaData:

 create procedure sys.sp_MSsetrowmetadata  
(@tablenick int,
@rowguid uniqueidentifier,
@generation bigint,
@lineage varbinary(311),
@colv varbinary(2953),
@type tinyint,
@was_tombstone int = NULL OUTPUT,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@isinsert bit = 0, -- 1 = is an insert, 0 = is an update or delete
@pubid uniqueidentifier = NULL,
@publication_number smallint = NULL,
@partition_id int = NULL,
@partition_options tinyint = 0
)
as
declare @retcode int, @partchangegen bigint
declare @mycommand2 nvarchar(2000), @myflag int
declare @marker uniqueidentifier

if (@isinsert = 1)
set @partchangegen = -(@generation)
else
set @partchangegen = NULL

if (@tablenick is null)
begin
RAISERROR(14043, 16, -1, '@tablenick', 'sp_MSsetrowmetadata')
return (1)
end
if (@rowguid is null)
begin
RAISERROR(14043, 16, -1, '@rowguid', 'sp_MSsetrowmetadata')
return (1)
end
if (@generation is null)
begin
RAISERROR(14043, 16, -1, '@generation', 'sp_MSsetrowmetadata')
return (1)
end
if (@lineage is null)
begin
RAISERROR(14043, 16, -1, '@lineage', 'sp_MSsetrowmetadata')
return (1)
end

--
-- Check to see if current publication has permission
--
if @pubid is NULL
begin
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @tablenick = @tablenick
if @retcode<>0 or @@ERROR<>0 return (1)
end
else
begin
if ({ fn ISPALUSER(@pubid) } <> 1)
begin
RAISERROR (14126, 11, -1)
return (1)
end
end

if @compatlevel < 90
begin
set @lineage= {fn LINEAGE_80_TO_90(@lineage)}
if @colv is not null
set @colv= {fn COLV_80_TO_90(@colv)}
end

if (@type=1 or @type=5 or @type=6)
begin
-- update or insert dbo.MSmerge_tombstone
update dbo.MSmerge_tombstone set generation = @generation, lineage = @lineage, type = @type
where tablenick = @tablenick and rowguid = @rowguid

if (@@rowcount = 0)
begin
insert into dbo.MSmerge_tombstone (rowguid, tablenick, type, generation, lineage)
values(@rowguid, @tablenick, @type, @generation, @lineage)

delete from dbo.MSmerge_current_partition_mappings where rowguid=@rowguid and tablenick=@tablenick
delete from dbo.MSmerge_contents where tablenick = @tablenick and rowguid = @rowguid

if @partition_options > 1 and @partition_id is not null
begin
insert into dbo.MSmerge_past_partition_mappings (publication_number, tablenick, rowguid,
partition_id, generation, reason)
values(@publication_number, @tablenick, @rowguid, @partition_id, @generation, 1)
end
else
begin
insert into dbo.MSmerge_past_partition_mappings (publication_number, tablenick, rowguid,
partition_id, generation, reason)
values (0, @tablenick, @rowguid, -1, @generation, 1)
end
end
else
begin
update dbo.MSmerge_past_partition_mappings
set generation = @generation
where tablenick = @tablenick and rowguid = @rowguid and generation = 0
end
end
else
begin

if not exists (select rowguid from dbo.MSmerge_contents where tablenick = @tablenick and rowguid = @rowguid)
begin
-- for the update case, evaluate partition membership if inserting a new contents row.
-- this is needed when upd_sp does not really make an update, e.g. when no column value
-- really changed. hence the trigger never fires and the contents entry doesn't get the
-- partition id's.
-- for insert case, trigger always takes care of it.

if @partition_options > 1
begin
if @partition_id is not null
begin
insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
select distinct @publication_number, @tablenick, @rowguid, @partition_id
where not exists (select * from dbo.MSmerge_current_partition_mappings
where publication_number = @publication_number
and tablenick = @tablenick
and rowguid = @rowguid
and partition_id = @partition_id)

end
end
else if @isinsert = 0
begin
exec @retcode = sys.sp_MSevaluate_change_membership_for_row @tablenick = @tablenick, @rowguid = @rowguid
if @retcode <> 0 or @@error <> 0
return 1
end

if @isinsert = 1
select @marker = newid()
else
select @marker = NULL

insert into dbo.MSmerge_contents (rowguid, tablenick, generation, partchangegen, lineage, colv1, marker)
values (@rowguid, @tablenick, @generation, @partchangegen, @lineage, @colv, @marker)
delete from dbo.MSmerge_tombstone where tablenick = @tablenick and rowguid = @rowguid
select @was_tombstone = @@rowcount
-- for insert case, trigger always takes care of it.
if @partition_options < 2 and @isinsert = 0
-- don't need to do this for well-partitioned articles.
begin
exec @retcode = sys.sp_MSevaluate_logicalrecordparent @nickname = @tablenick, @rowguid = @rowguid
if @retcode <> 0 or @@error <> 0
return 1
end
end
else
begin
-- update or insert to MSmerge_contents
-- The following updates the generation to passed in value only if the
-- filter columns have not changed.

-- If filter values have changed, update the generation to gen_cur for article -
-- This will allow a subsequent download to cleanup rows that don't belong at subscriber
-- the trigger would have already set partchangegen to gencur in case there was a partition column or filtering
-- column that changed. Here we ill set the generation to be the passed in generation only if
-- partchangegen is NULL. If not we will set it to generation itself which will be gen_cur

-- in some cases it is possible that the trigger was NFR. In that case both partchangegen and generation may have
-- old values. Hence in those cases we want to be sure that the generation is a valid open gen.
update dbo.MSmerge_contents
set generation = case when (isnull(partchangegen, -1) <> mc.generation and isnull(partchangegen, -1) <> (-mc.generation)) or g.genstatus in (1,2) then @generation else mc.generation end,
lineage = @lineage,
colv1 = @colv
-- @@@colv1 = case when datalength(@colv) < datalength(colv1) then colv1 else @colv end
from dbo.MSmerge_contents mc, dbo.MSmerge_genhistory g
where mc.tablenick = @tablenick
and mc.rowguid = @rowguid
and g.generation = mc.generation
end

end -- end of insert/update

IF @@ERROR<>0 return (1)
return (0)

sp_MSsetreplicastatus (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_MSsetreplicastatus(uniqueidentifier @subid
, int @status_value)

MetaData:

   
create procedure sys.sp_MSsetreplicastatus
(@subid uniqueidentifier,
@status_value int
) AS

--
-- Check to see if current publication has permission
--
declare @retcode int

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

if object_id(N'dbo.sysmergesubscriptions','U') is null
return 0

IF EXISTS (select subid from dbo.sysmergesubscriptions where subid=@subid)
begin
update dbo.sysmergesubscriptions set status = @status_value,
cleanedup_unsent_changes = case when @status_value=1 then 0 else cleanedup_unsent_changes end
WHERE subid=@subid
if @@ERROR<>0 return (1)

if @status_value = 1
begin
-- update the publisher replica row's cleanedup_unsent_changes as well.
update dbo.sysmergesubscriptions set cleanedup_unsent_changes = 0
WHERE subid = (select top 1 pubid from dbo.sysmergesubscriptions where subid = @subid)
if @@ERROR<>0 return (1)
end

update dbo.MSmerge_replinfo set resync_gen=-1 WHERE repid=@subid
if @@ERROR<>0 return (1)
end
return (0)

sp_MSsetreplicaschemaversion (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_MSsetreplicaschemaversion(uniqueidentifier @subid
, int @schemaversion
, uniqueidentifier @schemaguid)

MetaData:

 create procedure sys.sp_MSsetreplicaschemaversion(  
@subid uniqueidentifier,
@schemaversion int,
@schemaguid uniqueidentifier
) AS
begin
set nocount on
--
-- Check to see if current publication has permission
--
declare @retcode int

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

update dbo.sysmergesubscriptions set schemaversion = @schemaversion, schemaguid = @schemaguid
where subid = @subid

if @@error <> 0
return (1)

return (0)
end

sp_MSsetreplicainfo (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_MSsetreplicainfo(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @datasource_type
, nvarchar @server_name
, nvarchar @db_name
, nvarchar @datasource_path
, varbinary @replnick
, int @schemaversion
, uniqueidentifier @subid
, int @compatlevel
, int @partition_id
, int @replica_version
, bit @activate_subscription)

MetaData:

 create procedure sys.sp_MSsetreplicainfo  
(@publisher sysname,
@publisher_db sysname,
@publication sysname,
@datasource_type int = 0, -- 0 = SQL Server, 1 = DSN, 2 = Jet --
@server_name sysname = NULL, -- Server Name --
@db_name sysname = NULL, -- Database Name --
@datasource_path nvarchar(255) = NULL,-- Datasource path - JET MDB file path etc --
@replnick varbinary(6) = NULL,
@schemaversion int = NULL,
@subid uniqueidentifier = NULL,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@partition_id int = NULL,
@replica_version int = 60, -- 60=shiloh sp3 and below, 90=Yukon
@activate_subscription bit = 1)
as
declare @pubid uniqueidentifier
declare @repid uniqueidentifier
declare @retcode int
declare @maxlevel int

--
-- ODBC Issue - trim names
--
select @publisher_db = RTRIM(@publisher_db)
select @db_name = RTRIM(@db_name)

--
-- Security Check and publication validation
--
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, @publisher_db, @publisher, @pubid output
if @retcode <> 0 or @@error <> 0
return 1

if (@server_name is NULL)
SET @server_name = publishingservername()

if (@db_name is NULL)
set @db_name = db_name()

SELECT @repid = subid FROM dbo.sysmergesubscriptions
WHERE UPPER(subscriber_server) collate database_default = UPPER(@server_name) collate database_default
and db_name = @db_name and pubid = @pubid
if @repid is NULL
begin
RAISERROR(20021, 16, -1)
return (1)
end

update dbo.MSmerge_replinfo set validation_level = 0, resync_gen=-1 where repid=@repid

if 1=@activate_subscription
begin
update dbo.sysmergesubscriptions set status=1 where subid=@repid and (status=5 or status=0)
end

if @schemaversion is not null and
-- sp_MSsetreplicainfo is also called to update the subscriber replica info that is stored
-- at the publisher. If the subscriber has a schemaversion of -1, it means that the subscriber
-- wants to reinit. However, this should not be set at the publisher, because this would
-- falsely indicate that the publisher wants to reinit.
(
@schemaversion <> -1 or
UPPER(publishingservername()) collate database_default <> UPPER(@publisher) collate database_default or
db_name() <> @publisher_db
)
begin
update dbo.sysmergesubscriptions set schemaversion = @schemaversion where subid = @repid
if @@error <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end

if @subid is not null and @subid <> @repid
begin
-- Fix the repid for pull subscribers before we copy around global replica rows --
update dbo.MSmerge_replinfo set repid = @subid where repid = @repid
if @@error <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
update dbo.sysmergesubscriptions set subid = @subid where subid = @repid
if @@error <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end

if @replnick IS NOT NULL
begin
-- If this nickname isn't already assigned, reset it --
-- Replnick in 80 was int and in 90 is binary(6). The proc
does a implicit conversion from int to varbinary(6) but we still
need to reverse the binary value to get a corresponding 90 replnick --
if @compatlevel < 90
set @replnick= cast(reverse(substring(@replnick,1,4)) as binary(4))+ 0x0000
else
begin
-- 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.
select @maxlevel= sys.fn_MSgetmaxbackcompatlevel()

if @maxlevel <= 80
set @replnick = substring(@replnick,1,4) + 0x0000
end
if exists (select * from dbo.sysmergesubscriptions
where replnickname = @replnick and subid = subid and
((UPPER(subscriber_server) collate database_default <> UPPER(@server_name) collate database_default) or db_name <> @db_name))
return (0)
update dbo.sysmergesubscriptions set replnickname = @replnick where subid = @subid
if @@error <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end

if @partition_id is not null
begin
if exists (select * from dbo.sysmergepartitioninfo where pubid = @pubid and partition_options = 3)
begin
if exists (select 1 from dbo.sysmergesubscriptions
where pubid = @pubid and partition_id = @partition_id and subid<>@subid)
begin
raiserror(22525, 16, -1, @publication)
return 1
end
end
update dbo.sysmergesubscriptions set partition_id = @partition_id where subid = @subid
if @@error <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end

if @replica_version is not null
begin
update dbo.sysmergesubscriptions set replica_version = @replica_version where subid = @subid
if @@error <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end

return (0)

sp_MSsetlastsentgen (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_MSsetlastsentgen(uniqueidentifier @repid
, bigint @srcgen
, uniqueidentifier @srcguid)

MetaData:

   
CREATE PROCEDURE sys.sp_MSsetlastsentgen
(@repid uniqueidentifier, @srcgen bigint, @srcguid uniqueidentifier)
as
--
-- Check to see if current publication has permission
--
declare @retcode int
declare @pubid uniqueidentifier
declare @subscription_type smallint

-- if this is the light weight client we have nothing to set here
if object_id('dbo.MSmerge_rowtrack', 'U') is not null
return 0

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

if (@repid is null)
begin
RAISERROR(14043, 16, -1, '@repid', 'sp_MSsetlastsentgen')
return (1)
end

if object_id('MSmerge_contents') is NULL
begin
RAISERROR(20054 , 16, -1)
return (1)
end

--
-- This is a special case which is used to update the anonymous subscriptions sent and
-- received generation guids at the end of initializing a dynamic subscription. This is
-- used in the case where an anonymous subscription is reinitialized after a merge metadata
-- cleanup
--
if (@srcgen is null and @srcguid is null)
begin

declare @lastrecsentgen bigint
declare @lastrecsentguid uniqueidentifier

select @lastrecsentgen = max(g.generation)
from MSmerge_genhistory g,
(select isnull(gen2.generation, gen1.generation) as generation
from (select max(generation) as generation from dbo.MSmerge_genhistory where genstatus in (1,2)) as gen1,
(select min(generation) as generation from dbo.MSmerge_genhistory where genstatus in (0,4)) as gen2
) as minopengen
where g.generation <= minopengen.generation and g.genstatus in (1,2)
if @lastrecsentgen IS NOT NULL
select @lastrecsentguid = guidsrc from dbo.MSmerge_genhistory where generation = @lastrecsentgen
update dbo.sysmergesubscriptions set sentgen= @lastrecsentgen, sentguid = @lastrecsentguid
where subid = @repid
IF @@ERROR <>0 return (1)
end
else
begin
-- check for setting a sentgen which is obviously too high
if (exists (select * from dbo.MSmerge_genhistory where generation < @srcgen and
genstatus in (0,4) and
(art_nick = 0 or art_nick is null or art_nick in
(select nickname from dbo.sysmergearticles where
pubid = @pubid) )))
begin
RAISERROR('Setting sentgen too high', 16, -1)
return (1)
end
update dbo.sysmergesubscriptions set sentgen= @srcgen, sentguid = @srcguid
where subid = @repid
IF @@ERROR <>0 return (1)
end
return (0)

sp_MSsetgentozero (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_MSsetgentozero(int @tablenick
, uniqueidentifier @rowguid
, tinyint @metatype)

MetaData:

 create procedure sys.sp_MSsetgentozero  
(@tablenick int,
@rowguid uniqueidentifier,
@metatype tinyint= null)
as
declare @retcode int
declare @METADATA_TYPE_Tombstone tinyint
declare @METADATA_TYPE_Contents tinyint
declare @METADATA_TYPE_PartialDelete tinyint
declare @METADATA_TYPE_SystemDelete tinyint

set @METADATA_TYPE_Tombstone= 1
set @METADATA_TYPE_Contents= 2
set @METADATA_TYPE_PartialDelete= 5
set @METADATA_TYPE_SystemDelete= 6

if (@rowguid is null)
begin
RAISERROR(14043, 16, -1, '@rowguid', 'sp_MSsetgentozero')
return (1)
end
if (@tablenick is null)
begin
RAISERROR(14043, 16, -1, '@tablenick', 'sp_MSsetgentozero')
return (1)
end
if (@metatype is not null and
@metatype not in (@METADATA_TYPE_Tombstone,
@METADATA_TYPE_Contents,
@METADATA_TYPE_PartialDelete,
@METADATA_TYPE_SystemDelete))
begin
-- RAISERROR(20052, 16, -1)
return 0
end

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

if @metatype in (@METADATA_TYPE_Tombstone,
@METADATA_TYPE_PartialDelete,
@METADATA_TYPE_SystemDelete)
begin
update dbo.MSmerge_tombstone
set generation = 0
where tablenick = @tablenick and rowguid = @rowguid
end
else if @metatype = @METADATA_TYPE_Contents
begin
update dbo.MSmerge_contents
set generation = 0
where tablenick = @tablenick and rowguid = @rowguid

update dbo.MSmerge_past_partition_mappings
set generation = 0
where tablenick = @tablenick and rowguid = @rowguid
end
else
begin
update dbo.MSmerge_tombstone
set generation = 0
where tablenick = @tablenick and rowguid = @rowguid

if 0 = @@rowcount
begin
update dbo.MSmerge_contents
set generation = 0
where tablenick = @tablenick and rowguid = @rowguid
end

update dbo.MSmerge_past_partition_mappings
set generation = 0
where tablenick = @tablenick and rowguid = @rowguid
end

return 0

sp_MSsetlastrecgen (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_MSsetlastrecgen(uniqueidentifier @repid
, bigint @srcgen
, uniqueidentifier @srcguid)

MetaData:

   
CREATE PROCEDURE sys.sp_MSsetlastrecgen
(@repid uniqueidentifier, @srcgen bigint, @srcguid uniqueidentifier)
as
--
-- Check to see if current publication has permission
--
declare @retcode int

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

if (@repid is null)
begin
RAISERROR(14043, 16, -1, '@repid', 'sp_MSsetlastrecgen')
return (1)
end
if (@srcgen is null)
begin
RAISERROR(14043, 16, -1, '@srcgen', 'sp_MSsetlastrecgen')
return (1)
end
if (@srcguid is null)
begin
RAISERROR(14043, 16, -1, '@srcguid', 'sp_MSsetlastrecgen')
return (1)
end
if object_id('MSmerge_contents') is NULL and
object_id('MSmerge_rowtrack') is NULL
begin
RAISERROR(20054 , 16, -1)
return (1)
end

update dbo.sysmergesubscriptions set recgen = @srcgen, recguid = @srcguid
where subid = @repid
IF @@ERROR <>0 return (1)

return (0)

sp_MSsetcontext_replagent (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_MSsetcontext_replagent(tinyint @agent_type
, bit @is_publisher)

MetaData:

 create procedure sys.sp_MSsetcontext_replagent @agent_type tinyint, @is_publisher bit = 0  
as
begin
declare @cur_context varbinary(128)
declare @cur_context_first_byte binary(1)
declare @bitmask tinyint

-- agent type: snapshot=1, logreader=2, distrib=3, merge=4
-- bit to set: snapshot=1, logreader=2, distrib=4, merge=8
select @bitmask = case
when @agent_type = 1 then 1
when @agent_type = 2 then 2
when @agent_type = 3 then 4
when @agent_type = 4 then 8
end

if @is_publisher = 1
select @bitmask = (@bitmask | 16)

select @cur_context = isnull(context_info(),0x00)

-- get the first byte out. the replication agent flags are set in the first byte.
select @cur_context_first_byte = substring(@cur_context, 1, 1)
-- set the appropriate bit in this one byte (leaving other bits unchanged).
select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) | @bitmask)
-- replace the first byte of the 128 byte binary variable, so that now it has the appropriate bit set.
select @cur_context = convert(varbinary(128),stuff (@cur_context, 1, 1, @cur_context_first_byte))
-- set the context_info again with the new binary(128) value.
set context_info @cur_context

if @@error <> 0
return 1

return 0
end

sp_MSsetcontext_bypasswholeddleventbit (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_MSsetcontext_bypasswholeddleventbit(bit @onoff)

MetaData:

 create procedure sys.sp_MSsetcontext_bypasswholeddleventbit @onoff bit --  1 to turn on  
as
begin
declare @cur_context varbinary(128)
declare @cur_context_first_byte binary(1)
declare @bitmask tinyint
declare @retcode int

--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_subscribe_withddladmin
IF @@ERROR <> 0 or @retcode <> 0
return (1)

-- bit to set: snapshot=1, logreader=2, distrib=4, merge=8,
-- replication_agent=16, merge_identityrange_alterconstraint=32
-- merge_bypasswholeddleventbit=64
if @onoff=1
set @bitmask=64
else
set @bitmask=255-64

-- get the current context_info. remember we only want to modify a bit without changing the rest of the info
select @cur_context = isnull(context_info(),0x00)

-- get the first byte out. the replication agent flags are set in the first byte.
select @cur_context_first_byte = substring(@cur_context, 1, 1)
-- set the appropriate bit in this one byte (leaving other bits unchanged).
if @onoff=1
select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) | @bitmask)
else
select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) & @bitmask)

-- replace the first byte of the 128 byte binary variable, so that now it has the appropriate bit set.
select @cur_context = convert(varbinary(128),stuff (@cur_context, 1, 1, @cur_context_first_byte))
-- set the context_info again with the new binary(128) value.
set context_info @cur_context

if @@error <> 0
return 1

return 0
end

sp_MSsetconflicttable (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_MSsetconflicttable(nvarchar @article
, nvarchar @conflict_table
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

   
-- Add the conflict table pointer to dbo.sysmergearticles - Used by reconciler --
create procedure sys.sp_MSsetconflicttable (
@article sysname,
@conflict_table sysname,
@publisher sysname = NULL,
@publisher_db sysname = NULL,
@publication sysname = NULL
) AS

declare @artid uniqueidentifier
declare @pubid uniqueidentifier
declare @quoted_conflict_table nvarchar(270)
declare @qual_conflict_table nvarchar(270)
declare @basetableid int
declare @conflicttableowner nvarchar(270)
declare @retcode int

-- special case'd this out for backward compatibility with 7.0 subscribers.
if @publisher is NULL and @publisher_db is NULL and @publication is NULL
return (0)

--
-- Security Check get @pubid
--
SELECT @pubid = NULL
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, @publisher_db, @publisher, @pubid output
if @retcode <> 0 or @@error <> 0
return 1


select @artid = artid, @basetableid=objid FROM dbo.sysmergearticles WHERE name = @article and pubid=@pubid
if @artid IS NULL
BEGIN
RAISERROR (20027, 16, -1, @article)
RETURN (1)
END

select @quoted_conflict_table = quotename(@conflict_table)

-- in yukon the conflict table will always be owned by dbo schema
select @qual_conflict_table = '[dbo].' + @quoted_conflict_table

exec @retcode = sys.sp_MS_marksystemobjectwitherror @qual_conflict_table
if @retcode<>0 or @@ERROR<>0 return (1)
update dbo.sysmergearticles set conflict_table = @conflict_table where artid = @artid and pubid=@pubid
if @@ERROR <> 0
return (1)

declare @rgcol nvarchar(258)
declare @indname nvarchar(258)
declare @owner sysname
declare @quotedname nvarchar(517)

select @rgcol = QUOTENAME(name) from sys.columns where object_id = @basetableid and is_rowguidcol = 1
select @owner=SCHEMA_NAME(schema_id) from sys.objects where name=@conflict_table
select @indname = 'uc_' + @conflict_table
if len(@indname) > 128
begin
select @indname = substring(@indname,1,92) + convert(nvarchar(36), newid())
end
set @indname = QUOTENAME(@indname)
set @quotedname = QUOTENAME(@owner) + '.' + QUOTENAME(@conflict_table)

-- only create the conflict table index when needed.
if not exists (select * from sys.indexes where object_id = object_id(@quotedname) and index_id = 1)
and exists (select * from sys.columns where object_id = object_id(@quotedname) and quotename(name) = @rgcol)
and exists (select * from sys.columns where object_id = object_id(@quotedname) and name = 'origin_datasource')
begin
exec ('Create unique clustered index ' + @indname + ' on ' + @quotedname +
' (' + @rgcol + ',origin_datasource)' )
if @@error <> 0
return (1)
end

-- Create the conflict insert proc only when necessary for performance reason --
if exists (select * from dbo.sysmergearticles where artid = @artid and pubid=@pubid and OBJECT_ID(ins_conflict_proc) is null)
BEGIN
exec sys.sp_MSgetconflictinsertproc @pubid=@pubid, @artid = @artid, @output = 0, @force_generate_proc = 1
IF @@ERROR<> 0 OR @retcode <> 0
return (1)
END

return (0)

sp_MSsetconflictscript (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_MSsetconflictscript(nvarchar @publication
, nvarchar @article
, nvarchar @conflict_script
, nvarchar @login
, nvarchar @password)

MetaData:

   
-- Add the conflict script pointer to dbo.sysmergearticles - Used by snapshot --
create procedure sys.sp_MSsetconflictscript (
@publication sysname,
@article sysname,
@conflict_script nvarchar(255),
@login sysname =NULL,
@password nvarchar(524) =NULL
) AS

declare @artid uniqueidentifier
declare @pubid uniqueidentifier

--
-- Check for publish permission.
--
declare @retcode int
exec @retcode=sys.sp_MSreplcheck_publish
if @retcode<>0 or @@ERROR<>0 return (1)

select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid IS NULL
BEGIN
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END

select @artid = artid FROM dbo.sysmergearticles WHERE name = @article AND pubid = @pubid
if @artid IS NULL
BEGIN
RAISERROR (20027, 16, -1, @article)
RETURN (1)
END

update dbo.sysmergearticles set conflict_script = @conflict_script where artid = @artid and pubid=@pubid
if @@ERROR <> 0
return (1)
return (0)

sp_MSsetaccesslist (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_MSsetaccesslist(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db)

MetaData:

 create procedure sys.sp_MSsetaccesslist  
@publication sysname,
@publisher sysname,
@publisher_db sysname
AS
-- we don't need to do anything here since we use role based security
return 0

sp_MSsetbit (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_MSsetbit(smallint @coltoadd
, int @toset)

MetaData:

 create procedure sys.sp_MSsetbit  
@bm varbinary(128) output,
@coltoadd smallint,
@toset int = 1
AS
declare @bytenum smallint
declare @bit smallint
declare @mask tinyint
declare @newbyte tinyint
declare @oldbyte tinyint

SELECT @bytenum = 1 + FLOOR((@coltoadd-1)/8)

IF @bytenum > 128 return 0

SELECT @bit = (@coltoadd-1) % 8

SET @mask = POWER(2, @bit)
if @toset = 0
SET @mask = (~@mask % 256)

if @bm is null
set @bm = 0x0
while datalength(@bm) < @bytenum
set @bm = @bm + 0x00

SET @oldbyte = SUBSTRING( @bm, @bytenum, 1)
IF @oldbyte IS NULL SET @oldbyte = 0
if @toset <> 0
SET @newbyte = @oldbyte | @mask
else
SET @newbyte = @oldbyte & @mask

if (@bytenum = 1)
set @bm = convert(binary(1), @newbyte) + substring(@bm, 2, 127)
else
set @bm = substring(@bm, 1, @bytenum - 1) + convert(binary(1), @newbyte) + substring(@bm, @bytenum + 1, 128 - @bytenum)

Total Pageviews