Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadjustmergeidentity

  No additional text.


Syntax
/*
** When calling from the wrapper stored procedure, we make sure that there is one and
** only one of the two input SP is NULL. Therefore, we do no checking here.
*/
create procedure sys.sp_MSadjustmergeidentity
@publication		sysname = NULL,
@qualified_table_name	nvarchar(300) = NULL
AS

declare @db_name sysname
declare @pubid uniqueidentifier
declare @pub_identity_range bigint
declare @objid int
declare @retcode int
declare @identity_range bigint
declare @identity_support int
declare @artid uniqueidentifier
declare @ident_current numeric(38,0)
declare @ident_increment numeric(38,0)
declare @tablelevel	bit
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 @max_range numeric(38,0)
declare @ranges_to_be_reset int
declare @raise_warning bit

select @db_name=db_name()
select @pubid = newid()
select @objid = 0

if (@publication is NULL and @qualified_table_name is NULL) or
    (@publication is not NULL and @qualified_table_name is not NULL)
begin
    raiserror('internal error on nullability', 16, -1)
    return (1)
end

if @qualified_table_name is not NULL
begin
    select @tablelevel = 1
    select @objid = object_id(@qualified_table_name)
end
else
begin
    select @tablelevel = 0
    select @pubid = pubid from dbo.sysmergepublications
        where name = @publication  and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=@db_name
end

select @raise_warning = 1

if exists (select * from dbo.sysmergearticles where identity_support<>0 and
    ((@tablelevel=0 and pubid=@pubid) or (@tablelevel=1 and objid=@objid)))
begin
    declare one_article CURSOR LOCAL FAST_FORWARD FOR
        select DISTINCT artid from dbo.sysmergearticles where identity_support<>0 and
            ((@tablelevel=0 and pubid=@pubid) or (@tablelevel=1 and objid=@objid))
    open one_article
    fetch one_article into @artid
    while (@@fetch_status<>-1)
    begin
        -- check if this is a re-publisher. If republisher cannot use this proc to adjustmergeidentity
        -- need to merge with the root publisher to adjust the identity range.
        if exists (select pubid from dbo.sysmergearticles where artid=@artid and sys.fn_MSmerge_islocalpubid(pubid)=0)
        begin
            if @raise_warning = 1
            begin
                raiserror(20679, 11, -1)
                select @raise_warning = 0
            end
            goto NEXT_ARTICLE
        end

        select top 1 @objid=objid from dbo.sysmergearticles where artid=@artid

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

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

        -- now check how much of the identity allocated has been used by the publisher
        -- if this article is published in multiple publications the pubid we must use
        -- give by the following query
        if @tablelevel=1
        begin
            select @pubid = NULL
            select @max_used = NULL
            select @pubid = subid, @max_used = max_used from dbo.MSmerge_identity_range
                where artid=@artid and is_pub_range=1 and (sys.fn_MSmerge_islocalpubid(subid)=1)
            if @pubid is NULL or @max_used is NULL
            begin
                raiserror(20663, 16, -1)
                return (1)
            end
        end
        else
        begin
            select @max_used=NULL
            select @max_used = max_used from dbo.MSmerge_identity_range where artid=@artid and subid=@pubid and is_pub_range=1
            if @max_used is NULL
            begin
                raiserror(20663, 16, -1)
                return (1)
            end
        end

        -- begin transaction in which identity range will be updated
        begin tran
        save tran sp_MSadjustpubidentity

        select @pub_identity_range=pub_range, @identity_range=range from dbo.sysmergearticles where artid=@artid and pubid=@pubid

        -- get the max_used value again within the transaction
        select @max_used = max_used, @max_range=range_end from dbo.MSmerge_identity_range with (updlock, rowlock)
            where artid=@artid and subid=@pubid and is_pub_range=1

        -- if the following is true, something is definitely wrong. Basically max_used should have been
        -- updated everytime a new range is allocated. So just reset the max_used correctly.
        if ((@ident_increment > 0) and (@ident_current > @max_used)) or
           ((@ident_increment < 0) and (@ident_current < @max_used))
        begin
            --raiserror(20659, 11, -1, @qualified_table_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

        select @range_begin = range_begin,
               @range_end = range_end,
               @next_range_begin = next_range_begin,
               @next_range_end = next_range_end
            from dbo.MSmerge_identity_range where artid=@artid and subid=@pubid and is_pub_range=0

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

        -- check which range is in use and which needs to be reset.
        if @ident_increment > 0
        begin
            if @ident_current >= @next_range_end
                -- both ranges need to be reset.
                select @ranges_to_be_reset = 2
            else if @ident_current >= @range_end
                -- one range needs to be reset
                select @ranges_to_be_reset = 1
            else
                -- no reset required
                select @ranges_to_be_reset = 0
        end
        else
        begin
            if @ident_current <= @next_range_end
                -- both ranges need to be reset.
                select @ranges_to_be_reset = 2
            else if @ident_current <= @range_end
                -- one range needs to be reset
                select @ranges_to_be_reset = 1
            else
                -- no reset required
                select @ranges_to_be_reset = 0
        end


        if @ranges_to_be_reset = 0
        begin
            commit tran
            goto NEXT_ARTICLE
        end

        if @ranges_to_be_reset = 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 + @identity_range -- in yukon use sub range for publisher's local allocation
        end
        if @ranges_to_be_reset = 2
        begin
            select @range_begin = @max_used
            select @range_end = @range_begin + @identity_range -- in yukon use sub range for publisher's local allocation
            select @next_range_begin = @range_end
            select @next_range_end = @next_range_begin + @identity_range -- in yukon use sub range for publisher's local allocation
        end

        if ((@ident_increment > 0) and (@next_range_end > @max_range)) or
           ((@ident_increment < 0) and (@next_range_end < @max_range))
        begin
            raiserror(20664, 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

        -- call the stored procedure to reseed the table and also create the identity range check constraint
        exec @retcode = sys.sp_MSrefresh_idrange_check_constraint
                                @qualified_table_name,
                                @artid,
                                @range_begin,
                                @range_end,
                                @next_range_begin,
                                @next_range_end,
                                @ranges_to_be_reset
        if @@ERROR<>0 or @retcode<>0
        begin
            raiserror(21197, 16, -1)
            goto FAILURE
        end

        update dbo.MSmerge_identity_range
            set range_begin = @range_begin,
                range_end = @range_end,
                next_range_begin = @next_range_begin,
                next_range_end = @next_range_end
            where subid=@pubid and artid=@artid and is_pub_range=0
        if @@ERROR<>0
        begin
            raiserror(21197, 16, -1)
            goto FAILURE
        end

        -- commit identity range transaction
        commit tran

        -- set the values about the current allocation on the distributor.
        if @ranges_to_be_reset > 0
        begin
            exec @retcode = sys.sp_MSmerge_log_idrange_alloc_on_distributor
                                @pubid,
                                @artid,
                                0, -- is_pub_range publisher's local allocation is always a sub range
                                @ranges_to_be_reset,
                                @range_begin,
                                @range_end,
                                @next_range_begin,
                                @next_range_end
        end

NEXT_ARTICLE:
        fetch next from one_article into @artid
    end
end
else
begin
    raiserror(21295, 16, -1, @publication)
    return (1)
end

return 0

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

 
Last revision 2008RTM
See also

  sp_adjustpublisheridentityrange (Procedure)
sp_MSdrop_rladmin (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