Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_MSsetup_publisher_idrange
    @publication sysname,
    @source_table sysname,
    @source_owner sysname
    -- this proc be called in prepare merge article phase of snapshot
    -- will will setup the id range allocation for the publisher's local
    -- use of one has not already been allocated.
    -- if the publisher has a range allocated already and is using the second
    -- range we will allocate a new next range for the publisher
    declare @retcode int
    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 @identity_range numeric(38,0)
    declare @low_range_inclusive bit
    declare @qualified_name nvarchar(300)
    declare @pubid uniqueidentifier
    declare @artid uniqueidentifier
    declare @objid int
    declare @pubid_other uniqueidentifier
    declare @identity_support int

    select @low_range_inclusive = 0
    select @qualified_name = quotename(@source_owner) + '.' + quotename(@source_table)
    select @objid = object_id(@qualified_name)
    if @objid is NULL
        raiserror(14027, 16, 1, @qualified_name)
        return (1)

    select @pubid = pubid from dbo.sysmergepublications
        where name = @publication and publisher = publishingservername() and publisher_db = db_name()
    if @pubid is NULL
        raiserror (20026, 16, -1, @publication)
        return (1)

    select @artid = artid, @identity_support = identity_support, @identity_range = range from dbo.sysmergearticles where pubid=@pubid and objid = @objid
    if @artid is NULL
        RAISERROR (20027, 16, -1, @qualified_name)
        RETURN (1)

    if @identity_support <> 1
        return 0

    -- check if the current db is a republisher of the given article
    if 1 = sys.fn_MSmerge_isrepublisher(@artid)
        return 0

    -- the following could be the case if the article is published in two publications and the other publication's
    -- snapshot was run first.
    if exists (select subid from dbo.MSmerge_identity_range
                where artid = @artid and subid<>@pubid and is_pub_range=0 and (sys.fn_MSmerge_islocalpubid(subid) = 1))
        return 0

    -- the following would be case if the article is in two or more publications and the other pub's snapshot has not been run
    -- yet but the subid in MSmerge_identity_range is the other publication's pubid
    select @pubid_other = subid from dbo.MSmerge_identity_range
                where artid = @artid and is_pub_range=1 and (sys.fn_MSmerge_islocalpubid(subid) = 1) and subid <> @pubid
    if @pubid_other is not NULL
        select @pubid = @pubid_other

    -- if the following is the case something is really wrong
    if not exists(select subid from dbo.MSmerge_identity_range where subid = @pubid and artid = @artid and is_pub_range = 1)
        raiserror (20663, 16, -1, @publication)
        return (1)

    if exists (select 1 from dbo.MSmerge_identity_range where subid = @pubid and artid = @artid and is_pub_range = 0)
        declare @ranges_needed tinyint
        declare @refresh_constraint bit

        select @ranges_needed = 0
        if exists (select 1 from sys.identity_columns where object_id=@objid and last_value is NULL)
            set @refresh_constraint = 0
            set @refresh_constraint = 1

        -- a previous snapshot has already setup the publisher range. So we will just refresh the publisher's
        -- subscriber range if needed here.
        exec @retcode = sys.sp_MScheck_publisher_range_refresh @qualified_name, @pubid, @artid, @ranges_needed output
        if @retcode<>0 or @@error<>0
            raiserror (20689, 16, -1, @qualified_name)
     return (1)
        if @ranges_needed > 0
            exec sys.sp_MSrefresh_publisher_idrange @qualified_name, @pubid, @artid, @ranges_needed, @refresh_constraint
            if @@error<>0 or @retcode<>0
                raiserror(20666, 16, -1, @qualified_name)
                return 1
        -- the following statement will ensure that the ident_curr in now equal to the highest values stored in the
        -- identity column (lowest value for negative increments). This to account for incorrect reseeds
        -- IDENT_CURRENT Returns the last identity value generated for a specified table in any session and any scope.
        DBCC CHECKIDENT(@qualified_name, RESEED) with no_infomsgs

        select @max_used = max_used from dbo.MSmerge_identity_range where subid = @pubid and artid = @artid

        -- max_used below will not be null in the case of upgrade. After adding the article during the first snapshot it will be NULL
        if @max_used is NULL
            if exists (select 1 from sys.identity_columns where object_id=@objid and last_value is NULL)
                select @max_used = IDENT_SEED(@qualified_name)
                select @low_range_inclusive = 1
                select @max_used = IDENT_CURRENT(@qualified_name)

        -- addmergearticle would have already inserted the publisher identity range in MSmerge_identity_range table
        -- what we need do here is insert the publisher's subscriber range - local allocation.
        -- now insert publisher's subscriber entry that corresponds to the range allocated to the publisher
        select @range_begin = @max_used
        select @range_end = @range_begin + @identity_range
        select @next_range_begin = @range_end
        select @next_range_end = @next_range_begin + @identity_range

        insert dbo.MSmerge_identity_range(subid, artid, range_begin, range_end, next_range_begin, next_range_end, is_pub_range, max_used)
            values(@pubid, @artid, @range_begin, @range_end,  @next_range_begin, @next_range_end, 0, NULL)
        if @@error <> 0
                goto FAILURE

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

        -- call the stored procedure to reseed the table and also create the identity range check constraint
        exec @retcode = sys.sp_MSrefresh_idrange_check_constraint
        if @@ERROR<>0 or @retcode<>0
            goto FAILURE

        -- set the values about the current allocation on the distributor.
        exec @retcode = sys.sp_MSmerge_log_idrange_alloc_on_distributor
                            0, -- is_pub_range publisher's local allocation is always a sub range
                            2, -- ranges_allocated we just allocated two ranges to the publisher


    return 0

    raiserror(20690, 16, -1, @source_table)
    return 1

Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSprepare_mergearticle (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash