May 14, 2012

sp_MSfetchidentityrange (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_MSfetchidentityrange(nvarchar @tablename
, bit @adjust_only
, nvarchar @table_owner)

MetaData:

 create procedure sys.sp_MSfetchidentityrange   
@tablename nvarchar(270),
@adjust_only bit,
@table_owner sysname = NULL
AS

declare @retcode int
declare @objid int
declare @artid uniqueidentifier
declare @pubid uniqueidentifier
declare @next_seed bigint
declare @range bigint
declare @identity_support int
declare @tablenick int
declare @quoted_tablename nvarchar(270)
declare @is_republisher bit
declare @ident_current bigint
declare @ident_increment bigint
declare @range_begin numeric(38,0)
declare @range_end numeric(38,0)
declare @next_range_begin numeric(38,0)
declare @next_range_end numeric(38,0)
declare @max_used numeric(38,0)
declare @threshold int

if @table_owner is not NULL
select @quoted_tablename = QUOTENAME(@table_owner) + '.' + QUOTENAME(@tablename)
else
select @quoted_tablename = quotename(@tablename)

select @objid = object_id(@quoted_tablename)

select @identity_support=identity_support, @tablenick = nickname, @artid=artid, @range=range, @threshold=threshold
from dbo.sysmergearticles where objid=@objid

if @identity_support is NULL or @identity_support=0
begin
-- table is not enabled for auto identity range management
raiserror(21197, 16, -1)
return (1)
end

--
-- do permission checking
--
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @tablenick=@tablenick
if @retcode<>0 or @@ERROR<>0 return (1)

-- check if this is a republisher.
if exists (select pubid from dbo.sysmergearticles where artid=@artid and sys.fn_MSmerge_islocalpubid(pubid)=0)
select @is_republisher=1
else
select @is_republisher=0

select @pubid=subid from dbo.MSmerge_identity_range where artid=@artid and is_pub_range=1 and (sys.fn_MSmerge_islocalsubid(subid)=1)
if @pubid is NULL
begin
raiserror(20663, 16, -1)
return (1)
end


-- get new identity. Now we do not know who the subscriber is. So it we cannot keep track
-- of this subscriber. However we will allocate a new range and update the publisher's entry
-- which indicates how much has been allocated.
-- we will allocate a new identity irrespective of what @adjust_only has been set to. This is
-- because the merge agent always calls mostly with @adjust_only being set to true and in the
-- one case that it is set to false it should really be true.
begin tran
save tran fetchidentityrange

select @ident_current = ISNULL(IDENT_CURRENT(@quoted_tablename), IDENT_SEED(@quoted_tablename))
select @ident_increment = IDENT_INCR(@quoted_tablename)

select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end,
@next_seed = max_used
from dbo.MSmerge_identity_range with (updlock, rowlock) where artid=@artid and subid=@pubid and is_pub_range=1
if @range_begin is NULL or @range_end is NULL or @next_seed is NULL
begin
raiserror(21197, 16, -1)
goto FAILURE
end

-- add one or subscract one from max_used for backward compatibility. This means between ranges we will always skip
-- one number. That is fine if not Daytona will have overlapping ranges. Though SQL downlevel subscribers will be fine
-- Daytona will have overlapping ranges and hence this increment.
select @next_seed = @next_seed + @ident_increment

-- the following is fine even in case of negative increments since the @range value is negative
if @is_republisher=0
begin
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
else
begin
if @ident_increment>0
begin
if @range_end >= @next_seed and @range_begin <= @next_seed
begin
if (@next_seed+@range) <= @range_end
begin
-- there is enough space in the first range
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
else
begin
-- we need to start using the second range
select @next_seed = @next_range_begin
if @next_range_begin is NULL
begin
raiserror(21197, 16, -1)
goto FAILURE
end
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
end
else if @next_range_end >= @next_seed and @next_range_begin <= @next_seed
begin
if (@next_seed+@range) <= @next_range_end
begin
-- there is enough space in the second range
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
else
begin
-- there is not enough range at the republisher to allocate for it subscriber
raiserror(20665, 16, -1)
goto FAILURE
end
end
else
begin
-- there is something terribly wrong here. @max_used is not in the ranges available at the publisher
raiserror(21197, 16, -1)
goto FAILURE
end
end
else
begin
if @range_end <= @next_seed and @range_begin >= @next_seed
begin
if (@next_seed+@range) >= @range_end
begin
-- there is enough space in the first range
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
else
begin
-- we need to start using the second range
select @next_seed = @next_range_begin
if @next_range_begin is NULL
begin
raiserror(21197, 16, -1)
goto FAILURE
end
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
end
else if @next_range_end <= @next_seed and @next_range_begin >= @next_seed
begin
if (@next_seed+@range) >= @next_range_end
begin
-- there is enough space in the second range
update dbo.MSmerge_identity_range set max_used = @next_seed+@range where artid=@artid and subid=@pubid and is_pub_range=1
if @@error<>0
begin
raiserror(21197, 16, -1)
goto FAILURE
end
end
else
begin
-- there is not enough range at the republisher to allocate for it subscriber
raiserror(20665, 16, -1)
goto FAILURE
end
end
else
begin
-- there is something terribly wrong here. @max_used is not in the ranges available at the publisher
raiserror(21197, 16, -1)
goto FAILURE
end
end
end
commit tran

select @range_begin = @next_seed, @range_end = @next_seed+@range

if @is_republisher=0
begin
declare @publication sysname
select @publication = name from dbo.sysmergepublications where pubid = @pubid

-- set the values about the current allocation on the distributor.
exec @retcode = sys.sp_MSmerge_log_idrange_alloc_on_distributor
NULL,
@artid,
0, -- is_pub_range 0 because we just allocated to a subscriber
1, -- we allocated only one range since this a backward compat thing
@range_begin,
@range_end,
NULL, -- next_range_begin is NULL because we are allocating only one range to a downlevel subscriber
NULL, -- next_range_end is NULL because we are allocating only one range to a downlevel subscriber
@publication,
N'',
N''
end


-- initialize article collection for agents.
select @identity_support, @next_seed, @range, @threshold

return 0
FAILURE:
if @@trancount>0
begin
rollback tran fetchidentityrange
commit tran
end
return 1

No comments:

Post a Comment

Total Pageviews