May 21, 2012

sp_MSinsertschemachange (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_MSinsertschemachange(uniqueidentifier @pubid
, uniqueidentifier @artid
, int @schemaversion
, uniqueidentifier @schemaguid
, int @schematype
, nvarchar @schematext
, int @schemasubtype
, tinyint @update_schemaversion)

MetaData:

   
create procedure sys.sp_MSinsertschemachange(
@pubid uniqueidentifier,
@artid uniqueidentifier = NULL, -- Can be NULL for directory commands --
@schemaversion int,
@schemaguid uniqueidentifier,
@schematype int,
@schematext nvarchar(max),
@schemasubtype int = 0,
@update_schemaversion tinyint = 1
)
as
declare @retcode int
declare @constraintname sysname
declare @owner sysname
declare @tablename sysname
declare @qualname nvarchar(270)
declare @objid int
declare @alter_table_type int
declare @subid uniqueidentifier

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

select @alter_table_type = 11

-- Parameter validation --
if (@schemaversion is null)
begin
RAISERROR(14043, 16, -1, '@schemaversion', 'sp_MSinsertschemachange')
return (1)
end

--
-- Special case: for push subscription, reinit-with-upload, we do not want to cleanup subscriber side
-- cause we need to upload; however we still need to apply alter-table. To avoid duplicate key insert
-- failure, we no-OP this insert of alter-table schema.
--
if @schematype=@alter_table_type and exists
(select * from dbo.sysmergeschemachange where pubid=@pubid and schemaversion=@schemaversion)
return (1)

begin transaction tran_sp_MSinsertschemachange
save transaction tran_sp_MSinsertschemachange

if not exists (select * from dbo.sysmergeschemachange with (HOLDLOCK TABLOCKX)
where pubid=@pubid and artid=@artid and
schemaversion=@schemaversion and schemaguid = @schemaguid and
schematype = @schematype)
begin
insert into dbo.sysmergeschemachange with (HOLDLOCK TABLOCKX)
(pubid, artid, schemaversion, schemaguid, schematype, schematext, schemasubtype)
values (@pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext, @schemasubtype)
end
else
begin
update dbo.sysmergeschemachange with (HOLDLOCK TABLOCKX)
set schematext = @schematext
where pubid=@pubid and artid=@artid and
schemaversion=@schemaversion and schemaguid = @schemaguid and
schematype = @schematype
end
if @@error <> 0
begin
rollback transaction tran_sp_MSinsertschemachange
commit transaction
RAISERROR(21305 , 16, -1)
return (1)
end

-- update the schema version and schemaguid in dbo.sysmergesubscriptions - The following servername is used as a publishing server name --
if @update_schemaversion = 1
begin
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

update dbo.sysmergesubscriptions set schemaversion = @schemaversion, schemaguid = @schemaguid
where subid = @subid and (schemaversion<@schemaversion or schemaversion is NULL)
if @@error <> 0
begin
rollback transaction tran_sp_MSinsertschemachange
commit transaction
RAISERROR(21305 , 16, -1)
return (1)
end
end
commit transaction
return (0)

No comments:

Post a Comment

Total Pageviews