Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSget_identity_range_info

  No additional text.


Syntax
-- 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


 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash