Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrefresh_idrange_check_constraint

  No additional text.


Syntax
create procedure sys.sp_MSrefresh_idrange_check_constraint
    @qualified_object_name nvarchar(517),
    @artid uniqueidentifier,
    @range_begin numeric(38,0),
    @range_end numeric(38,0),
    @next_range_begin numeric(38,0),
    @next_range_end numeric(38,0),
    @ranges_given tinyint = 2,
    @low_range_inclusive bit = 0
as
    set nocount on

    declare @constraintname sysname
	declare @quoted_constraintname nvarchar(258)
    declare @colname		sysname
    declare @dbname			sysname
    declare @prefix			sysname
    declare @prefixlike 	sysname
	declare @object_name	sysname
	declare @schema_name	sysname
    declare @retcode        int
    declare @objid          int
	declare @dbid			int
    declare @command nvarchar(4000)
    declare @ident_incr  numeric(38,0)

    -- DDL trigger depends on this prefix, if changed, be sure to change sp_MSmerge_altertable as well
    select @prefix = 'repl_identity_range_'
    select @prefixlike = @prefix+'%'
    select @dbname = db_name()
	select @dbid = db_id()
    select @retcode = 0

    select @objid = object_id(@qualified_object_name)
    if @objid is NULL
    begin
        raiserror(14027, 16, 1, @qualified_object_name)
        return (1)
    end

	
	-- We can't fully trust the input here as it could be passed in via a user via
	--  sp_MSrefresh_publisher_idrange.  We use the @dbid parameter here as we could
	--  be resolving an object_id that's in a different database which the user doesn't
	--  have access to.
	
	select @object_name = object_name(@objid, @dbid)
	select @schema_name = object_schema_name(@objid, @dbid)
    if @object_name is NULL or @schema_name is NULL
    begin
        raiserror(14027, 16, 1, @qualified_object_name)
        return (1)
    end
	
	select @qualified_object_name = quotename(@schema_name) + N'.' + quotename(@object_name)

    select @colname = NULL
    select @colname=name from sys.columns where object_id=@objid and is_identity=1
    if @colname is NULL
    begin
        raiserror(21756, 16, 1, @qualified_object_name)
        return (1)
    end

    select @ident_incr = IDENT_INCR(@qualified_object_name)

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

    -- the first time this proc is called on the subscriber, the publisher constraint could be present
    -- hence we need to drop all 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 @quoted_constraintname = quotename(@constraintname)
        exec ('alter table '+ @qualified_object_name + ' drop constraint ' + @quoted_constraintname)
        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 @constraintname = @prefix + convert(nvarchar(36), @artid)
    select @constraintname = REPLACE(@constraintname, '-', '_')
    if exists (select * from sys.objects where name = @constraintname and type='C')
    begin
		select @quoted_constraintname = quotename(@constraintname)
        exec ('alter table '+ @qualified_object_name + ' drop constraint ' + @quoted_constraintname)
        if @@ERROR<>0
            goto err_clean
    end


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

    declare @str1 nvarchar(40), @str2 nvarchar(40), @str3 nvarchar(40), @str4 nvarchar(40)
    select @str1 = convert(nvarchar(40),@range_begin)
    select @str2 = convert(nvarchar(40),@range_end)
    select @str3 = convert(nvarchar(40),@next_range_begin)
    select @str4 = convert(nvarchar(40),@next_range_end)

    if @ident_incr > 0
    begin
        select @command = 'alter table '+ @qualified_object_name + ' with NOCHECK add CONSTRAINT ' + @constraintname + ' check NOT FOR REPLICATION ('
        if @low_range_inclusive = 1
            select @command = @command + '(' + @qualcolname + ' >= ' + @str1
        else
            select @command = @command + '(' + @qualcolname + ' > ' + @str1
        select @command = @command +
                    ' and ' + @qualcolname + ' <= ' + @str2 + ')' +
                ' or ' +
                '(' + @qualcolname + ' > ' + @str3 +
                    ' and ' + @qualcolname + ' <= ' + @str4 + ')' +
                ')'
        exec (@command)
    end
    else
    begin
        select @command = 'alter table '+ @qualified_object_name + ' with NOCHECK add CONSTRAINT ' + @constraintname + ' check NOT FOR REPLICATION ('
        if @low_range_inclusive = 1
            select @command = @command + '(' + @qualcolname + ' <= ' + @str1
        else
            select @command = @command + '(' + @qualcolname + ' < ' + @str1
        select @command = @command +
                    ' and ' + @qualcolname + ' >= ' + @str2 + ')' +
                ' or ' +
                '(' + @qualcolname + ' < ' + @str3 +
                    ' and ' + @qualcolname + ' >= ' + @str4 + ')' +
                ')'

        exec (@command)
    end
    if @@ERROR<>0
        goto err_clean

    if @ranges_given = 2 and @low_range_inclusive = 0
    begin
        DBCC CHECKIDENT(@qualified_object_name, RESEED, @range_begin) with no_infomsgs
        if @@ERROR<>0
            goto err_clean

        -- If this is the first merge the subscriber would not have had any local inserts when this proc is called.
        -- In that case the subscriber's ident current will be NULL since the table was created by applying the snapshot
        -- to the subscriber. Hence if we reseed to @range_begin the next value that will be inserted will be
        -- equal to @range_begin (this is special case of NULL). However if there have been local inserts the next value
        -- inserted will be equal to @range_begin+@ident_incr. Hence to keep the same behavior for initial and subsequent
        -- reseeds we will reseed it to @range_begin + @ident_incr in case of a NULL value of ident_current
        if exists (select 1 from sys.identity_columns where object_id=@objid and last_value is NULL)
        begin
            select @range_begin = @range_begin + @ident_incr
            DBCC CHECKIDENT(@qualified_object_name, RESEED, @range_begin) with no_infomsgs
            if @@ERROR<>0
                goto err_clean
        end
    end

    goto success_clean

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

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

 
Last revision SQL2008SP2
See also

  sp_MSadjustmergeidentity (Procedure)
sp_MScreate_zero_identity_constraint (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MSmerge_altertable (Procedure)
sp_MSrefresh_publisher_idrange (Procedure)
sp_MSsetup_identity_range (Procedure)
sp_MSsetup_publisher_idrange (Procedure)
sp_MSset_new_identity_range (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