Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_mergesystables

  No additional text.


Syntax
create procedure sys.sp_MScreate_mergesystables
    @whattocreate smallint    -- 1=heavyweight, 2=lightweight
as
    -- Parameter check
    if @whattocreate not in (1,2)
    begin
        return 1
        RAISERROR (20008, 16, -1)
    end

    /* This is to make sure that the varbinary columns do not get padded */
    set ANSI_PADDING off

    DECLARE @exist bit
    DECLARE @validsubs smallint
    declare @retval int
    declare @retcode int

    select @exist = 1

    -- If the database is a publisher, verify that this SKU allows this
    if 1=sys.fn_MSrepl_ismergepublished(db_name())
    begin
        exec @retcode= sys.sp_MSsku_allows_replication
        if @@error<>0 return 1
        if @retcode <> 0
        begin
            raiserror(21106, 16, -1)
            return (1)
        end
    end

    begin tran
    save transaction sp_MScreate_mergesystables

    -- We first remove metadata tables if there are no valid subscriptions.
    
    exec sys.sp_MScheckvalidsystables @validsubs output

    if 0=@validsubs
    begin
        exec sys.sp_MSdrop_mergesystables @whattodrop=3
    end
    else if 2=@validsubs and 1=@whattocreate
    begin
        exec sys.sp_MSdrop_mergesystables @whattodrop=1
    end
    else if 1=@validsubs and 2=@whattocreate
    begin
        exec sys.sp_MSdrop_mergesystables @whattodrop=2
    end

    if 1=@whattocreate
    begin
        exec @retcode = sys.sp_MSmerge_create_sub_table
        if @retcode <> 0 or @@ERROR <> 0
            goto Error

        exec @retcode = sys.sp_MSmerge_create_pub_table
        if @retcode <> 0 or @@ERROR <> 0
            goto Error

        -- this table exists at publisher and subscriber dbs
        if object_id('MSmerge_identity_range', 'U') is NULL
        begin
            create table dbo.MSmerge_identity_range (
                subid               uniqueidentifier not NULL,
                artid               uniqueidentifier not NULL,
                range_begin         numeric(38,0) NULL,
                range_end           numeric(38,0) NULL,
                next_range_begin    numeric(38,0) NULL,
                next_range_end      numeric(38,0) NULL,
                is_pub_range        bit not NULL,
                max_used            numeric(38,0) NULL
            )
            exec dbo.sp_MS_marksystemobject MSmerge_identity_range
            if @@ERROR <> 0
                goto Error
            create unique clustered index uclidrange on MSmerge_identity_range(subid, artid, is_pub_range)
            if @@ERROR <> 0
                goto Error
        end

        if object_id('sysmergepartitioninfo', 'U') is NULL
        begin
            --raiserror('Creating table sysmergepartitioninfo',0,1)
            create table dbo.sysmergepartitioninfo (
                artid                           uniqueidentifier    NOT NULL,
                pubid                           uniqueidentifier    NOT NULL,
                partition_view_id               int                 NULL,
                repl_view_id                    int                 NULL,
                partition_deleted_view_rule     nvarchar(max)       NULL,
                partition_inserted_view_rule    nvarchar(max)       NULL,
                membership_eval_proc_name       sysname             NULL,
                column_list                     nvarchar(max)       NULL,
                column_list_blob                nvarchar(max)       NULL,
                expand_proc                     sysname             NULL,
                logical_record_parent_nickname  int                 NULL,
                logical_record_view             int                 NULL,
                logical_record_deleted_view_rule nvarchar(max)      NULL,
                logical_record_level_conflict_detection bit         default 0,
                logical_record_level_conflict_resolution bit        default 0,
                partition_options                tinyint            default 0
            )

            if @@error<>0
                goto Error
            else
            begin

                create unique clustered index uc1sysmergepartitioninfo
                    on dbo.sysmergepartitioninfo(artid, pubid)
                if @@ERROR <> 0
                    goto Error
            end

            exec dbo.sp_MS_marksystemobject sysmergepartitioninfo
            if @@ERROR <> 0
                goto Error
        end

        if object_id('sysmergepartitioninfoview', 'V') is not NULL
        begin
            drop view dbo.sysmergepartitioninfoview
        end
        exec ('create view dbo.sysmergepartitioninfoview as
               select sma.*, smaw.partition_view_id,
                    smaw.repl_view_id,
                    smaw.partition_deleted_view_rule,
                    smaw.partition_inserted_view_rule,
                    smaw.membership_eval_proc_name,
                    smaw.column_list,
                    smaw.column_list_blob,
                    smaw.expand_proc,
                    smaw.logical_record_parent_nickname,
                    smaw.logical_record_view,
                    smaw.logical_record_deleted_view_rule,
                    smaw.logical_record_level_conflict_detection,
                    smaw.logical_record_level_conflict_resolution,
                    smaw.partition_options
               from dbo.sysmergearticles sma, dbo.sysmergepartitioninfo smaw
               where sma.artid = smaw.artid and sma.pubid = smaw.pubid')
        if @@error <> 0
            goto Error

        exec dbo.sp_MS_marksystemobject sysmergepartitioninfoview

        if object_id('dbo.sysmergeschemaarticles', 'U') is null
        begin
            exec @retval= sys.sp_MScreate_sysmergeschemaarticles_table
            if @@error <> 0 or @retval <> 0 goto Error
        end

        if object_id('sysmergeextendedarticlesview') is not NULL
        begin
            drop view dbo.sysmergeextendedarticlesview
        end

        exec @retval= sys.sp_MScreate_sysmergeextendedarticlesview
        if @@error<>0 or @retval<>0 goto Error

        exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview

        if object_id('MSmerge_tombstone', 'U') is NULL
        begin
            create table dbo.MSmerge_tombstone
            (
                rowguid            uniqueidentifier rowguidcol NOT NULL,
                tablenick        int                NOT NULL,
                type            tinyint            NOT NULL,
                lineage            varbinary(311)    NOT NULL,
                generation        bigint            NOT NULL,
                logical_record_parent_rowguid    uniqueidentifier    NULL,
                logical_record_lineage    varbinary(311)    NULL,
            )

            if @@ERROR <> 0
                goto Error
            else
            begin
                create unique clustered index uc1MSmerge_tombstone
                    on MSmerge_tombstone (tablenick DESC, rowguid)
                if @@ERROR <> 0    goto Error

                create index nc2MSmerge_tombstone
                    on MSmerge_tombstone (generation)
                if @@ERROR <> 0    goto Error

                --create index nc3MSmerge_tombstone on MSmerge_tombstone(logical_record_parent_rowguid)
                --if @@ERROR <> 0    goto Error
            end


             exec dbo.sp_MS_marksystemobject MSmerge_tombstone
             if @@ERROR <> 0
                goto Error

        end

        if object_id('MSmerge_contents', 'U') is NULL
        begin

            create table dbo.MSmerge_contents
            (
                tablenick        int                           NOT NULL,
                rowguid          uniqueidentifier rowguidcol   NOT NULL,
                generation       bigint                        NOT NULL,
                partchangegen    bigint                        NULL,
                lineage          varbinary(311)                NOT NULL,
                colv1            varbinary(2953)               NULL,
                marker           uniqueidentifier              NULL,
                logical_record_parent_rowguid uniqueidentifier NULL,
                logical_record_lineage    varbinary(311)       NULL
            )

            if @@ERROR <> 0
                goto Error
            else
            begin
                create unique clustered index uc1SycContents on MSmerge_contents(tablenick, rowguid)
                if @@ERROR <> 0 goto Error
                create index nc2MSmerge_contents on MSmerge_contents(generation)
                if @@ERROR <> 0 goto Error
                create index nc4MSmerge_contents on MSmerge_contents(rowguid)
                if @@ERROR <> 0    goto Error
                create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
                if @@ERROR <> 0 goto Error
                --create index nc6MSmerge_contents on MSmerge_contents(logical_record_parent_rowguid)
                --if @@ERROR <> 0    goto Error
            end
            exec dbo.sp_MS_marksystemobject MSmerge_contents
            if @@ERROR <> 0
                goto Error

        end

        if object_id('MSmerge_genhistory', 'U') is NULL
        begin
            create table dbo.MSmerge_genhistory
            (
                guidsrc         uniqueidentifier     NOT NULL,
                pubid           uniqueidentifier     NULL,
                generation      bigint               identity(1,1) NOT NULL,
                art_nick        int                  NULL,
                -- when the size of nicknames is changed: adjust constants in CMergeDatasource::EnumerateGenerationRange.
                -- it is 1000 bytes in Shiloh, 1001 in Yukon: difference allows to determine whether column upgrade took place.
                -- Also need to update cbReplnickArrayMax in replfunc.cpp.
                nicknames       varbinary(1001)      NOT NULL,
                coldate         datetime             NOT NULL,
                genstatus       tinyint              NOT NULL default 0, -- 0 = open,
                                                                         -- 1 = closed and generated locally,
                                                                         -- 2 = closed and came from elsewhere,
                                                                         -- 3 = temporarily closed used only by makgeneration
                                                                         -- 4 = generation came from elsewhere and was inserted by merge. is open. possible interrupted generation
                changecount     int                  NOT NULL default 0,
                subscriber_number  int not NULL default 0
            )

            if @@ERROR <> 0
                goto Error

            create clustered index c1MSmerge_genhistory on MSmerge_genhistory(generation)
            if @@ERROR <> 0
                goto Error

            create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc, pubid)
            if @@ERROR <> 0
                goto Error

            create  index nc2MSmerge_genhistory on MSmerge_genhistory(genstatus, art_nick,changecount)
            if @@ERROR <> 0
                goto Error

            CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)
            if @@ERROR <> 0
                goto Error

             exec dbo.sp_MS_marksystemobject MSmerge_genhistory
             if @@ERROR <> 0
                goto Error
        end

        if object_id('MSmerge_settingshistory', 'U') is NULL
        begin
            --raiserror('Creating table MSmerge_settingshistory',0,1)

            --This table records the history of when merge related settings
            --were changed. It can also bo used to record important events
            --that affect behavior of merge replication.

            --eventtype can have one of the following values
            --  1   Initial publication level property setting.
            --  2   Change in publication property.
            --  101 Initial article level property setting.
            --  102 Change in article property.
            --  In future add publication related event below 100 and
            --  article related events about 100 to make searching easier

            create table dbo.MSmerge_settingshistory
            (
                eventtime        datetime            default getdate(),
                pubid            uniqueidentifier    NOT NULL,
                artid           uniqueidentifier    NULL,
                eventtype         tinyint                NOT NULL,
                propertyname    sysname             NULL,
                   previousvalue   sysname             NULL,
                newvalue        sysname             NULL,
                eventtext        nvarchar(2000)         NULL
            )

            if @@ERROR <> 0
                goto Error

            create clustered index c1MSmerge_settingshistory on MSmerge_settingshistory(pubid,eventtype)
            if @@ERROR <> 0
                goto Error

             exec dbo.sp_MS_marksystemobject MSmerge_settingshistory
             if @@ERROR <> 0
                goto Error
        end

        if object_id('sysmergeschemachange', 'U') is NULL
        begin

            --raiserror('Creating table sysmergeschemachange',0,1)

            create table dbo.sysmergeschemachange
            (
                pubid            uniqueidentifier     NOT NULL,
                artid            uniqueidentifier     NULL,
                schemaversion     int                    NOT NULL,
                schemaguid        uniqueidentifier     NOT NULL,
                schematype        int                      NOT NULL,
                schematext        nvarchar(max)         NOT NULL,
                schemastatus    tinyint                NOT NULL default(1),  -- 0 - inactive, 1 - active
                schemasubtype    int                      NOT NULL default(0) -- contains the actual type of schema change such as add/drop/modify col, 0 is dummy value
            )

            if @@ERROR <> 0
                goto Error
            else
                begin
                    create unique clustered index schemachangeversion on sysmergeschemachange(schemaversion, pubid)
                    if @@ERROR <> 0
                        goto Error
                end
            exec dbo.sp_MS_marksystemobject sysmergeschemachange
            if @@ERROR <> 0
                goto Error

        end

        if object_id('sysmergesubsetfilters', 'U') is NULL
        begin
            create table dbo.sysmergesubsetfilters (
                filtername              sysname                 NOT NULL,
                join_filterid            int                    identity NOT NULL,
                pubid                    uniqueidentifier    NOT NULL,
                artid                    uniqueidentifier    NOT NULL,
                art_nickname            int                    NOT NULL,
                join_articlename        sysname NOT NULL,
                join_nickname             int                    NOT NULL,
        join_unique_key            int                    NOT NULL,
                expand_proc                sysname                    NULL,
                join_filterclause        nvarchar(1000)          NULL,
                filter_type                tinyint                NOT NULL default 1
            )
            if @@ERROR <> 0
                goto Error

            CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters
                ON sysmergesubsetfilters(join_filterid, pubid)
            if @@ERROR <> 0
                goto Error

            CREATE UNIQUE CLUSTERED INDEX uc2sysmergesubsetfilters
                ON sysmergesubsetfilters(pubid, filtername)
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject sysmergesubsetfilters
            if @@ERROR <> 0
                goto Error
        end

        if @@error <> 0
            goto Error

        if object_id('MSdynamicsnapshotviews', 'U') is NULL
        begin
            --raiserror('Creating table MSdynamicsnapshotviews',0,1)

            create table dbo.MSdynamicsnapshotviews (
                dynamic_snapshot_view_name sysname primary key
            )
            if @@ERROR <> 0
                goto Error
            exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews
        end

        if object_id('MSdynamicsnapshotjobs', 'U') is NULL
        begin
            --raiserror('Creating table MSdynamicsnapshotjobs',0,1)

            create table dbo.MSdynamicsnapshotjobs (
                id int identity,
                name sysname not null unique,
                pubid uniqueidentifier not null,
                job_id uniqueidentifier not null,
                agent_id int not null default 0,
                dynamic_filter_login sysname null,
                dynamic_filter_hostname sysname null,
                dynamic_snapshot_location nvarchar(255) not null,
                partition_id int not NULL default -1,
                computed_dynsnap_location bit not NULL default 0
            )
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs

            create unique clustered index uciMSdynamicsnapshotjobs on
                dbo.MSdynamicsnapshotjobs(job_id, pubid)

            if @@ERROR <> 0
                goto Error

            create nonclustered index nciMSdynamicsnapshotjobs on
                dbo.MSdynamicsnapshotjobs(partition_id)

            if @@ERROR <> 0
                goto Error

        end

        if @@error <> 0
            goto Error

        if object_id('MSmerge_altsyncpartners', 'U') is NULL
        begin
            --raiserror('Creating table MSmerge_altsyncpartners',0,1)

            create table dbo.MSmerge_altsyncpartners (
                subid                 uniqueidentifier     not null,
                alternate_subid     uniqueidentifier     not null,
                description            nvarchar(255)        NULL
            )
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners

            create unique clustered index uciMSmerge_altsyncpartners on
                dbo.MSmerge_altsyncpartners(subid, alternate_subid)

            if @@ERROR <> 0
                goto Error


        end

        -- new tables added for the first time after SQL2000.
        if object_id('MSmerge_partition_groups', 'U') is NULL
        begin
            create table dbo.MSmerge_partition_groups (partition_id int identity not null primary key clustered,
            						publication_number smallint not null, maxgen_whenadded bigint null,
            						using_partition_groups bit default 0, is_partition_active bit default 1 not null)
         if @@ERROR <> 0 goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_partition_groups
            if @@ERROR <> 0 goto Error

            create nonclustered index nc1MSmerge_partition_groups on dbo.MSmerge_partition_groups (publication_number)
            if @@ERROR <> 0 goto Error

            grant select on dbo.MSmerge_partition_groups to public
        end

        if object_id('MSmerge_generation_partition_mappings', 'U') is NULL
        begin
            create table dbo.MSmerge_generation_partition_mappings
                    (
                    publication_number smallint not null,
                    generation bigint not null,
                    partition_id int not null,
                    changecount int NOT NULL default 0
                    )
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_generation_partition_mappings

            create clustered index cMSmerge_generation_partition_mappings on dbo.MSmerge_generation_partition_mappings (partition_id, publication_number)
            if @@ERROR <> 0
                goto Error
            create nonclustered index nc1MSmerge_generation_partition_mappings on dbo.MSmerge_generation_partition_mappings(generation)
            if @@ERROR <> 0
                goto Error
        end

        if object_id('MSmerge_current_partition_mappings', 'U') is NULL
        begin
            create table dbo.MSmerge_current_partition_mappings (publication_number smallint not null, tablenick int not null, rowguid uniqueidentifier not null, partition_id int not null)
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_current_partition_mappings

            create clustered index cMSmerge_current_partition_mappings on dbo.MSmerge_current_partition_mappings (tablenick, rowguid)
            if @@ERROR <> 0
                goto Error

            create nonclustered index ncMSmerge_current_partition_mappings on dbo.MSmerge_current_partition_mappings (publication_number, partition_id)
            if @@ERROR <> 0
                goto Error
        end

        if object_id('MSmerge_past_partition_mappings', 'U') is NULL
        begin
            create table dbo.MSmerge_past_partition_mappings (publication_number smallint not null, tablenick int not null, rowguid uniqueidentifier not null, partition_id int not null, generation bigint null,reason tinyint not null default(0))
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_past_partition_mappings

            create clustered index cMSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (tablenick, rowguid)
            if @@ERROR <> 0
                goto Error

            create nonclustered index nc1MSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (publication_number, partition_id)
            if @@ERROR <> 0
                goto Error


            create nonclustered index nc2MSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (generation, tablenick) include (publication_number, partition_id)
            if @@ERROR <> 0
                goto Error
        end

        if object_id('MSmerge_dynamic_snapshots', 'U') is NULL
        begin
            create table dbo.MSmerge_dynamic_snapshots (
                    partition_id int not null primary key clustered foreign key references dbo.MSmerge_partition_groups(partition_id) on delete cascade,
                    dynamic_snapshot_location nvarchar(255) null,
                    last_updated datetime null,
                    last_started datetime null)
            if @@ERROR <> 0 goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_dynamic_snapshots
        end

        if object_id('MSmerge_supportability_settings', 'U') is NULL
        begin
            create table dbo.MSmerge_supportability_settings (
                    pubid                uniqueidentifier    NULL,
                    subid                uniqueidentifier    NULL,
                    web_server           sysname             NULL,
                    constraint           unique_supportpubsrvdb     unique nonclustered (pubid, subid, web_server),
                    support_options      int NOT NULL default(0),    -- check the SUPPORT_OPTIONS enum in agent code.
                    log_severity         int NOT NULL default(2),
                    log_modules          int NOT NULL default(0),
                    log_file_path        nvarchar(255) NULL,
                    log_file_name        sysname NULL,
                    log_file_size        int NOT NULL default(10000000),
                    no_of_log_files      int NOT NULL default(5),
                    upload_interval      int NOT NULL default(0),
                    delete_after_upload  int NOT NULL default(0),
                    custom_script        nvarchar(2048) NULL,
                    message_pattern      nvarchar(2000) NULL,
                    last_log_upload_time datetime       NULL
               )
            if @@ERROR <> 0 goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_supportability_settings
        end

        if object_id('MSmerge_log_files', 'U') is NULL
        begin
            create table dbo.MSmerge_log_files (
                    id                   int identity(1,1),
                    pubid                uniqueidentifier    NULL,
                    subid                uniqueidentifier    NULL,
                    web_server           sysname             NULL,
                    file_name            nvarchar(2000)      NOT NULL,
                    upload_time          datetime            NOT NULL default getdate(),
                    log_file_type        int                 NOT NULL, -- Check UPLOAD_LOG_FILE_TYPE enum in agent code.
                    log_file             varbinary(max)      NULL
                )
            if @@ERROR <> 0 goto Error

            create clustered index ucMSmerge_log_files on MSmerge_log_files(pubid, subid, id)
            if @@ERROR <> 0 goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_log_files
        end
    end --1=@whattocreate
    else
    begin
        exec @retval= sys.sp_MScreatelightweightmetatables
        if @@ERROR <> 0 or @retval <> 0 goto Error
    end

    if object_id('sp_MScreatedebuginfrastructure','P') is not null
    begin
        exec @retval= sys.sp_MScreatedebuginfrastructure
        if @@ERROR <> 0 or @retval <> 0 goto Error
    end

    commit transaction
    return (0)
Error:
    ROLLBACK TRANSACTION sp_MScreate_mergesystables
    COMMIT TRANSACTION
    RAISERROR (20008, 16, -1)
    return (1)

 
Last revision 2008RTM
See also

  sp_addmergepullsubscription (Procedure)
sp_addmergesubscription (Procedure)
sp_MSmergepublishdb (Procedure)
sp_MSmergesubscribedb (Procedure)
sp_MSmerge_ddldispatcher (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