May 21, 2012

sp_MSinsertgenerationschemachanges (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_MSinsertgenerationschemachanges(nvarchar @publication)

MetaData:

 create procedure sys.sp_MSinsertgenerationschemachanges  
@publication sysname
AS
declare @mingen bigint
declare @lastrecgen bigint
declare @lastrecguid uniqueidentifier
declare @lastsentgen bigint
declare @lastsentguid uniqueidentifier
declare @db_name sysname
declare @repid uniqueidentifier
declare @pubid uniqueidentifier
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(2000)
declare @artid uniqueidentifier
declare @retcode int
set nocount on

set @lastrecgen = NULL
set @lastsentgen = NULL
set @db_name = db_name()

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


SELECT @repid = subid FROM dbo.sysmergesubscriptions where pubid = @pubid and subid = @pubid
if @repid is NULL
begin
RAISERROR(20021, 16, -1)
return (1)
end


begin tran
save TRAN MSinsertgenerationschemachanges

select @lastrecgen = 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 @lastrecgen IS NOT NULL
begin
select @lastrecguid = guidsrc from dbo.MSmerge_genhistory where generation = @lastrecgen

set @artid = NULL
set @schematype = 5 -- last rec gen schema type --
select @schematext = 'exec dbo.sp_MSsetlastrecgen ' + '''' + convert(nchar(36), @repid) + '''' + ',' + '''' + convert(nvarchar, @lastrecgen) + '''' + ',' + '''' + convert(nchar(36), @lastrecguid) + ''''

if exists (select * from dbo.sysmergeschemachange
where pubid = @pubid
AND schematype = @schematype)
begin
-- Select the existing schema guid --
select @schemaversion = schemaversion, @schemaguid = schemaguid from dbo.sysmergeschemachange
where pubid = @pubid
AND schematype = @schematype
--
-- Update the schema change version
--
exec @retcode = sys.sp_MSupdateschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext
if @@error <> 0 or @retcode <> 0
goto FAILURE
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

-- generate a new schema guid --
set @schemaguid = newid()
exec @retcode = sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext
if @@error <> 0 or @retcode <> 0
goto FAILURE
end
end

set @schematype = 17 -- last sent gen schema type --
set @artid = NULL
select @schematext = 'exec sys.sp_MScomputelastsentgen ' + '''' + convert(nvarchar(36), @repid) + ''''
if exists (select * from dbo.sysmergeschemachange
where pubid = @pubid
AND schematype = @schematype)
begin
-- Select the existing schema guid --
select @schemaversion = schemaversion, @schemaguid = schemaguid from dbo.sysmergeschemachange
where pubid = @pubid
AND schematype = @schematype
--
-- Update the schema change version
--
exec @retcode = sys.sp_MSupdateschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext
if @@error <> 0 or @retcode <> 0
goto FAILURE
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

-- generate a new schema guid --
set @schemaguid = newid()
exec @retcode = sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion,
@schemaguid, @schematype, @schematext
if @@error <> 0 or @retcode <> 0
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 MSinsertgenerationschemachanges
COMMIT TRANSACTION
end

RETURN 1

No comments:

Post a Comment

Total Pageviews