create procedure sys.sp_MSreseed
(
@objid int,
@next_seed bigint,
@range bigint,
@is_publisher int = 0,
@check_only bit = 0,
@drop_only bit = 0,
@initial_setting bit = 0,
@bound_value bigint = 0
)
AS
begin
declare @min_value bigint
declare @max_value bigint
declare @constraintname sysname
declare @artid uniqueidentifier
declare @min_str nvarchar(20)
declare @identity_column sysname
declare @colid int
declare @colname sysname
declare @max_str nvarchar(20)
declare @owner sysname
declare @tablename sysname
declare @qualname nvarchar(517)
declare @dbname sysname
declare @prefix sysname
declare @retcode int
declare @cmd nvarchar(2000)
SET NOCOUNT ON
-- DDL trigger depends on this prefix, if changed, be sure to change sp_MSTran_altertable as well
select @prefix = 'repl_identity_range_'
select @colid=1
select @dbname=db_name()
select @colname=name from sys.columns where column_id=@colid and object_id = @objid
select @retcode = 0
-- Security check: dbo & sysadmin only
execute @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
begin
return 1
end
select @colname = NULL
select @colname=name, @colid=column_id from sys.columns where object_id = @objid and is_identity=1
if @colname is NULL
begin
select @tablename = object_name(@objid)
raiserror(21756, 16, 1, @tablename)
return (1)
end
if @range > 0
begin
select @min_value = @next_seed
if @initial_setting = 1
select @min_value = @bound_value - 1 -- allow for race condition with ident_current.
-- @next_seed + @range can be used by this db, but not other db.
-- Leave one slot unused. This is to prevent violation of primary key constraint
-- if the next value is used by a subscriber and the publisher has received it.
-- It seems the pk constraint will be validated before this check.
-- select @max_value = @next_seed + @range + 1
select @max_value = @next_seed + @range
end
else
begin
select @max_value = @next_seed
if @initial_setting = 1
select @max_value = @bound_value + 1 -- allow for race condition with ident_current.
-- @next_seed + @range can be used by this db, but not other db.
-- Leave one slot unused. This is to prevent violation of primary key constraint
-- if the next value is used by a subscriber and the publisher has received it.
-- It seems the pk constraint will be validated before this check.
-- select @min_value = @next_seed + @range - 1
select @min_value = @next_seed + @range
end
select @min_str = convert(nvarchar, @min_value)
select @max_str = convert(nvarchar, @max_value)
select @owner = schema_name(schema_id) from sys.objects where object_id = @objid
select @tablename = object_name(@objid)
select @qualname = QUOTENAME(@owner) + '.' + QUOTENAME(@tablename)
if @check_only = 0
begin
DBCC CHECKIDENT(@qualname, RESEED, @next_seed) with no_infomsgs
if @@ERROR<>0
return (1)
-- ident_current is NULL the server will start inserting from next_seed. However if ident_current
-- is not null the server will insert from next_seed+ident_increment. However our constraint
-- always says ">next_seed". To keep the constrain the same and range values consistent
-- if ident_current is NULL here we need to reseed to next_seed + ident_increment.
if exists (select 1 from sys.identity_columns where object_id=@objid and last_value is NULL)
begin
declare @temp_next_seed bigint
select @temp_next_seed = @next_seed + IDENT_INCR(@qualname)
DBCC CHECKIDENT(@qualname, RESEED, @temp_next_seed) with no_infomsgs
if @@ERROR<>0
return (1)
end
end
if @is_publisher < 0
begin
-- Used by tran
-- Publisher side constraint maybe transfered to subscriber.
-- If so, it will be dropped with code below.
select @constraintname = NULL
select @constraintname = name from sys.objects where name like @prefix + 'tran_[0-9]%' and
type='C' and
parent_object_id = @objid
if @constraintname is not null
begin
-- signal to db ddl trigger to bail out
EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=1
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
select @cmd = 'alter table '+ @qualname + ' drop constraint ' + quotename(@constraintname)
exec(@cmd)
if @@ERROR<>0
goto err_clean
EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
end
-- Prepare the name of the constraint we are going to create
select @constraintname = @prefix + 'tran_' + convert(nvarchar(10), @objid)
while (1=1)
begin
if exists (select * from sys.objects where name = @constraintname)
begin
-- A constraint already exists in the database with same name
-- Change the name
select @constraintname = @constraintname + convert(nvarchar(10), @objid)
end
else
begin
-- We can use the current constraint name to create the constraint
break
end
end
end
else
begin
-- Used by merge
-- turn on context bit so DDL event will not be replicated
EXEC @retcode = sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=1
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
declare @prefixlike sysname
select @prefixlike = @prefix + '%'
-- drop all identity range constraints which contain the repl_identity_range prefix
select @constraintname = NULL
select top 1 @constraintname = name from sys.objects where parent_object_id=@objid and type='C' and name like @prefixlike
while @constraintname is not NULL
begin
select @cmd = 'alter table '+ @qualname + ' drop constraint ' + quotename(@constraintname)
exec(@cmd)
if @@ERROR<>0
goto err_clean
select @constraintname = NULL
select top 1 @constraintname = name from sys.objects where parent_object_id=@objid and type='C' and name like @prefixlike
end
select @artid=artid from dbo.sysmergearticles where objid=@objid
if @is_publisher=1
select @constraintname = @prefix + 'pub_' + convert(nvarchar(36), @artid)
else
if @is_publisher = 2
select @constraintname = @prefix + 'repub_' + convert(nvarchar(36), @artid)
else
select @constraintname = @prefix + 'sub_' + convert(nvarchar(36), @artid)
select @constraintname = REPLACE(@constraintname, '-', '_')
end
-- Don't add new constraint if only drop is needed.
if @drop_only = 1
goto success_clean
declare @qualcolname nvarchar(258)
select @qualcolname = quotename(@colname)
-- signal to db ddl trigger to bail out
EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=1
IF @@ERROR <> 0 or @retcode <> 0
goto err_clean
select @cmd = 'alter table '+ @qualname + ' with NOCHECK add CONSTRAINT ' + quotename(@constraintname) + ' check NOT FOR REPLICATION ( ' + @qualcolname + ' > '
+ @min_str + ' and ' + @qualcolname + ' < ' + @max_str + ')'
exec(@cmd)
if @@ERROR<>0
goto err_clean
goto success_clean
success_clean:
EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0
return 0
err_clean:
EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0
return 1
end
