Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmerge_create_sub_table

  No additional text.


Syntax
create procedure sys.sp_MSmerge_create_sub_table as

    if object_id('dbo.sysmergesubscriptions', 'U') is null
    begin

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

        create table dbo.sysmergesubscriptions
        (
            subscriber_server    sysname            NULL,
            db_name              sysname            NOT NULL,
            pubid                uniqueidentifier   NULL,
            constraint           unique_pubsrvdb    unique nonclustered (pubid, subscriber_server, db_name),
            datasource_type      int                NOT NULL default 0,
            subid                uniqueidentifier   NOT NULL,
            replnickname         binary(6)          NOT NULL,
            replicastate         uniqueidentifier   NOT NULL,
            status               tinyint            NOT NULL,
            subscriber_type      int                NOT NULL,
            subscription_type    int                NOT NULL,
            sync_type            tinyint            NOT NULL,    -- 1 = automatic 2 = no sync
            description          nvarchar(255)      NULL,
            priority             real               NULL,       -- only valid when subscriber_type is global
            recgen               bigint             NULL,
            recguid              uniqueidentifier   NULL,
            sentgen              bigint             NULL,
            sentguid             uniqueidentifier   NULL,
            schemaversion        int                NULL,
            schemaguid           uniqueidentifier   NULL,
            last_validated       datetime           NULL,
            attempted_validate   datetime           NULL,
            last_sync_date       datetime           NULL,
            last_sync_status     int                NULL,
            last_sync_summary    sysname            NULL,
            metadatacleanuptime  datetime           not NULL default getdate(),
            partition_id         int                NULL,
            cleanedup_unsent_changes bit            NOT NULL default 0,
            replica_version      int                NOT NULL default 60,    -- 60 = shiloh sp3 and below, 90=Yukon
            supportability_mode  int                NOT NULL default 0,    -- central control of log files. Off by default.
            application_name     sysname            NULL,
            subscriber_number    int identity not NULL,
            last_makegeneration_datetime datetime NULL

            -- whenever adding a column here also add it to sp_MScreatenewreplnick
        )

        if @@error<>0
            goto Error
        else
        begin
            create unique clustered index uc1sysmergesubscriptions on dbo.sysmergesubscriptions (subid)
            if @@ERROR<>0
                goto Error

            create index nc2sysmergesubscriptions on dbo.sysmergesubscriptions (subscriber_server, db_name)
            if @@ERROR<>0
                goto Error

            create index nc3sysmergesubscriptions on dbo.sysmergesubscriptions (replnickname)
            if @@ERROR<>0
                goto Error
        end
        exec dbo.sp_MS_marksystemobject sysmergesubscriptions
        if @@ERROR <> 0
            goto Error
    end

    exec sys.sp_MScreate_common_dist_tables @subside=1

    if object_id('dbo.MSmerge_agent_parameters', 'U') is null
    begin
        --raiserror('Creating table MSmerge_agent_parameters',0,1)

        create table dbo.MSmerge_agent_parameters
        (
        profile_name         sysname        NOT NULL,
        parameter_name       sysname        NOT NULL,
        value                nvarchar(255)  NOT NULL
        )

        if @@ERROR <> 0
            goto Error

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

    if object_id('dbo.MSmerge_replinfo', 'U') is null
    begin
        --raiserror('Creating table MSmerge_replinfo',0,1)

        create table dbo.MSmerge_replinfo
        (
            repid                     uniqueidentifier     NOT NULL,
            use_interactive_resolver  bit        NOT NULL default 0,
            validation_level          int                    not NULL default 0,
            resync_gen                bigint                not NULL default -1,
            login_name                sysname             not NULL default suser_sname(suser_sid()),
            hostname                  sysname                NULL,
            merge_jobid               binary(16)            NULL,
            sync_info                 int identity not NULL -- used only by lightweight subscribers
        )

        if @@ERROR <> 0
            goto Error
        else
        begin
            create unique clustered index uc1MSmerge_replinfo
                on dbo.MSmerge_replinfo (repid)
            if @@ERROR <> 0
                goto Error
        end
        exec dbo.sp_MS_marksystemobject MSmerge_replinfo
        if @@ERROR <> 0
            goto Error
    end

    if object_id('dbo.sysmergearticles', 'U') is null
    begin
        create table dbo.sysmergearticles
        (
            name                    sysname                NOT NULL,
            type                    tinyint                NULL,
            objid                   int                    NOT NULL,
            sync_objid              int                    NOT NULL,
            view_type               tinyint                NULL,
            artid                   uniqueidentifier       NOT NULL,
            description             nvarchar(255)          NULL,
            pre_creation_command    tinyint                NULL,
            pubid                   uniqueidentifier       NOT NULL,
            nickname                int                    NOT NULL,
            column_tracking         int                    NOT NULL,
            status                  tinyint                NULL,
            conflict_table          sysname                NULL,
            creation_script         nvarchar(255)          NULL,
            conflict_script         nvarchar(255)          NULL,
            article_resolver        nvarchar(255)          NULL,
            ins_conflict_proc       sysname                NULL,
            insert_proc             sysname                NULL,
            update_proc             sysname                NULL,
            select_proc             sysname                NULL,
            metadata_select_proc    sysname                NULL,
            delete_proc             sysname                NULL,
            schema_option           binary(8)              NULL,
            destination_object      sysname                NOT NULL,
            destination_owner       sysname                NULL,
            resolver_clsid          nvarchar(50)           NULL,
            subset_filterclause     nvarchar(1000)         NULL,
            missing_col_count       int                    NULL,
            missing_cols            varbinary(128)         NULL,
            excluded_cols           varbinary(128)         NULL,
            excluded_col_count      int                    not NULL default 0,
            columns                 varbinary(128)         NULL,
            deleted_cols            varbinary(128)       NULL default 0x0,
            resolver_info           nvarchar(517)        NULL,
            view_sel_proc           nvarchar(290)        NULL,
            gen_cur                 bigint                NULL,
            vertical_partition      int                    not NULL default 0,
            identity_support        int                    not NULL default 0,
            before_image_objid      int           NULL,
            before_view_objid       int                    NULL,
            verify_resolver_signature  int            NULL default 1,
            allow_interactive_resolver bit            NOT NULL default 0,
            fast_multicol_updateproc   bit            NOT NULL default 0,
            check_permissions          int            NOT NULL default 0,
            maxversion_at_cleanup      int            NOT NULL default 1,
            processing_order        int               NOT NULL default 0,
            upload_options          tinyint           NOT NULL default 0, -- 0 = Default case. Changes on subscriber replicate to publisher.
                                                                          -- 1 = Changes on subscriber are allowed but not uploaded.
                                                                          -- 2 = Changes on subscriber are not allowed.
                                                                          -- 3 = Out of partition changes are not allowed on subscriber.

            published_in_tran_pub   bit               NOT NULL default 0,
            lightweight             bit               not null default 0,
            procname_postfix        nchar(32)         null,    -- for lightweight
            well_partitioned_lightweight bit          null,
            before_upd_view_objid        int          NULL, -- only used by makegeneration because users of other publications may not have access to bi object of this publication
            delete_tracking          bit              default 1,  -- when set to false the delete triggers do not update tombstone tables and hence deletes are not replicated.
            compensate_for_errors    bit              not null default 0,
            pub_range                bigint NULL, -- publisher identity range
            range                    bigint NULL, -- subscriber identity range
            threshold                int    NULL,    -- in percentage, set by sp_addmergearticle
            stream_blob_columns      bit              NOT NULL default 0,  -- controls whether    blob optimization is used or not.
            preserve_rowguidcol      bit              not null default 1 -- 0=rg created by replication
            -- Note: Please update sysmergeextendedarticlesview whenever
            -- there is a schema change in sysmergearticles
        )

        if @@error<>0 goto Error

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

        if not exists (select * from sys.indexes where name = 'nc1sysmergearticles')
        begin
            create nonclustered index nc1sysmergearticles on dbo.sysmergearticles(nickname)
            if @@ERROR <> 0    goto Error
        end

        if not exists (select * from sys.indexes where name = 'nc2sysmergearticles')
        begin
            create nonclustered index nc2sysmergearticles on dbo.sysmergearticles(processing_order)
            if @@ERROR <> 0    goto Error
        end

        if not exists (select * from sys.indexes where name = 'nc3sysmergearticles')
        begin
            create unique nonclustered index nc3sysmergearticles on dbo.sysmergearticles(objid, pubid)
            if @@ERROR <> 0    goto Error
        end

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

        grant select(nickname, maxversion_at_cleanup, objid) on dbo.sysmergearticles to public
        if @@ERROR <> 0    goto Error

    end

    if object_id('dbo.MSmerge_conflicts_info', 'U') is null
    begin

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

        create table dbo.MSmerge_conflicts_info
        (
            tablenick                int                NOT NULL,
            rowguid                  uniqueidentifier    rowguidcol NOT NULL,
            origin_datasource        nvarchar(255)    NULL,
            conflict_type            int NULL,
            reason_code              int NULL,
            reason_text              nvarchar(720)         NULL,
            pubid                    uniqueidentifier    NULL,
            MSrepl_create_time       datetime not null default getdate(),
            origin_datasource_id     uniqueidentifier    NULL
        )
        if @@ERROR <> 0
            goto Error

        create clustered index uc1MSmerge_conflicts_info on MSmerge_conflicts_info(tablenick, rowguid)
        if @@ERROR <> 0
            goto Error

        CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_conflicts_info
            ON MSmerge_conflicts_info(tablenick, rowguid, origin_datasource, conflict_type)
        if @@ERROR <> 0
            goto Error

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

    if object_id('dbo.MSmerge_metadataaction_request', 'U') is null
    begin
        create table dbo.MSmerge_metadataaction_request
        (
            tablenick int not null,
            rowguid uniqueidentifier not null,
            action tinyint not null,
            generation bigint null, -- for hws cleanup
            changed int null -- for lws cleanup
        )
        if @@ERROR <> 0    goto Error

        create clustered index ucMSmerge_metadataaction_request on MSmerge_metadataaction_request(tablenick, rowguid)
        if @@ERROR <> 0    goto Error

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

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

        create table dbo.MSmerge_errorlineage (
            tablenick        int NOT NULL,
            rowguid            uniqueidentifier NOT NULL,
            lineage            varbinary(311)
        )
        if @@ERROR <> 0 goto Error

        EXEC dbo.sp_MS_marksystemobject MSmerge_errorlineage
        if @@ERROR <> 0 goto Error
        create unique clustered index uc1errorlineage on MSmerge_errorlineage(tablenick, rowguid)
        if @@ERROR <> 0 goto Error
    end

    return 0

Error:
    return 1

 
Last revision SQL2008SP2
See also

  sp_MScreatelightweightmetatables (Procedure)
sp_MScreate_mergesystables (Procedure)
sp_vupgrade_mergetables (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