create procedure sys.sp_MSremoveidrangesupport
(@pubid uniqueidentifier, @artid uniqueidentifier, @propagate_ddl_change bit = 0)
as
set nocount on
declare @retcode smallint
declare @source_table nvarchar(517)
declare @ownername sysname
declare @objectname sysname
declare @objid int
select @objid = max(objid) from dbo.sysmergearticles where artid = @artid
if @objid is NULL
return 0
-- get owner name, and table name
select @objectname = name, @ownername = schema_name(schema_id)
from sys.objects where object_id = @objid
-- construct the qualified table name
select @source_table = QUOTENAME(@ownername) + '.' + QUOTENAME(@objectname)
-- no need to check if the other publication also had id range enabled, as it is required
-- that all publications publishing the same article, should have the same id range
-- management option
if exists (select * from dbo.sysmergearticles where artid=@artid and pubid<>@pubid)
begin
if exists (select * from MSmerge_identity_range where artid=@artid and subid=@pubid)
begin
declare @pubid_other uniqueidentifier
select top 1 @pubid_other=pubid from dbo.sysmergearticles where artid=@artid and pubid<>@pubid
if @pubid_other is NULL
return (1)
update dbo.MSmerge_identity_range set subid=@pubid_other where subid=@pubid and artid=@artid
if @@ERROR<>0
return (1)
delete from dbo.MSmerge_identity_range where artid=@artid and subid in (select subid from dbo.sysmergesubscriptions where pubid=@pubid)
if @@ERROR<>0
return (1)
end
end
else
begin
declare @constraintname nvarchar(258)
select @constraintname = 'repl_identity_range_%' + convert(nvarchar(36), @artid)
select @constraintname = REPLACE(@constraintname, '-', '_')
if exists (select * from sys.objects where name like @constraintname and type='C')
begin
select @constraintname = quotename(name) from sys.objects where name like @constraintname and type ='C'
-- turn on context bit if needed so DDL event will not be replicated
EXEC @retcode = sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=1
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
exec ('alter table '+ @source_table + ' drop constraint ' + @constraintname)
if @@ERROR<>0
begin
EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
return (1)
end
EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
if @propagate_ddl_change = 1
begin
declare @schematext nvarchar(4000)
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
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()
set @schematype = 300 /* REPLICATE_DDL_ACTIONS */
set @schematext = N' if object_id(N''' + sys.fn_replreplacesinglequote(@source_table) + ''', ''U'') is not NULL and object_id(N''' + @constraintname + ''', ''C'') is not NULL alter table '+ @source_table + ' drop constraint ' + @constraint
name
exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
end
end
if exists (select * from dbo.MSmerge_identity_range where artid=@artid)
begin
delete from dbo.MSmerge_identity_range where artid=@artid and subid in (select subid from dbo.sysmergesubscriptions where pubid=@pubid)
if @@ERROR<>0
return (1)
DBCC CHECKIDENT(@source_table, RESEED) with no_infomsgs
if @@ERROR<>0
return (1)
end
end