Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_pub_tables

  No additional text.


Syntax
create procedure sys.sp_MScreate_pub_tables
AS
    DECLARE @retcode int,
    		@fError int
    		
    SELECT @fError = 0

    -- enable 'create tables as pseudo system tables


    /*
    ** Msg 226, Level 16, State 9
    ** CREATE TABLE system-table command not allowed within multi-statement transaction.
    */
    /*
    BEGIN TRAN sp_create_central_pub_tables
    */

    /* Creating 'sysarticles' */
    IF not exists (select * from sys.objects where name = 'sysarticles')
    BEGIN
        create table dbo.sysarticles
        (
        artid               int                 identity NOT NULL,
        creation_script     nvarchar(255)       NULL,
        del_cmd             nvarchar(255)       NULL,
        description         nvarchar(255)       NULL,
        dest_table          sysname             NOT NULL,
        filter              int                 NOT NULL,
        filter_clause       ntext               NULL,
        ins_cmd             nvarchar(255)       NULL,
        name                sysname             NOT NULL,
        objid               int                 NOT NULL,
        pubid               int                 NOT NULL,
        pre_creation_cmd    tinyint             NOT NULL,
        status              tinyint             NOT NULL,
        sync_objid          int                 NOT NULL,
        type                tinyint             NOT NULL,
        upd_cmd             nvarchar(255)       NULL,
        schema_option       binary(8)           NULL,
        dest_owner          sysname             NULL,
        ins_scripting_proc int NULL,	-- object id of custom scripting proc registered by user, to replace sp_scriptinsproc
        del_scripting_proc int NULL,	-- object id of custom scripting proc registered by user, to replace sp_scriptdelproc
        upd_scripting_proc int NULL,	-- object id of custom scripting proc registered by user, to replace sp_scriptupdproc
        custom_script nvarchar(2048) NULL,	-- custom script, set by sp_register_custom_script, cleared by DDL trigger
        								-- 	or by sp_unregister_custom_script
        fire_triggers_on_snapshot bit           NOT NULL default 0
        -- Note: Please update sysextendedarticlesview whenever
        -- there is a schema change in sysarticles
        )

        exec dbo.sp_MS_marksystemobject 'sysarticles'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index c1sysarticles
            on sysarticles(artid, pubid)

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END
    END
    IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
    BEGIN
        create table dbo.sysarticlecolumns
        (
        artid             int NOT NULL,
        colid             int NOT NULL,
        is_udt 			bit default 0,
        is_xml 			bit default 0,
        is_max 			bit default 0
        )
    create unique clustered index idx_sysarticlecolumns on sysarticlecolumns (artid, colid)
        EXEC dbo.sp_MS_marksystemobject 'sysarticlecolumns'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END
    END

	 /* Creating 'sysschemaarticles' */
    IF not exists (select * from sys.objects where name = 'sysschemaarticles')
    BEGIN
        create table dbo.sysschemaarticles
        (
        artid               int                 NOT NULL,
        creation_script     nvarchar(255)       NULL,
        description         nvarchar(255)       NULL,
        dest_object         sysname             NOT NULL,
        name                sysname             NOT NULL,
        objid               int                 NOT NULL,
        pubid               int                 NOT NULL,
        pre_creation_cmd    tinyint             NOT NULL,
        status              int                 NOT NULL,
        type                tinyint           NOT NULL,
        schema_option       binary(8)           NULL,
        dest_owner          sysname             NULL
        )

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        exec dbo.sp_MS_marksystemobject 'sysschemaarticles'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index c1sysschemaarticles
            on sysschemaarticles(artid, pubid)

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

    END

    /* Creating 'sysextendedarticlesview' */
    IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
    BEGIN
        exec ('create view dbo.sysextendedarticlesview
               as
               select artid, creation_script, del_cmd, description,
        	dest_table, filter, filter_clause, ins_cmd, name, objid, pubid,
        	pre_creation_cmd, status, sync_objid, type, upd_cmd,
        	schema_option, dest_owner, ins_scripting_proc, del_scripting_proc,
        	upd_scripting_proc, custom_script, fire_triggers_on_snapshot
               from sysarticles
               union all
               select artid, creation_script, NULL, description,
               dest_object, NULL, NULL, NULL, name, objid, pubid,
               pre_creation_cmd, status, NULL, type, NULL,
               schema_option, dest_owner, NULL, NULL, NULL, NULL, 0
               from sysschemaarticles
               go')

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        exec dbo.sp_MS_marksystemobject 'sysextendedarticlesview'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

    END

    /* Creating 'syspublications' */
    IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
    BEGIN
        CREATE TABLE dbo.syspublications (
        description                 nvarchar(255)   NULL,
        name                        sysname         NOT NULL,
        pubid                       int    identity NOT NULL,
        repl_freq                   tinyint         NOT NULL,
        status                      tinyint         NOT NULL,
        sync_method                 tinyint         NOT NULL,
        snapshot_jobid              binary(16)      NULL,
        independent_agent           bit             NOT NULL,
        immediate_sync              bit             NOT NULL,
        enabled_for_internet        bit             NOT NULL,
        allow_push                  bit             NOT NULL,
        allow_pull                  bit             NOT NULL,
        allow_anonymous             bit             NOT NULL,
        immediate_sync_ready        bit             NOT NULL,
        -- SyncTran
        allow_sync_tran             bit             NOT NULL,
        autogen_sync_procs          bit             NOT NULL,
        retention                   int             NULL,
        -- The following are post 7.0
        allow_queued_tran           bit   default 0 not null,
        -- portable snapshot support
        snapshot_in_defaultfolder           bit   default 1 NOT NULL,
        alt_snapshot_folder         nvarchar(255)   NULL,
        -- snapshot pre/post- command
        pre_snapshot_script         nvarchar(255)   NULL,
        post_snapshot_script        nvarchar(255)   NULL,
        -- Snapshot compression
        compress_snapshot           bit   default 0 NOT NULL,
        -- Post 7.0 Ftp support
        ftp_address                 sysname         NULL,
        ftp_port                    int   default 21 NOT NULL,
        ftp_subdirectory            nvarchar(255)   NULL,
        ftp_login                   sysname         NULL default N'anonymous',
        ftp_password                nvarchar(524)   NULL,
        allow_dts       bit default 0 not null,
        allow_subscription_copy     bit default 0 not null,
        centralized_conflicts       bit             NULL, -- 0 False, 1 True
        conflict_retention          int             NULL, -- 60
        conflict_policy             int             NULL, -- 1 = PubWins, 2 = SubWins, 3 = Reinit
        queue_type                  int             NULL,  -- 1 = MSMQ, 2 = SQL
        ad_guidname                 sysname         NULL,
        backward_comp_level int default 10 not NULL, -- default is sphinx
        allow_initialize_from_backup  bit   default 0 NOT NULL,
        min_autonosync_lsn binary(10)      NULL,
        replicate_ddl int default 1,
        options int default 0 not null,
        originator_id int NULL, -- 4 bytes are used
		)

        exec dbo.sp_MS_marksystemobject 'syspublications'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index uc1syspublications
            on syspublications (pubid)
        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc2syspublications
            on syspublications (name)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create nonclustered index nc3syspublications
            on syspublications (status)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

    END

       /* Creating 'syssubscriptions' */
    IF not exists (select * from sys.objects where name = 'syssubscriptions')
    BEGIN

        CREATE TABLE dbo.syssubscriptions
        (
        artid                   int                 NOT NULL,
        srvid                   smallint            NOT NULL,
        dest_db                 sysname             NOT NULL,
        status                  tinyint             NOT NULL,
        sync_type               tinyint             NOT NULL,
        login_name              sysname             NOT NULL,
        subscription_type       int                 NOT NULL,
        distribution_jobid      binary(16)          NULL,
        timestamp NOT NULL,
        -- SyncTran
        update_mode             tinyint             NOT NULL, -- 0(read only), 1(Sync Tran), 2(Queued Tran), 3(Failover),
                                                         -- 4(sqlqueued), 5(sqlqueued failover), 6(sqlqueued qfailover), 7(qfailover)
        loopback_detection      bit NOT NULL,
        queued_reinit           bit DEFAULT 0       NOT NULL,
        nosync_type             tinyint DEFAULT 0   NOT NULL -- 0(none), 1(replication support only), 2(initialize with backup), 3(initialize from lsn)
        ,srvname sysname not null default N''
        )

        exec dbo.sp_MS_marksystemobject 'syssubscriptions'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1syssubscriptions
            on syssubscriptions (artid, srvid, dest_db, srvname)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

    END

    -- SyncTran
    /* Creating 'sysarticleupdates' */

    IF not exists (select * from sys.objects where name = 'sysarticleupdates')
    BEGIN

        CREATE TABLE dbo.sysarticleupdates
        (
        artid                  int       NOT NULL,
        pubid                  int       NOT NULL,
        sync_ins_proc          int       NOT NULL,     -- ID of sproc handling Insert Sync Transactions
        sync_upd_proc          int       NOT NULL,     -- ID of sproc handling Update Sync Transactions
        sync_del_proc          int       NOT NULL,     -- ID of sproc handling Delete Sync Transactions
        autogen                bit       NOT NULL,
        sync_upd_trig          int       NOT NULL,     -- Note 7.0 upgrade issue
        conflict_tableid       int       NULL,         -- ID of conflict table for this article
        ins_conflict_proc      int      NULL,         -- ID of sproc to log conflicts
        identity_support       bit default 0 not null  -- Whether or not do auto identity range
        )

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END


        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'sysarticleupdates'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysarticleupdates
            on sysarticleupdates (artid, pubid)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END
    -- end SyncTran


    IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
    BEGIN
        CREATE TABLE dbo.MSpub_identity_range
        (
            objid int not null,
            range bigint not null,
            pub_range bigint not null,
            current_pub_range bigint not null,
            threshold int not null,
            last_seed bigint null -- It will be not when uninitialized.
        )

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END


        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'MSpub_identity_range'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1MSpub_identity_range
            on MSpub_identity_range (objid)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END

    IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
    BEGIN
        CREATE TABLE dbo.systranschemas
        (
            tabid int not null,
            startlsn binary(10) not null,
            endlsn binary(10) not null,
			typeid int not null default 52
    	)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'systranschemas'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index uncsystranschemas
            on systranschemas (startlsn)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END

	-- We will always create PeerToPeer tables at the publisher. Subscriber side
	-- tables will only be created when subscribed to PeerToPeer publication
	exec @retcode = sys.sp_MScreate_peer_tables
	IF @retcode <> 0 or @@ERROR <> 0
    BEGIN
    	GOTO ERROR
    END

	EXEC @retcode = sys.sp_MSrepl_ddl_triggers @type='tran', @mode='drop'
	IF @@ERROR <> 0 or @retcode <> 0
	BEGIN
    	GOTO ERROR
	END

	EXEC @retcode = sys.sp_MSrepl_ddl_triggers @type='tran', @mode='add'
	IF @@ERROR <> 0 or @retcode <> 0
	BEGIN
    	GOTO ERROR
	END
CLEANUP:

    RETURN( @fError )

ERROR:

    select @fError = 1
    GOTO CLEANUP


 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSpublishdb (Procedure)
sp_vupgrade_publisherdb (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