create procedure sys.sp_MSget_new_idrange
@qualified_object_name nvarchar(300),
@artid uniqueidentifier,
@range_begin numeric(38,0) output,
@range_end numeric(38,0) output,
@next_range_begin numeric(38,0) output,
@next_range_end numeric(38,0) output,
@range_type tinyint, -- 1=publisher range, 2=subscriber range
@ranges_needed tinyint -- 0=none needed, 1=one range needed, 2=both ranges needed
as
declare @subid uniqueidentifier
declare @pubid uniqueidentifier
declare @pub_range bigint
declare @sub_range bigint
declare @identity_support int
declare @artname sysname
declare @max_used numeric(38,0)
declare @is_republisher bit
declare @range bigint
declare @pub_range_begin numeric(38,0)
declare @pub_range_end numeric(38,0)
declare @pub_next_range_begin numeric(38,0)
declare @pub_next_range_end numeric(38,0)
declare @ident_increment numeric(38,0)
declare @ident_current numeric(38,0)
declare @retcode int
declare @root_publisher sysname
if @ranges_needed=0
return 0
select @pubid = NULL
select @subid=subid from dbo.MSmerge_identity_range where artid=@artid and is_pub_range=1 and (sys.fn_MSmerge_islocalsubid(subid)=1)
if @subid is NULL
begin
raiserror(20663, 16, -1)
return 1
end
if (sys.fn_MSmerge_islocalpubid(@subid)=0)
begin
select @pubid = pubid from dbo.sysmergearticles where artid=@artid and (sys.fn_MSmerge_islocalpubid(pubid)=1)
if @pubid is NULL
begin
raiserror(20663, 16, -1)
return 1
end
select @root_publisher = publisher from dbo.sysmergepublications where pubid = @pubid
end
else
select @pubid = @subid
select @artname=name, @pub_range=pub_range, @sub_range=range, @identity_support=identity_support
from dbo.sysmergearticles where artid=@artid and pubid=@pubid
if @identity_support=0 or @pub_range is NULL or @sub_range is NULL
begin
raiserror(20667, 16, -1, @artname)
return 1
end
if @range_type=1
select @range=@pub_range
else
select @range=@sub_range
if exists (select pubid from dbo.sysmergearticles where artid=@artid and pubid<>@pubid and sys.fn_MSmerge_islocalpubid(pubid)=0)
select @is_republisher=1
else
select @is_republisher=0
select @ident_increment = IDENT_INCR(@qualified_object_name)
begin tran
save tran MSgetnewidrange
if @is_republisher=0
begin
select @max_used=max_used,
@pub_range_begin=range_begin,
@pub_range_end=range_end
from dbo.MSmerge_identity_range with (updlock, rowlock) where artid=@artid and subid=@pubid and is_pub_range=1
if @pub_range_begin is NULL or @pub_range_end is NULL or @max_used is NULL
begin
raiserror(20663, 16, -1)
goto FAILURE
end
-- validate that the max(or min) identity value used in the table
-- is not higher than what the table has as the max_used.
select @ident_current = IDENT_CURRENT(@qualified_object_name)
if @ident_current is not NULL
begin
if ((@ident_increment > 0) and (@ident_current > @max_used)) or
((@ident_increment < 0) and (@ident_current < @max_used))
begin
--raiserror(20659, 11, -1, @qualified_object_name)
update dbo.MSmerge_identity_range set max_used = @ident_current where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
goto FAILURE
end
select @max_used = @ident_current
end
end
if @ranges_needed = 1
begin
select @range_begin = @next_range_begin
select @range_end = @next_range_end
select @next_range_begin = @max_used
select @next_range_end = @next_range_begin + @range
end
if @ranges_needed = 2
begin
select @range_begin = @max_used
select @range_end = @range_begin + @range
select @next_range_begin = @range_end
select @next_range_end = @next_range_begin + @range
end
if ((@ident_increment > 0) and (@next_range_end > @pub_range_end)) or
((@ident_increment < 0) and (@next_range_end < @pub_range_end))
begin
-- we should never hit this condition when this is the root publisher
raiserror(20668, 16, -1)
goto FAILURE
end
-- update the max_used for the publisher's identity range
update dbo.MSmerge_identity_range set max_used = @next_range_end where subid=@pubid and artid=@artid and is_pub_range=1
if @@error <> 0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
-- now we are done. The caller of this proc is responsible for updating dbo.MSmerge_identity_range for the subscriber
-- for which this new range was allocated.
end
else
begin
select @pub_range_begin = range_begin,
@pub_range_end = range_end,
@pub_next_range_begin = next_range_begin,
@pub_next_range_end = next_range_end,
@max_used = max_used
from dbo.MSmerge_identity_range with (updlock, rowlock) where artid=@artid and subid=@subid and is_pub_range=1
if @pub_range_begin is NULL or @pub_next_range_begin is NULL or @max_used is NULL
begin
raiserror(20662, 16, -1, @root_publisher)
goto FAILURE
end
-- convert the range and republisher's allocations to absolute values so that
-- the comparisons can be done the same way whether the we are using positive
-- or negative increment
if @ident_increment < 0
begin
select @pub_range_begin = -1*@pub_range_begin
select @pub_range_end = -1*@pub_range_end
select @pub_next_range_begin = -1*@pub_next_range_begin
select @pub_next_range_end = -1*@pub_next_range_end
select @max_used = -1*@max_used
select @range = -1*@range
end
if @ranges_needed=1
begin
select @range_begin = @next_range_begin
select @range_end = @next_range_end
exec @retcode=sys.sp_MSallocate_one_idrange
@pub_range_begin,
@pub_range_end,
@pub_next_range_begin,
@pub_next_range_end,
@range,
@max_used output,
@next_range_begin output,
@next_range_end output
if @@error<>0 or @retcode<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
if @ident_increment < 0
begin
select @max_used = -1*@max_used
select @next_range_begin = -1*@next_range_begin
select @next_range_end = -1*@next_range_end
end
-- update the max_used for the republisher's identity range
update dbo.MSmerge_identity_range set max_used = @max_used where subid=@subid and artid=@artid and is_pub_range=1
if @@error <> 0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
-- now we are done. The caller of this proc is responsible for updating dbo.MSmerge_identity_range for the subscriber
-- for which this new range was allocated.
end
if @ranges_needed=2
begin
exec @retcode=sys.sp_MSallocate_one_idrange
@pub_range_begin,
@pub_range_end,
@pub_next_range_begin,
@pub_next_range_end,
@range,
@max_used output,
@range_begin output,
@range_end output
if @@error<>0 or @retcode<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
--select @max_used=@max_used + abs(@ident_increment)
exec @retcode=sys.sp_MSallocate_one_idrange
@pub_range_begin,
@pub_range_end,
@pub_next_range_begin,
@pub_next_range_end,
@range,
@max_used output,
@next_range_begin output,
@next_range_end output
if @@error<>0 or @retcode<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
if @ident_increment < 0
begin
select @max_used = -1*@max_used
select @range_begin = -1*@range_begin
select @range_end = -1*@range_end
select @next_range_begin = -1*@next_range_begin
select @next_range_end = -1*@next_range_end
end
-- update the max_used for the republisher's identity range
update dbo.MSmerge_identity_range set max_used = @max_used where subid=@subid and artid=@artid and is_pub_range=1
if @@error <> 0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
-- now we are done. The caller of this proc is responsible for updating dbo.MSmerge_identity_range for the subscriber
-- for which this new range was allocated.
end
end
commit tran
return 0
FAILURE:
if @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION MSgetnewidrange
COMMIT TRANSACTION
end
