Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSreseed

  No additional text.


Syntax
create procedure sys.sp_MSreseed
(
    @objid              int,
    @next_seed          bigint,
    @range              bigint,
    @is_publisher       int = 0,
    @check_only         bit = 0,
    @drop_only          bit = 0,
    @initial_setting    bit = 0,
    @bound_value        bigint = 0
)
AS
begin
    declare @min_value          bigint
    declare @max_value          bigint
    declare @constraintname     sysname
    declare @artid              uniqueidentifier
    declare @min_str            nvarchar(20)
    declare @identity_column    sysname
    declare @colid              int
    declare @colname            sysname
    declare @max_str            nvarchar(20)
    declare @owner              sysname
    declare @tablename          sysname
    declare @qualname           nvarchar(517)
    declare @dbname             sysname
    declare @prefix             sysname
    declare @retcode            int
	declare @cmd				nvarchar(2000)

    SET NOCOUNT ON

    -- DDL trigger depends on this prefix, if changed, be sure to change sp_MSTran_altertable as well

    select @prefix = 'repl_identity_range_'
    select @colid=1
    select @dbname=db_name()
    select @colname=name from sys.columns where column_id=@colid and object_id = @objid
    select @retcode = 0

    -- Security check: dbo & sysadmin only
    execute @retcode = sys.sp_MSreplcheck_subscribe
    if @@error <> 0 or @retcode <> 0
    begin
        return 1
    end

    select @colname = NULL
    select @colname=name, @colid=column_id from sys.columns where object_id = @objid and is_identity=1
    if @colname is NULL
    begin
        select @tablename = object_name(@objid)
        raiserror(21756, 16, 1, @tablename)
        return (1)
    end
    if @range > 0
    begin
        select @min_value = @next_seed
        if @initial_setting = 1
            select @min_value = @bound_value - 1 -- allow for race condition with ident_current.
        -- @next_seed + @range can be used by this db, but not other db.
        -- 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.
        -- select @max_value = @next_seed + @range + 1
        select @max_value = @next_seed + @range
    end
    else
    begin
        select @max_value = @next_seed
        if @initial_setting = 1
            select @max_value = @bound_value + 1 -- allow for race condition with ident_current.
        -- @next_seed + @range can be used by this db, but not other db.
        -- 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.
        -- select @min_value = @next_seed + @range - 1
        select @min_value = @next_seed + @range
    end

    select @min_str = convert(nvarchar, @min_value)
    select @max_str = convert(nvarchar, @max_value)
    select @owner = schema_name(schema_id) from sys.objects where object_id = @objid
    select @tablename = object_name(@objid)
    select @qualname = QUOTENAME(@owner) + '.' + QUOTENAME(@tablename)

    if @check_only = 0
    begin
        DBCC CHECKIDENT(@qualname, RESEED, @next_seed) with no_infomsgs
        if @@ERROR<>0
            return (1)
        -- ident_current is NULL the server will start inserting from next_seed. However if ident_current
        -- is not null the server will insert from next_seed+ident_increment. However our constraint
        -- always says ">next_seed". To keep the constrain the same and range values consistent
        -- if ident_current is NULL here we need to reseed to next_seed + ident_increment.
        if exists (select 1 from sys.identity_columns where object_id=@objid and last_value is NULL)
        begin
            declare @temp_next_seed bigint
            select @temp_next_seed = @next_seed + IDENT_INCR(@qualname)
            DBCC CHECKIDENT(@qualname, RESEED, @temp_next_seed) with no_infomsgs
            if @@ERROR<>0
                return (1)
        end
    end

    if @is_publisher < 0
    begin
        -- Used by tran
        -- Publisher side constraint maybe transfered to subscriber.
        -- If so, it will be dropped with code below.
        select @constraintname = NULL
        select @constraintname = name from sys.objects where name like @prefix + 'tran_[0-9]%' and
                    type='C' and
                    parent_object_id = @objid
        if @constraintname is not null
        begin
                -- signal to db ddl trigger to bail out
                EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=1
                IF @@ERROR <> 0 or @retcode <> 0
                    RETURN(1)

                select @cmd = 'alter table '+ @qualname + ' drop constraint ' + quotename(@constraintname)
				exec(@cmd)
                if @@ERROR<>0
                    goto err_clean

                EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0
                IF @@ERROR <> 0 or @retcode <> 0
                    RETURN(1)
        end
        -- Prepare the name of the constraint we are going to create
        select @constraintname = @prefix + 'tran_' + convert(nvarchar(10), @objid)
        while (1=1)
            begin
            if exists (select * from sys.objects where name = @constraintname)
            begin
                -- A constraint already exists in the database with same name
                -- Change the name
                select @constraintname = @constraintname + convert(nvarchar(10), @objid)
            end
            else
            begin
                -- We can use the current constraint name to create the constraint
                break
            end
        end
    end
    else
    begin
        -- Used by merge
        -- turn on context bit so DDL event will not be replicated
        EXEC @retcode = sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=1
        IF @@ERROR <> 0 or @retcode <> 0
            RETURN(1)

        declare @prefixlike sysname

        select @prefixlike = @prefix + '%'

        -- drop all identity range constraints which contain the repl_identity_range prefix
        select @constraintname = NULL
        select top 1 @constraintname = name from sys.objects where parent_object_id=@objid and type='C' and name like @prefixlike
        while @constraintname is not NULL
        begin
            select @cmd = 'alter table '+ @qualname + ' drop constraint ' + quotename(@constraintname)
			exec(@cmd)
            if @@ERROR<>0
                goto err_clean
            select @constraintname = NULL
            select top 1 @constraintname = name from sys.objects where parent_object_id=@objid and type='C' and name like @prefixlike
        end

        select @artid=artid from dbo.sysmergearticles where objid=@objid
        if @is_publisher=1
            select @constraintname = @prefix + 'pub_' + convert(nvarchar(36), @artid)
        else
        if @is_publisher = 2
            select @constraintname = @prefix + 'repub_' + convert(nvarchar(36), @artid)
        else
            select @constraintname = @prefix + 'sub_' + convert(nvarchar(36), @artid)
        select @constraintname = REPLACE(@constraintname, '-', '_')
    end

    -- Don't add new constraint if only drop is needed.
    if @drop_only = 1
        goto success_clean

    declare @qualcolname nvarchar(258)
    select @qualcolname = quotename(@colname)

    -- signal to db ddl trigger to bail out
    EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=1
    IF @@ERROR <> 0 or @retcode <> 0
        goto err_clean

    select @cmd = 'alter table '+ @qualname + ' with NOCHECK add CONSTRAINT ' + quotename(@constraintname) + ' check NOT FOR REPLICATION ( ' + @qualcolname + ' > '
            + @min_str + ' and ' + @qualcolname + ' < ' + @max_str + ')'
	exec(@cmd)
    if @@ERROR<>0
        goto err_clean
    goto success_clean

success_clean:
    EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
    EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0
    return 0

err_clean:
    EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
    EXEC @retcode = sys.sp_MSsetcontext_bypasswholeddleventbit @onoff=0
    return 1
end

 
Last revision 2008RTM
See also

  sp_MScheckidentityrange (Procedure)
sp_MScreatelightweightprocstriggersconstraints (Procedure)
sp_MSpub_adjust_identity (Procedure)
sp_MSrepl_droparticle (Procedure)
sp_MSsub_set_identity (Procedure)
sp_subscription_cleanup (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