May 14, 2012

sp_MSget_identity_range_info (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_MSget_identity_range_info(uniqueidentifier @subid
, uniqueidentifier @artid
, tinyint @range_type)

MetaData:

 --  this stored procedure should be called on the subscriber to check if the  
-- subscriber needs a new identity range
create procedure sys.sp_MSget_identity_range_info
@subid uniqueidentifier,
@artid uniqueidentifier,
@range_type tinyint, -- 1=publisher range, 2=subscriber range
@ranges_needed tinyint output, -- 0=none needed, 1=one range needed, 2=both ranges needed
@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
as
declare @max_used numeric(38,0)
declare @ident_increment numeric(38,0)
declare @retcode int
declare @is_pub_range bit
declare @objid int
declare @qualified_table_name nvarchar(517)

exec @retcode = sys.sp_MSreplcheck_subscribe
if (@retcode <> 0) or (@@error <> 0)
return 1

exec @retcode = sys.sp_MScheck_article_auto_identity @artid, @objid output, @qualified_table_name output
if @retcode<>0 or @@error<>0
return 1

select @ident_increment = IDENT_INCR(@qualified_table_name)

if @range_type = 1
begin
select @is_pub_range = 1
end
else
begin
select @is_pub_range = 0
end

if not exists (select * from dbo.MSmerge_identity_range where subid=@subid and artid=@artid and is_pub_range=@is_pub_range)
begin
-- we did not find the entry to get information about it. The entry should be present
-- when this proc is called. sp_MSsetup_identity_range proc called during the initial
-- merge should have created the entry.

-- however if this is an incrementally added article return information saying that both ranges are needed
if exists (select * from dbo.sysmergearticles
where artid=@artid and (status = 5 or status = 6) and
pubid in (select pubid from dbo.sysmergesubscriptions where subid=@subid))
begin
return 0
end
else
begin
RAISERROR(20671, 16, -1)
return (1)
end
end

-- now check how much of the range has been used.
select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end,
@max_used = max_used
from dbo.MSmerge_identity_range where subid=@subid and artid=@artid and is_pub_range=@is_pub_range
if @is_pub_range = 0
begin
declare @ident_current numeric(38,0)
select @ident_current = IDENT_CURRENT(@qualified_table_name)

-- the range begin would be null if this is an attached subscription
if @range_begin is NULL or @range_end is NULL
begin
select @ranges_needed = 2
return 0
end

-- since range_begin above was not null it means that we have atleast one valid range in our
-- idrange metadata tables. Now if ident_curent is NULL then it means something is wrong.
-- I have noticed that this is the case after upgrade from shiloh. To guard against possible
-- shiloh bugs we will reseed the talbe to the first range begin
if @ident_current is NULL
begin
-- if last_value is still null it means that server is going to start inserting from range_begin
-- but we really want the server to start inserting from range_begin + ident_increment. Hence
-- we need to do this extra step
if exists (select 1 from sys.identity_columns where object_id=@objid and last_value is NULL)
begin
declare @temp_range_begin numeric(38,0)
select @temp_range_begin = @range_begin + @ident_increment
DBCC CHECKIDENT(@qualified_table_name, RESEED, @temp_range_begin) with no_infomsgs
select @ident_current = IDENT_CURRENT(@qualified_table_name)
end
else
begin
DBCC CHECKIDENT(@qualified_table_name, RESEED, @range_begin) with no_infomsgs
select @ident_current = IDENT_CURRENT(@qualified_table_name)
end
end

-- here it is possible that on the subscriber the inserts were all done by non-dbo users
-- in that case code that advances to using the next range in the insert trigger would not have got executed
-- since we know that this proc was called by a user who is an admin on the subscriber, check
-- here if we need to start using the second range and if so do the dbcc checkident
-- the id range check constraint refresh would have done a dbcc reseed only if both ranges
-- are being refreshed. However we can be in a situation when (for positive increment)
-- range_end <= ident_current < next_range_begin. In that case we need to do a reseed.
if (@ident_increment > 0 and @range_end <= @ident_current and @ident_current < @next_range_begin) or
(@ident_increment < 0 and @ident_current > @next_range_begin and @range_end >= @ident_current)
begin
DBCC CHECKIDENT(@qualified_table_name, RESEED, @next_range_begin) with no_infomsgs
select @ident_current = IDENT_CURRENT(@qualified_table_name)
end

select @ranges_needed = sys.fn_MSMerge_get_ranges_needed(
@ident_increment,
@ident_current,
@range_begin,
@range_end,
@next_range_begin,
@next_range_end)

-- next range begin would be NULL if this is an upgrade. In Shiloh we only allocated
-- one range. We get that one range and put in in the range_begin and range_end values on
-- upgrade. hence next range begin and end will be NULL after upgrade. So if this is
-- the case we should request for atleast one range.
if @next_range_begin is NULL or @next_range_end is NULL
begin
select @next_range_begin = @range_begin
select @next_range_end = @range_end
update dbo.MSmerge_identity_range
set next_range_begin = @next_range_begin,
next_range_end = @next_range_end
where subid=@subid and artid=@artid and is_pub_range=@is_pub_range
end
end
else
begin
-- the range begin would be null if this is an attached subscription
if @range_begin is NULL or @range_end is NULL
begin
select @ranges_needed = 2
return 0
end

select @ranges_needed = sys.fn_MSMerge_get_ranges_needed(
@ident_increment,
@max_used,
@range_begin,
@range_end,
@next_range_begin,
@next_range_end)

-- next range begin would be NULL if this is an upgrade. In Shiloh we only allocated
-- one range. hence next range begin and end will be NULL after upgrade. So if this is
-- the case we should request for atleast one range.
if @next_range_begin is NULL or @next_range_end is NULL
begin
select @next_range_begin = @range_begin
select @next_range_end = @range_end
update dbo.MSmerge_identity_range
set next_range_begin = @next_range_begin,
next_range_end = @next_range_end
where subid=@subid and artid=@artid and is_pub_range=@is_pub_range
end
end

return 0

No comments:

Post a Comment

Total Pageviews