Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSremoveidrangesupport

  No additional text.


Syntax
create procedure sys.sp_MSremoveidrangesupport
    (@pubid uniqueidentifier, @artid uniqueidentifier, @propagate_ddl_change bit = 0)
as
    set nocount on
    declare @retcode        smallint
    declare @source_table   nvarchar(517)
    declare @ownername      sysname
    declare @objectname     sysname
    declare @objid          int

    select @objid = max(objid) from dbo.sysmergearticles where artid = @artid
    if @objid is NULL
        return 0

    -- get owner name, and table name
    select @objectname = name, @ownername = schema_name(schema_id)
        from sys.objects where object_id = @objid
    -- construct the qualified table name
    select @source_table = QUOTENAME(@ownername) + '.' + QUOTENAME(@objectname)

    -- no need to check if the other publication also had id range enabled, as it is required
    -- that all publications publishing the same article, should have the same id range
    -- management option

    if exists (select * from dbo.sysmergearticles where artid=@artid and pubid<>@pubid)
    begin
        if exists (select * from MSmerge_identity_range where artid=@artid and subid=@pubid)
        begin
            declare @pubid_other uniqueidentifier
            select top 1 @pubid_other=pubid from dbo.sysmergearticles where artid=@artid and pubid<>@pubid
            if @pubid_other is NULL
                return (1)
            update dbo.MSmerge_identity_range set subid=@pubid_other where subid=@pubid and artid=@artid
            if @@ERROR<>0
                return (1)
            delete from dbo.MSmerge_identity_range where artid=@artid and subid in (select subid from dbo.sysmergesubscriptions where pubid=@pubid)
            if @@ERROR<>0
                return (1)
        end
    end
    else
    begin
        declare @constraintname nvarchar(258)
        select @constraintname = 'repl_identity_range_%' + convert(nvarchar(36), @artid)
        select @constraintname = REPLACE(@constraintname, '-', '_')
        if exists (select * from sys.objects where name like @constraintname and type='C')
        begin
            select @constraintname = quotename(name) from sys.objects where name like @constraintname and type ='C'
            -- turn on context bit if needed so DDL event will not be replicated
            EXEC @retcode = sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=1
            IF @@ERROR <> 0 or @retcode <> 0
                RETURN(1)

            exec ('alter table '+ @source_table + ' drop constraint ' + @constraintname)
            if @@ERROR<>0
            begin
                EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
                return (1)
            end
            EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
            IF @@ERROR <> 0 or @retcode <> 0
                RETURN(1)

            if @propagate_ddl_change = 1
            begin
                declare @schematext           nvarchar(4000)
                declare @schemaversion      int
                declare @schemaguid          uniqueidentifier
                declare @schematype          int

                select @schemaversion = schemaversion from dbo.sysmergeschemachange
                if (@schemaversion is NULL)
                    set @schemaversion = 1
                else
                    select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
                set @schemaguid = newid()
                set @schematype = 300 /* REPLICATE_DDL_ACTIONS */
                set @schematext = N' if object_id(N''' + sys.fn_replreplacesinglequote(@source_table) + ''', ''U'') is not NULL and object_id(N''' + @constraintname + ''', ''C'') is not NULL alter table '+ @source_table + ' drop constraint ' + @constraint
name
                exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
   end
        end

        if exists (select * from dbo.MSmerge_identity_range where artid=@artid)
        begin
            delete from dbo.MSmerge_identity_range where artid=@artid and subid in (select subid from dbo.sysmergesubscriptions where pubid=@pubid)
            if @@ERROR<>0
                return (1)
            DBCC CHECKIDENT(@source_table, RESEED) with no_infomsgs
            if @@ERROR<>0
                return (1)
        end
    end

 
Last revision 2008RTM
See also

  sp_changemergearticle (Procedure)
sp_MSarticlecleanup (Procedure)
sp_MSdropmergearticle (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