Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSallocate_new_identity_range

  No additional text.


Syntax
create procedure sys.sp_MSallocate_new_identity_range
    @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) = NULL output,
    @range_end numeric(38,0) = NULL output,
    @next_range_begin numeric(38,0) = NULL output,
    @next_range_end numeric(38,0) = NULL output,

    -- the following parameters are only used for logging the identity range
    -- allocation on the distributor. These are needed because in case of an
    -- anonymous or a pull subscription when this proc is called for the first time
    -- the publisher has no record of the subid above in sysmergesubscriptions
    -- the reconciler passes these in only when an initial range is being allocated
    @publication sysname = NULL,
    @subscriber sysname = NULL,
    @subscriber_db sysname = NULL
as
    declare @max_used numeric(38,0)
    declare @is_pub_range bit
    declare @range bigint
    declare @ident_increment numeric(38,0)
    declare @retcode int
    declare @objid int
    declare @qualified_table_name nvarchar(517)
    declare @applockname nvarchar(255)
    declare @DbPrincipal sysname



    exec @retcode = sys.sp_MSrepl_PAL_rolecheck @artid=@artid
    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


    -- do the following resetting of pub's max_used to reflect the max_used only if
    -- this is a root publisher and not if it is a republisher
    if (sys.fn_MSmerge_isrepublisher(@artid)=0)
    begin
        declare @publisher_max_used numeric(38,0)
        declare @pubid uniqueidentifier
        declare @pub_ranges_needed tinyint
        declare @pub_refresh_constraint bit

        select @pubid = subid, @publisher_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
        begin
            raiserror(20663, 16, -1)
            return 1
        end
        -- also update the publisher's identity range max to reflect this subscriber's allocation
        -- only if the subscriber thinks it has a valid allocation
        if @next_range_end is not NULL
        begin
            if (@ident_increment > 0 and @next_range_end > @publisher_max_used) or
               (@ident_increment < 0 and @next_range_end < @publisher_max_used)
            begin
                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(20663, 16, -1)
                    return 1
                end
            end
        end

        -- find the local pubid and refresh the publisher's identity range allocation if this is a
        -- dbo user. This will be needed when publisher inserts are all done by non-dbo users.
        -- Code for refreshing the publisher range in the insert trigger will not be executed when
        -- run by a non-dbo user.
        -- we need this to refresh the publisher's range here in that case.
        -- Do this only when this is the root publisher and not a republisher.
        -- The republisher's range will get refreshed when the merge agent runs.
        if (is_member('db_owner') = 1)
        begin
            select @pub_ranges_needed = 0
            if IDENT_CURRENT(@qualified_table_name) is NULL
                set @pub_refresh_constraint = 0
            else
                set @pub_refresh_constraint = 1

            -- sp_MScheck_publisher_range_refresh checks if ranges_need=0/1/2 and based on that value
            -- sp_MSrefresh_publisher_idrange allocates the ranges. If two threads call sp_MScheck.. at the same time
            -- decide ranges_needed=1 followed by a serialized call to sp_MSrefresh.., we will end up losing an range
            -- and IDENT_CURRENT value will be out of the allocated range (< range_begin). Hence the need to
            -- serialize the calls to sp_MScheck.. and sp_MSrefresh..
            -- see qfe 50002854
            if exists (select * from sys.database_principals where name=N'MSmerge_PAL_role' and type = 'R')
                select @DbPrincipal = N'MSmerge_PAL_role'
            else
                select @DbPrincipal = N'db_owner'

            set @applockname= N'sp_MSallocate_new_identity_range_EX' + convert(nvarchar(11), db_id())
            -- wait for a maximum of 5 secs to get the loc
            exec @retcode= sp_getapplock @Resource= @applockname, @LockMode= N'Exclusive', @LockOwner= N'Session', @LockTimeout= 5000, @DbPrincipal = @DbPrincipal

            -- a previous snapshot has already setup the publisher range. So we will just refresh the subscriber's
            -- publisher range if needed here.
            exec @retcode = sys.sp_MScheck_publisher_range_refresh @qualified_table_name, @pubid, @artid, @pub_ranges_needed output
            if @retcode<>0 or @@error<>0
            begin
                exec sp_releaseapplock @Resource= @applockname, @LockOwner= N'Session', @DbPrincipal = @DbPrincipal
                raiserror (20689, 16, -1, @qualified_table_name)
                return (1)
            end
            if @pub_ranges_needed > 0
            begin
                exec @retcode = sys.sp_MSrefresh_publisher_idrange @qualified_table_name, @pubid, @artid, @pub_ranges_needed, @pub_refresh_constraint
                if @@error<>0 or @retcode<>0
                begin
                    exec sp_releaseapplock @Resource= @applockname, @LockOwner= N'Session', @DbPrincipal = @DbPrincipal
                    raiserror(20666, 16, -1)
                    return 1
                end
            end
            exec sp_releaseapplock @Resource= @applockname, @LockOwner= N'Session', @DbPrincipal = @DbPrincipal
        end
    end

    if not exists (select * from dbo.MSmerge_identity_range where subid=@subid and artid=@artid and is_pub_range=@is_pub_range)
    begin
        -- create an entry for this subscriber
        insert dbo.MSmerge_identity_range values (@subid, @artid, NULL, NULL, NULL, NULL, @is_pub_range, NULL)
        if @@error<>0
        begin
            raiserror(21197, 16, -1)
            return 1
        end
    end


    -- even if the range required is none, we will update the information stored on the publisher
    -- with what was passed in.
    if @ranges_needed = 0
    begin
        if @range_begin is NULL
        begin
            -- this is case when we are getting the first range for the subscriber
            if exists (select * from dbo.MSmerge_identity_range where subid=@subid and artid=@artid and is_pub_range=@is_pub_range and range_begin is not NULL)
            begin
                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 subid=@subid and artid=@artid and is_pub_range=@is_pub_range
                select @ranges_needed = 2
            end
            else
            begin
                select @ranges_needed = 2 -- we will ask for 2 ranges here since this is the first allocation for this subscriber
                exec @retcode = sys.sp_MSget_new_idrange
                                        @qualified_table_name,
                                        @artid,
                                        @range_begin output,
                                        @range_end output,
                                        @next_range_begin output,
                                        @next_range_end output,
                                        @range_type,
                                        @ranges_needed
                if @@error<>0 or @retcode<>0
                begin
                    raiserror(21197, 16, -1)
                    return 1
                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,
                    max_used = NULL
                where subid = @subid and artid = @artid and is_pub_range = @is_pub_range
                if @@error<>0
                begin
                    raiserror(21197, 16, -1)
                    return 1
                end
            end
        end
        else
        begin
            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,
                    max_used = @max_used
                where subid = @subid and artid = @artid and is_pub_range = @is_pub_range
            if @@error<>0
            begin
                raiserror(21197, 16, -1)
                return 1
            end
        end
    end
    else
    begin
        -- get a new range for the given subscriber
        exec @retcode = sys.sp_MSget_new_idrange
                                @qualified_table_name,
                                @artid,
                                @range_begin output,
                                @range_end output,
                                @next_range_begin output,
                                @next_range_end output,
                                @range_type,
                                @ranges_needed
        if @@error<>0 or @retcode<>0
        begin
            raiserror(21197, 16, -1)
            return 1
        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,
                max_used = @max_used
            where subid = @subid and artid = @artid and is_pub_range = @is_pub_range
        if @@error<>0
        begin
            raiserror(21197, 16, -1)
            return 1
        end
    end

    -- if we reached here it means that the range was successfully allocated. Call a stored procedure
    -- to log this information on the distributor. We need to add code in publisher restore from backup
    -- to pick up this information from the distributor and automatically set the max_used correctly.
    if @ranges_needed > 0
    begin
        exec @retcode = sys.sp_MSmerge_log_idrange_alloc_on_distributor
                            @subid,
                            @artid,
                            @is_pub_range,
                            @ranges_needed,
                            @range_begin,
                            @range_end,
                            @next_range_begin,
                            @next_range_end,
                            @publication,
                            @subscriber,
                            @subscriber_db
    end

    return 0


 
Last revision SQL2008SP2
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