Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSpub_adjust_identity

  No additional text.


Syntax
create procedure sys.sp_MSpub_adjust_identity
(
    @artid int = null
    ,@max_identity bigint = null -- used only during initialization
)
as
begin
    set nocount on

    declare @retcode int
            ,@cmd nvarchar(1000)
            ,@objid int, @threshhold int
            ,@pub_range bigint, @next_seed bigint, @current_pub_range bigint
            ,@last_seed bigint, @identity_so_far bigint, @threshold int, @range bigint
            ,@database sysname, @table_name sysname
            ,@qualname nvarchar(517)
            ,@distributor sysname
            ,@distribdb sysname
            ,@publishingservername sysname

    
    -- Security Check.
    
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    select @database = db_name()
			,@publishingservername = publishingservername()
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
                                       @distribdb = @distribdb OUTPUT
    IF @@ERROR <> 0
    BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

    DECLARE adjust_identity CURSOR LOCAL FAST_FORWARD FOR
        SELECT art1.objid
          FROM sysarticles art1,
               sysarticleupdates art2
          where art1.artid = art2.artid and
                art2.identity_support = 1 and
                (art1.artid = @artid or @artid is null)
    FOR READ ONLY

    OPEN adjust_identity
    FETCH adjust_identity INTO @objid
    WHILE (@@fetch_status <> -1)
    begin
        select @table_name = object_name(@objid)
        exec @retcode = sys.sp_MSget_qualified_name @objid, @qualname OUTPUT

        select @range = range, @pub_range = pub_range, @current_pub_range = current_pub_range,
            @last_seed = last_seed,
            @threshold = threshold from
            MSpub_identity_range where objid=@objid

        select @identity_so_far = isnull(ident_current(@qualname), ident_seed(@qualname))

        if @last_seed is null
        begin
            -- First time
            select @last_seed = (@identity_so_far / @pub_range) * @pub_range
            -- We always reserve a new range for the publisher without reseeding
            -- the publisher, and we guarantee to have more slots then
            -- a full range initially for the publisher.
            if  (@pub_range > 0 and @last_seed < @identity_so_far) or
                (@pub_range < 0 and @last_seed > @identity_so_far)
                select @last_seed = @last_seed + @pub_range

            select @next_seed = @last_seed + @pub_range
            -- Initialize distribution side entry
            SELECT @cmd = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSinsert_identity'
            EXEC @retcode = @cmd
                @publisher = @publishingservername,
                @publisher_db = @database,
                @tablename = @table_name,
                @identity_support = 1,
                @pub_identity_range = 0, -- We don't need this at the distributor
                @identity_range = @range,
                @threshold  = @threshold,
                -- Make sure we don't have gap at the beginning
                @next_seed  = @next_seed,
                @max_identity = @max_identity
            IF @@ERROR <> 0 OR @retcode <> 0
                GOTO UNDO
            -- Add constraint only without reseeding.
            exec @retcode = sys.sp_MSreseed
                @objid = @objid,
                @next_seed = @last_seed,
                @range = @pub_range,
                @is_publisher = -1,
                @check_only = 1,
                @initial_setting = 1,
                @bound_value = @identity_so_far

            IF @@ERROR <> 0 OR @retcode <> 0
                GOTO UNDO
            update MSpub_identity_range set last_seed = @last_seed where objid = @objid
            IF @@ERROR <> 0
                GOTO UNDO
        end
        else
        begin

            -- Leave one slot unused. This is to prevent violation of primary key constraint
            -- if the next value is used by a subscriber and the publisher has received it.
            -- It seems the pk constraint will be validated before this check.
            declare @actual_range int
            if @current_pub_range > 0
                select @actual_range = @current_pub_range -1
            else
                select @actual_range = @current_pub_range +1

            -- Calculate the current ratio
            if 100*(@identity_so_far - @last_seed)/@actual_range >= @threshold
            -- need bump up
            begin
                SELECT @cmd = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSfetchAdjustidentityrange'
                EXEC @retcode = @cmd
                    @publisher = @publishingservername,
                    @publisher_db = @database,
                    @tablename = @table_name,
                    @adjust_only = 1,
                    @for_publisher = 1,
                    @range = @pub_range,
                    @next_seed = @next_seed output
                IF @@ERROR <> 0 OR @retcode <> 0
                    GOTO UNDO

                select @last_seed = @next_seed - @pub_range

                update MSpub_identity_range set last_seed = @last_seed,
                    current_pub_range = @pub_range
                    where objid = @objid
                IF @@ERROR <> 0
                    GOTO UNDO
                -- RESEED and change constraint
                exec @retcode = sys.sp_MSreseed
                    @objid = @objid,
                    @next_seed = @last_seed,
                    @range = @pub_range,
                    @is_publisher = -1
                IF @@ERROR <> 0 OR @retcode <> 0
                    GOTO UNDO
            end
        end

        FETCH adjust_identity INTO @objid
    end
    return 0
UNDO:
    -- No need to start a transaction.
    return 1
end

 
Last revision SQL2008SP2
See also

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