Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_sub_tables_internal

  No additional text.


Syntax
create procedure sys.sp_MScreate_sub_tables_internal
(
    @tran_sub_table                 bit = 0,
    @property_table                 bit = 1,
    @sqlqueue_table                 bit = 0,
    @subscription_articles_table    bit = 0,
    @p2p_table                         bit = 0
)
as
BEGIN
    set nocount on
    declare @retcode int

    
    -- for transactional subscriptions
    
    IF @tran_sub_table = 1
    BEGIN
        
        -- MSreplication_subscriptions
        
        if object_id(N'dbo.MSreplication_subscriptions', 'U') is NULL
        begin
            
            -- table does not exist - create the table
            
            BEGIN TRAN sp_MScreate_sub_tables
            SAVE TRAN sp_MScreate_sub_tables
            CREATE TABLE dbo.MSreplication_subscriptions
            (
            publisher sysname NOT NULL,
            publisher_db sysname NULL,
            publication sysname NULL,
            independent_agent bit NOT NULL,
            subscription_type int NOT NULL,
            distribution_agent sysname NULL,
            time smalldatetime NOT NULL,
            description nvarchar(255) NULL,
            transaction_timestamp varbinary(16) NOT NULL,
            update_mode tinyint NOT NULL,
            agent_id binary(16) NULL,
            subscription_guid binary(16) NULL,
            subid binary(16) NULL,
            immediate_sync bit NOT NULL default 1, -- sync_mode with a default of 1
            )
            IF @@ERROR <> 0
                GOTO UNDO
            CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON
                MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type, transaction_timestamp)
            IF @@ERROR <> 0
                GOTO UNDO

            exec dbo.sp_MS_marksystemobject 'MSreplication_subscriptions'
            IF @@ERROR <> 0
                GOTO UNDO
            COMMIT TRAN sp_MScreate_sub_tables
        end
        else
        begin
            
            -- table exists - add new columns
            

            if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1
            BEGIN
                alter table dbo.MSreplication_subscriptions alter column distribution_agent sysname null
            end

            if exists (select * from sys.indexes where object_id = object_id('MSreplication_subscriptionss') and name = 'uc1MSReplication_subscriptions'
                    and is_unique = 'True' )
            begin
                if not exists (select * from sys.indexes SI
                            join sys.index_columns SIC on SI.object_id = SIC.object_id and SI.index_id = SIC.index_id
                            join sys.columns SC on SI.object_id = SC.object_id and SC.column_id = SIC.column_id
                            where SI.object_id = object_id('MSreplication_subscriptions') and is_unique = 'True' and SC.name = 'transaction_timestamp')
                begin
                    drop index MSreplication_subscriptions.uc1MSReplication_subscriptions
                    CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON
                        MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type, transaction_timestamp)
                end
            end
        end -- MSreplication_subscriptions

        
        -- MSsubscription_agents
        
        IF object_id(N'dbo.MSsubscription_agents', 'U') is NULL
        BEGIN
            
            -- table does not exist - create the table
            
            BEGIN TRAN sp_MScreate_sub_tables
            SAVE TRAN sp_MScreate_sub_tables
            CREATE TABLE dbo.MSsubscription_agents
            (
            id int identity,
            publisher sysname NOT NULL,
            publisher_db sysname NOT NULL,
            publication sysname NOT NULL,
            subscription_type int NOT NULL,
            queue_id sysname NULL,
            update_mode tinyint default 0 not null, -- 0 = read only, 1 = sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued failover
            failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
            spid int NOT NULL,
            login_time datetime NOT NULL,
            allow_subscription_copy bit default 0 not null,
            attach_state int default 0 not null,    -- 0: not attached 1 attached but not processed 2 attached and processed.
            attach_version binary(16) default newid() not null,
            last_sync_status int NULL, -- allow null for upgrade
            last_sync_summary sysname NULL, -- allow null for upgrade
            last_sync_time datetime NULL, -- allow null for upgrade
            queue_server sysname NULL -- only used for MSMQ based updating subscribers
            )
            IF @@ERROR <> 0
                GOTO UNDO

            CREATE unique CLUSTERED INDEX ucMSsubscription_agents ON dbo.MSsubscription_agents
                (publication, publisher_db, publisher, subscription_type)

            CREATE INDEX ucMSsubscription_agents_id ON dbo.MSsubscription_agents
                (id)

            exec dbo.sp_MS_marksystemobject 'MSsubscription_agents'
            IF @@ERROR <> 0
                GOTO UNDO

            grant select on dbo.MSsubscription_agents to public
            IF @@ERROR <> 0
                GOTO UNDO
            COMMIT TRAN sp_MScreate_sub_tables
        END
        ELSE
        BEGIN
            
            -- table exists - add new columns
            
            if not exists (select * from sys.columns where
                object_id = object_id(N'dbo.MSsubscription_agents') and
                name = 'queue_server')
            begin
                BEGIN TRAN sp_MScreate_sub_tables
                SAVE TRAN sp_MScreate_sub_tables
                alter table dbo.MSsubscription_agents add queue_server sysname NULL
                if @@error != 0
                    goto UNDO
                exec @retcode = sys.sp_MSupdate_mqserver_subdb
                if @retcode != 0 or @@error != 0
                    goto UNDO
                COMMIT TRAN sp_MScreate_sub_tables
            end
        END -- MSsubscription_agents
        
        -- MSreplication_objects
        
        IF object_id(N'dbo.MSreplication_objects', 'U') is NULL
        BEGIN
            
            -- table does not exist - create the table
            
            BEGIN TRAN sp_MScreate_sub_tables
            SAVE TRAN sp_MScreate_sub_tables
            CREATE TABLE dbo.MSreplication_objects
            (
                publisher sysname NULL,
                publisher_db sysname NULL,
                publication sysname NULL,
                object_name    sysname NOT NULL,
                object_type    char(2) NOT NULL
                ,article sysname NULL
            )
            IF @@ERROR <> 0
                GOTO UNDO

            CREATE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
            exec dbo.sp_MS_marksystemobject 'dbo.MSreplication_objects'
            IF @@ERROR <> 0
                GOTO UNDO

            COMMIT TRAN sp_MScreate_sub_tables
        END -- MSreplication_objects
        ELSE
        BEGIN
            BEGIN TRAN sp_MScreate_sub_tables
            SAVE TRAN sp_MScreate_sub_tables

            if exists (select * from sys.indexes where name = N'ucMSreplication_objects'
            								and object_id = OBJECT_ID(N'dbo.MSreplication_objects')
            								and is_unique = 1)
            begin --remove uniqueness constraint
            	drop index ucMSreplication_objects on dbo.MSreplication_objects
            	CREATE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
            end

            
            -- table exists - add new columns
            
            if not exists (select * from sys.columns where
                object_id = object_id(N'dbo.MSreplication_objects') and
                name = 'article')
            begin
                alter table dbo.MSreplication_objects add article sysname NULL
                if @@error != 0
                    goto UNDO
            end
            COMMIT TRAN sp_MScreate_sub_tables
        END -- MSreplication_objects
    END -- @tran_sub_table = 1

    
    -- Is property table flag enabled
    
    if (@property_table = 1)
    begin
        
        -- MSsubscription_properties
        
        if object_id(N'dbo.MSsubscription_properties', 'U') is NULL
        BEGIN
            BEGIN TRAN sp_MScreate_sub_tables
            SAVE TRAN sp_MScreate_sub_tables

            exec @retcode= sys.sp_MScreate_subscription_properties_table
            if @@error <> 0 or @retcode <> 0 goto UNDO

            COMMIT TRAN sp_MScreate_sub_tables
        END -- MSsubscription_agents
        else
        begin
            
            -- NOTE : TEMPORARY - remove this block
            -- after the builds have stabilized
            -- this code is already in sp_vupgrade_MSsubscription_properties
            -- table exists - add new columns
            
            BEGIN TRAN sp_MScreate_sub_tables
            SAVE TRAN sp_MScreate_sub_tables
            if not exists (select * from sys.columns where
                object_id = object_id(N'dbo.MSsubscription_properties') and
                name = 'publisherlink')
            begin
                alter table dbo.MSsubscription_properties add publisherlink sysname NULL
                if @@error != 0
                    goto UNDO
            end
            if not exists (select * from sys.columns where
                object_id = object_id(N'dbo.MSsubscription_properties') and
                name = 'publisherlinkuser')
            begin
                alter table dbo.MSsubscription_properties add publisherlinkuser sysname NULL
                if @@error != 0
                    goto UNDO
            end
            COMMIT TRAN sp_MScreate_sub_tables
        end
    end -- @property_table = 1

    
    -- SQL Queue related tables
    
    IF @sqlqueue_table = 1
    BEGIN
        declare @folddata bit

        
        -- MSreplication_queue
        
        BEGIN TRAN sp_MScreate_sub_tables
        SAVE TRAN sp_MScreate_sub_tables
        if object_id('MSreplication_queue', 'U') is not NULL
        BEGIN
            
            -- table exists - check if we need to add columns
            
            if not exists (select * from sys.columns where
                    object_id = object_id(N'dbo.MSreplication_queue') and
                    name = 'cmdstate')
            BEGIN
                ALTER TABLE dbo.MSreplication_queue ADD cmdstate bit DEFAULT 0 NOT NULL
                IF @@ERROR <> 0
                    GOTO UNDO
            END

            
            -- change data column from text to varbinary(8000)
            -- SPECIAL CASE : since a simple ALTER does not work here
            -- we create a temp table to save the existing data and then
            -- recreate the table
            
            if exists (select * from sys.columns
                where object_id = object_id(N'dbo.MSreplication_queue') and
                name = 'data' and system_type_id = 34)
            begin
                
                -- save existing column data
                
                if exists (select * from dbo.MSreplication_queue)
                begin
                    select @folddata = 1
                    create table #olddata (
                 publisher                    sysname collate database_default not null ,
                        publisher_db                sysname collate database_default not null ,
                        publication                    sysname collate database_default not null ,
                        tranid                        sysname collate database_default not null ,
                        data                        varbinary(8000) NULL ,
                        datalen                     int,
                        commandtype                    int,
                        insertdate                    datetime ,
                        orderkey                    bigint,
                        cmdstate                    bit)

                    insert into #olddata
                        select publisher, publisher_db, publication, tranid, CAST(data as varbinary(8000)),
                                datalen, commandtype, insertdate, orderkey, cmdstate
                        from dbo.MSreplication_queue
                    if @@error != 0
                        goto UNDO
                end

                
                -- drop table
                
                DROP TABLE dbo.MSreplication_queue
                IF @@ERROR <> 0
                    GOTO UNDO
            end
        END

        
        -- Create table if it does not exist
        
        if object_id(N'dbo.MSreplication_queue', 'U') is NULL
        BEGIN
            CREATE TABLE dbo.MSreplication_queue (
                publisher                     sysname NOT NULL ,
                publisher_db                 sysname NOT NULL ,
                publication                 sysname NOT NULL ,
                tranid                         sysname NOT NULL ,
                data                         varbinary(8000) NULL ,
                datalen                     int DEFAULT 0 ,
                commandtype                 int NULL ,
                insertdate                     datetime DEFAULT GETDATE(),
                orderkey                    bigint IDENTITY(1,1) PRIMARY KEY,
                cmdstate                    bit DEFAULT 0 NOT NULL
            )
            IF @@ERROR <> 0
                GOTO UNDO

            CREATE NONCLUSTERED INDEX nc1MSreplication_queue ON
            MSreplication_queue(publisher, publisher_db, publication, tranid)
            IF @@ERROR <> 0
                GOTO UNDO

            EXEC @retcode = dbo.sp_MS_marksystemobject 'MSreplication_queue'
            if @retcode <> 0 or @@error <> 0
                GOTO UNDO

            
            -- Do we need to restore old data
            
            if (@folddata = 1)
            begin
                insert dbo.MSreplication_queue (publisher, publisher_db, publication, tranid, data,
                            datalen, commandtype, insertdate, cmdstate)
                    select publisher, publisher_db, publication, tranid, data,
                                datalen, commandtype, insertdate, cmdstate
                        from #olddata
                        order by orderkey
                if @@error != 0
                    goto UNDO

                drop table #olddata
                if @@error != 0
                    goto UNDO
            end
        END
        
        -- Table MSrepl_queuedtraninfo
        -- Create table if it does not exist
        
        if object_id(N'dbo.MSrepl_queuedtraninfo', 'U') is NULL
        BEGIN
            CREATE TABLE dbo.MSrepl_queuedtraninfo (
                publisher                     sysname NOT NULL ,
                publisher_db                     sysname NOT NULL ,
                publication                     sysname NOT NULL ,
                tranid                         sysname NOT NULL ,
                maxorderkey                    bigint NOT NULL,
                commandcount                bigint NOT NULL
            )
            IF @@ERROR <> 0
                GOTO UNDO
            CREATE CLUSTERED INDEX nc1MSrepl_queuedtraninfo ON
                    MSrepl_queuedtraninfo(publisher,publisher_db,publication,tranid)
            IF @@ERROR <> 0
                GOTO UNDO
            EXEC @retcode = dbo.sp_MS_marksystemobject 'MSrepl_queuedtraninfo'
            if @retcode <> 0 or @@error <> 0
                GOTO UNDO
            
            -- Populate this table as necessary
            
            exec @retcode = sp_populateqtraninfo
            if @retcode <> 0 or @@error <> 0
                GOTO UNDO
        END

        IF object_id(N'dbo.MSsubscription_articlecolumns', 'U') is NULL
        BEGIN
            create table dbo.MSsubscription_articlecolumns
            (
                agent_id int NOT NULL, -- related entry in MSsubscription_agents, can't add FK since source is not PK
                artid             int NOT NULL,
                colid             int NOT NULL
            )
            if @@error <> 0
                GOTO UNDO

            create unique clustered index idx_MSsubscription_articlecolumns
                        on MSsubscription_articlecolumns (agent_id, artid, colid)
            if @@error <> 0
                GOTO UNDO

            exec @retcode = dbo.sp_MS_marksystemobject 'MSsubscription_articlecolumns'
            if @retcode <> 0 or @@error <> 0
                GOTO UNDO
        END
        else
        begin
            if exists (select * from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id
                where object_id = object_id('dbo.MSsubscription_articlecolumns', 'U') and sc.name = N'colid' and st.name = N'smallint')
            begin
                if exists (select * from sysindexes where id = object_id('dbo.MSsubscription_articlecolumns') and name ='idx_MSsubscription_articlecolumns')
                    begin
                        drop index MSsubscription_articlecolumns.idx_MSsubscription_articlecolumns
                        if @@error <> 0 return 1
                    end

                alter table dbo.MSsubscription_articlecolumns alter column colid int NOT null
                if @@error <> 0 return 1

                create unique clustered index idx_MSsubscription_articlecolumns on MSsubscription_articlecolumns (agent_id, artid, colid)
                if @@error <> 0 return 1
            end
        end

        COMMIT TRAN sp_MScreate_sub_tables
    END -- @sqlqueue_table = 1

    IF @subscription_articles_table = 1
        or @p2p_table = 1
    BEGIN
        BEGIN TRAN sp_MScreate_sub_tables
        SAVE TRAN sp_MScreate_sub_tables

        
        -- Create system table MSsubscription_articles if it does not exist
        
        IF object_id(N'dbo.MSsubscription_articles', 'U') is NULL
        BEGIN
            CREATE TABLE dbo.MSsubscription_articles
            (
                agent_id        int NOT NULL,        -- related entry in MSsubscription_agents
                artid            int NOT NULL,        -- article id
                article            sysname,            -- article name
                dest_table        sysname,            -- destination table
                owner            sysname,            -- destination owner
                cft_table        sysname    NULL        -- conflict table
            )
            IF @@ERROR <> 0
                GOTO UNDO

            CREATE UNIQUE CLUSTERED INDEX ucMSsubscription_articles ON dbo.MSsubscription_articles(agent_id, artid)
            IF @@ERROR <> 0
                GOTO UNDO

            exec @retcode = dbo.sp_MS_marksystemobject 'MSsubscription_articles'
            if @retcode <> 0 or @@error <> 0
                GOTO UNDO
        END

        COMMIT TRAN sp_MScreate_sub_tables
    END

    -- setup the subscriber tables for PeerToPeer replication
    IF @p2p_table = 1
    BEGIN
        -- the proc wraps all table creation with a BEGIN TRAN and will
        -- roll it back internally if a failure occurs. That's why all
        -- we do here is return 1 if an error occurs instead of an "UNDO"
        exec @retcode = sys.sp_MScreate_peer_tables
        if @retcode <> 0 or @@error <> 0
            return 1
    END

    
    -- All done - we have already committed any
    -- open transactions if we have reached here
    
    return(0)

UNDO:
    
    -- Rollback to the savepoint and commit
    -- this way we can undo the operations
    -- contained in this SP and return error
    
    ROLLBACK TRAN sp_MScreate_sub_tables
    COMMIT TRAN sp_MScreate_sub_tables
    return(1)
END

 
Last revision 2008RTM
See also

  sp_addmergepullsubscription (Procedure)
sp_addpullsubscription (Procedure)
sp_addpullsubscription_agent (Procedure)
sp_addqueued_artinfo (Procedure)
sp_addsynctriggers (Procedure)
sp_link_publication (Procedure)
sp_MScreate_sub_tables (Procedure)
sp_MSreset_queue (Procedure)
sp_MSrestore_sub_merge (Procedure)
sp_replsqlqgetrows (Procedure)
sp_setreplfailovermode (Procedure)
sp_vupgrade_subscription_tables (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