-- 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 asdeclare@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 beginselect@is_pub_range = 1 endelsebeginselect@is_pub_range = 0 endifnotexists (select * from dbo.MSmerge_identity_range where subid=@subidand artid=@artidand 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 neededifexists (select * from dbo.sysmergearticles where artid=@artidand (status = 5 or status = 6) and pubid in (select pubid from dbo.sysmergesubscriptions where subid=@subid)) beginreturn 0 endelsebeginRAISERROR(20671, 16, -1) return (1) endend-- 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=@subidand artid=@artidand is_pub_range=@is_pub_rangeif@is_pub_range = 0 begindeclare@ident_current numeric(38,0) select@ident_current = IDENT_CURRENT(@qualified_table_name) -- the range begin would be null if this is an attached subscriptionif@range_beginisNULLor@range_endisNULLbeginselect@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 beginif@ident_currentisNULLbegin-- 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 stepifexists (select 1 from sys.identity_columns where object_id=@objidand last_value isNULL) begindeclare@temp_range_begin numeric(38,0) select@temp_range_begin = @range_begin + @ident_incrementDBCC CHECKIDENT(@qualified_table_name, RESEED, @temp_range_begin) with no_infomsgs select@ident_current = IDENT_CURRENT(@qualified_table_name) endelsebeginDBCC CHECKIDENT(@qualified_table_name, RESEED, @range_begin) with no_infomsgs select@ident_current = IDENT_CURRENT(@qualified_table_name) endend-- 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_currentand@ident_current < @next_range_begin) or (@ident_increment < 0 and@ident_current > @next_range_beginand@range_end >= @ident_current) beginDBCC CHECKIDENT(@qualified_table_name, RESEED, @next_range_begin) with no_infomsgs select@ident_current = IDENT_CURRENT(@qualified_table_name) endselect@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_beginisNULLor@next_range_endisNULLbeginselect@next_range_begin = @range_beginselect@next_range_end = @range_endupdate dbo.MSmerge_identity_range set next_range_begin = @next_range_begin, next_range_end = @next_range_endwhere subid=@subidand artid=@artidand is_pub_range=@is_pub_rangeendendelsebegin-- the range begin would be null if this is an attached subscriptionif@range_beginisNULLor@range_endisNULLbeginselect@ranges_needed = 2 return 0 endselect@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_beginisNULLor@next_range_endisNULLbeginselect@next_range_begin = @range_beginselect@next_range_end = @range_endupdate dbo.MSmerge_identity_range set next_range_begin = @next_range_begin, next_range_end = @next_range_endwhere subid=@subidand artid=@artidand is_pub_range=@is_pub_rangeendendreturn 0

Followers

About Me

I think code should be easy to use, so if the "behind the scene" has to be complex for the user, to get an great experience, so be it.
Hence my saying: With great code, comes great complexity, so keep it simple stupid