May 2, 2012

sp_MSadd_mergereplcommand (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_MSadd_mergereplcommand(nvarchar @publication
, nvarchar @article
, int @schematype
, nvarchar @schematext
, nvarchar @tablename)

MetaData:

   
-- Add the replication command to the database - Used by snapshot --
create procedure sys.sp_MSadd_mergereplcommand (
@publication sysname,
@article sysname = NULL,
@schematype int,
@schematext nvarchar(2000),
@tablename sysname = NULL
) AS
declare @schemaguid uniqueidentifier
declare @schemaversion int
declare @retcode int
declare @pubid uniqueidentifier
declare @artid uniqueidentifier
declare @objid int

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

if @publication IS NULL
BEGIN
RAISERROR (14003, 16, -1)
RETURN (1)
END

select @pubid = pubid FROM dbo.sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
select @artid = artid, @objid = objid FROM dbo.sysmergeextendedarticlesview WHERE name = @article and pubid = @pubid
--
-- For certain system tables that are bcped out such as MSmerge_contents
-- and dbo.MSmerge_tombstone use the article name as sys.objects.name and get
-- sys.objects.object_id as the artid
--
if (@artid IS NULL) AND (@schematype <> 7)
begin
declare @binguid binary(16)
set @binguid = OBJECT_ID(@article)
set @artid = convert(uniqueidentifier, @binguid)
end
else if @tablename is not NULL and (@schematype <> 7)
begin
declare @binguid1 binary(8)
declare @binguid2 binary(8)
set @binguid1 = convert(binary(8),@objid)
set @binguid2 = convert(binary(8),OBJECT_ID(@tablename))
if @binguid2 is NULL
set @artid = convert(uniqueidentifier, @binguid1)
else
set @artid = convert(uniqueidentifier, @binguid1+@binguid2)
end

--
-- When schematype is 89 SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE the article name is
-- the user_type_id of the udt in string format.
-- When schematype is 91 SCHEMA_TYPE_PARTITIONSCHEME the article name is
-- the function_id of the partition_scheme
-- When schematype is 92 SCHEMA_TYPE_PARTITIONFUNCTION the article name is
-- the function_id of the partition_scheme
-- Convert it to guid and use it as artid to
-- identify the schema change.
--

if (@schematype in (88, 89, 90, 91, 92, 93, 96, 31, 105)) and (@article is not NULL)
begin
select @artid=convert(uniqueidentifier, convert(binary(16), @article))
end

-- now decide if we should insert a new schema change row or update an existing one. 0-insert, 1-update --
declare @UPDATE_SCHEMACHANGE int

if exists (select * from dbo.sysmergeschemachange
where pubid = @pubid
AND artid = @artid
AND ((schematype = @schematype AND schematype <> 31) -- we need to insert new schema change for CREATE SCHEMA
OR (@schematype in (3,4) and schematype in (3,4)) -- SCHEMA_TYPE_NATIVEBCP or SCHEMA_TYPE_CHARACTERBCP
OR (@schematype in (80,81) and schematype in (80,81)) -- SCHEMA_TYPE_SYSTABLE_NATIVE_BCP_90 or SCHEMA_TYPE_SYSTABLE_CHAR_BC_90
OR (@schematype in (82,83) and schematype in (82,83)) -- SCHEMA_TYPE_SYSTABLE_NATIVE_BCP_90_FORGLOBALONLY or SCHEMA_TYPE_SYSTABLE_CHAR_BC_90_FORGLOBALONLY
OR (@schematype in (84,85) and schematype in (84,85)) -- SCHEMA_TYPE_SYSTABLE_NATIVE_BCP_90_FORLIGHTWEIGHT or SCHEMA_TYPE_SYSTABLE_CHAR_BC_90_FORLIGHTWEIGHT
OR (@schematype in (131,132) and schematype in (131,132)) -- SCHEMA_TYPE_DYNAMICNATIVEBCP or SCHEMA_TYPE_DYNAMICCHARBCP
OR (@schematype in (208,209) and schematype in (208,209)) -- SCHEMA_TYPE_DYNAMIC_SYSTABLE_NATIVE_BCP_90 or SCHEMA_TYPE_DYNAMIC_SYSTABLE_CHAR_BCP_90
OR (@schematype in (210,211) and schematype in (210,211)) -- SCHEMA_TYPE_DYNAMIC_SYSTABLE_NATIVE_BCP_90_FORGLOBALONLY or SCHEMA_TYPE_DYNAMIC_SYSTABLE_CHAR_BCP_90_FORGLOBALONLY
OR (@schematype in (212,213) and schematype in (212,213)))) -- SCHEMA_TYPE_DYNAMIC_SYSTABLE_NATIVE_BCP_90_FORLIGHTWEIGHT or SCHEMA_TYPE_DYNAMIC_SYSTABLE_CHAR_BCP_90_FORLIGHTWEIGHT
begin
set @UPDATE_SCHEMACHANGE = 1 -- common case to update a row --
end
else if exists (select * from dbo.sysmergeschemachange
where pubid = @pubid
AND artid = @artid
AND schematype = @schematype
AND schematype = 31
AND schematext = @schematext)
begin
set @UPDATE_SCHEMACHANGE = 2 -- only insert a CREATE SCHEMA row if the schema text is different from the existing one for the article --
end
else
begin
set @UPDATE_SCHEMACHANGE = 0; -- common case to insert a new row --
end

if(@UPDATE_SCHEMACHANGE=1 or @UPDATE_SCHEMACHANGE=2 )
begin
-- Select the existing schema guid --
select @schemaversion = schemaversion, @schemaguid = schemaguid from dbo.sysmergeschemachange
where pubid = @pubid
AND artid = @artid
AND (schematype = @schematype
OR (@schematype in (3,4) and schematype in (3,4))
OR (@schematype in (80,81) and schematype in (80,81))
OR (@schematype in (82,83) and schematype in (82,83))
OR (@schematype in (84,85) and schematype in (84,85))
OR (@schematype in (131,132) and schematype in (131,132))
OR (@schematype in (208,209) and schematype in (208,209))
OR (@schematype in (210,211) and schematype in (210,211))
OR (@schematype in (212,213) and schematype in (212,213)))

-- For directory commands, delete the previous directory before the update --
if (@schematype = 7)
begin

-- Also remove the alternate directory command, pre/post
-- commands, snapshot header commands, and
-- dynsnap validation command from the
-- dbo.sysmergeschemachange table
--
delete dbo.sysmergeschemachange
where pubid = @pubid
AND schematype in (25, 40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 71)

end

--
-- Update the schema change version
--
if(@UPDATE_SCHEMACHANGE=1)
begin
exec @retcode = sys.sp_MSupdateschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext
end
else if (@UPDATE_SCHEMACHANGE=2)
begin
-- don't update the schematext here, and we need the schematext value to find the correct row to update --
exec @retcode = sys.sp_MSupdateschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext, 0
end

if @@error <> 0 or @retcode <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end
else
begin
-- Insert the schema change --
select @schemaversion = schemaversion from dbo.sysmergeschemachange
if (@schemaversion is NULL)
set @schemaversion = 1
else
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange

-- update the schema version and schemaguid in dbo.sysmergesubscriptions --
declare @subid uniqueidentifier
declare @sysmergesub_schemaversion int
declare @sysmergesub_schemaguid uniqueidentifier

select @subid = subid from dbo.sysmergesubscriptions
where UPPER(subscriber_server) collate database_default = UPPER(publishingservername()) collate database_default
and db_name = DB_NAME() and pubid = @pubid

select @sysmergesub_schemaversion = schemaversion, @sysmergesub_schemaguid = schemaguid
from dbo.sysmergesubscriptions where subid = @subid

--
-- If the schema version matches the dbo.sysmergesubscriptions.schemaversion and the schema_type = 52 (snapshot trailer),
-- then reuse the dbo.sysmergesubscriptions.schema_guid. Otherwise generate a new guid.
--
if @sysmergesub_schemaversion is NOT NULL and @schemaversion = @sysmergesub_schemaversion and @schematype = 52
begin
set @schemaguid = @sysmergesub_schemaguid
end
else
begin
set @schemaguid = newid()
end

exec @retcode = sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext
if @@error <> 0 or @retcode <> 0
begin
RAISERROR(20054 , 16, -1)
return (1)
end
end

if (@schematype = 7)
begin
update dbo.sysmergesubscriptions set last_validated=getdate() where pubid=@pubid and subid=@pubid
IF @@ERROR <> 0
begin
RAISERROR(20054 , 16, -1)
RETURN (1)
end
end

--
-- If we are inserting the snapshot trailer then we are done with the snapshot commands.
-- At this time remove all entries of type
-- SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE,
-- SCHEMA_TYPE_PARTITIONSCHEME
-- SCHEMA_TYPE_PARTITIONFUNCTION
-- that are still marked inactive.
--

if (@schematype = 52)
begin
delete from dbo.sysmergeschemachange where pubid=@pubid and schematype in (88, 89, 90, 91, 92, 93, 96, 105) and schemastatus = 0
IF @@ERROR <> 0
begin
RETURN (1)
end
end

return (0)

No comments:

Post a Comment

Total Pageviews