Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_common_dist_tables

  No additional text.


Syntax
create procedure sys.sp_MScreate_common_dist_tables @subside bit as
begin
	declare @retcode int
	
	IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_sessions' and type = 'U')
	BEGIN
		if @subside = 0
		begin
			--please keep table definition in sync between if and else clauses
			CREATE TABLE dbo.MSmerge_sessions
			(
				session_id				int identity    NOT NULL,
				agent_id				int 			not NULL, 	
				start_time				datetime		NULL,
				end_time				datetime		NULL,
				duration				int				NULL, -- in seconds
				delivery_time           int				NOT NULL default 0,
				upload_time				int				NOT NULL default 0,
				download_time           int				NOT NULL default 0,
				schema_change_time      int				NOT NULL default 0,
				prepare_snapshot_time   int				NOT NULL default 0,
				delivery_rate           decimal(12,2)	NOT NULL default 0,
				time_remaining			int				NOT NULL default 0,
				percent_complete		decimal(5,2)	NOT NULL default 0,
				upload_inserts			int				NULL default 0,
				upload_updates			int				NULL default 0,
				upload_deletes			int				NULL default 0,
				upload_conflicts		int				NULL default 0,
				upload_rows_retried		int				NULL default 0,
				download_inserts		int				NULL default 0,
				download_updates		int				NULL default 0,
				download_deletes		int				NULL default 0,
				download_conflicts		int				NULL default 0,
				download_rows_retried	int				NULL default 0,
				schema_changes			int				NULL default 0,
				bulk_inserts			int				NULL default 0,
				metadata_rows_cleanedup	int				NULL default 0,
				runstatus               int             NOT NULL,
				estimated_upload_changes				int	NULL default 0,
				estimated_download_changes				int	NULL default 0,
				connection_type			int 			NULL default 1, -- 1 for LAN, 2 for DUN
				timestamp								not null,
				current_phase_id		int				NULL default 0, -- 1 for upload, 2 for download
				spid					smallint		NULL,
				spid_login_time			datetime		NULL
				--please keep table definition in sync between if and else clauses
			)
		end
		else
		begin
			-- forced to do two different create table statements, instead of 1 common
			-- create table followed by an alter table to add subid. the reason is that the proc that creates
			-- system tables on pub/sub runs into error from DDL trigger if an alter table is performed with
			-- ansi_padding on.
			--please keep table definition in sync between if and else clauses
			CREATE TABLE dbo.MSmerge_sessions
			(	
				subid					uniqueidentifier	NULL,
				session_id				int identity    NOT NULL,
				agent_id				int 			not NULL, 	
				start_time				datetime		NULL,
				end_time				datetime		NULL,
				duration				int				NULL, -- in seconds
				delivery_time           int				NOT NULL default 0,
				upload_time				int				NOT NULL default 0,
				download_time           int				NOT NULL default 0,
				schema_change_time      int				NOT NULL default 0,
				prepare_snapshot_time   int				NOT NULL default 0,
				delivery_rate           decimal(12,2)	NOT NULL default 0,       	
				time_remaining			int				NOT NULL default 0,
				percent_complete		decimal(5,2)	NOT NULL default 0,
				upload_inserts			int				NULL default 0,
				upload_updates			int				NULL default 0,
				upload_deletes			int				NULL default 0,
				upload_conflicts		int				NULL default 0,
				upload_rows_retried		int				NULL default 0,
				download_inserts		int				NULL default 0,
				download_updates		int				NULL default 0,
				download_deletes		int				NULL default 0,
				download_conflicts		int				NULL default 0,
				download_rows_retried	int				NULL default 0,
				schema_changes			int				NULL default 0,
				bulk_inserts			int				NULL default 0,
				metadata_rows_cleanedup	int				NULL default 0,
				runstatus               int             NOT NULL,
				estimated_upload_changes				int	NULL default 0,
				estimated_download_changes				int	NULL default 0,
				connection_type			int 			NULL default 1, -- 1 for LAN, 2 for DUN
				timestamp								not null,
				current_phase_id		int				NULL default 0, -- 1 for upload, 2 for download
				spid					smallint		NULL,
				spid_login_time			datetime		NULL
				--please keep table definition in sync between if and else clauses
			)
		end

        exec dbo.sp_MS_marksystemobject 'MSmerge_sessions'

        CREATE UNIQUE CLUSTERED INDEX ucMSmerge_sessions ON dbo.MSmerge_sessions (session_id)
        create nonclustered index nc1MSmerge_sessions on dbo.MSmerge_sessions(agent_id)
        create nonclustered index nc2MSmerge_sessions on dbo.MSmerge_sessions(start_time)
        create nonclustered index nc3MSmerge_sessions on dbo.MSmerge_sessions(end_time)
        create nonclustered index nc4MSmerge_sessions on dbo.MSmerge_sessions(timestamp)
    END
    else
    begin
        if NOT EXISTS (select * from sys.columns where name = 'prepare_snapshot_time' and object_id=object_id('dbo.MSmerge_sessions'))
        BEGIN
            ALTER TABLE dbo.MSmerge_sessions ADD prepare_snapshot_time int NOT NULL default 0
            if @@error<>0 goto error
        END

        IF NOT EXISTS (select * from sys.columns where name = 'spid' and object_id=object_id('MSmerge_sessions'))
        BEGIN
            ALTER TABLE MSmerge_sessions ADD spid smallint NULL, spid_login_time datetime NULL
            if @@error<>0 goto error
        END
    end

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_articlehistory' and type = 'U')
    BEGIN
        CREATE TABLE dbo.MSmerge_articlehistory
        (
            session_id				int             NOT NULL,
            phase_id				int				NULL, -- 1 for upload, 2 for download
            article_name			sysname			NULL,
            start_time				datetime		NOT NULL,
            duration				int				NULL,
            inserts					int 			not NULL default 0,
            updates					int 			not NULL default 0,
            deletes					int 			not NULL default 0,
            conflicts				int 			not NULL default 0,
            rows_retried			int 			not NULL default 0,
            percent_complete		decimal(5,2)	not null default 0,
            estimated_changes		int				NULL default 0,
            relative_cost			decimal(12,2)	not null default 0	-- not decimal(5,2) because it temporarily holds absolute time values and
                                                                        -- not percentages. % are calculated at the end of session.
        )

        exec dbo.sp_MS_marksystemobject 'MSmerge_articlehistory'
        create unique clustered index ucMSmerge_articlehistory ON dbo.MSmerge_articlehistory (session_id, phase_id, article_name)
        create nonclustered index nc1MSmerge_articlehistory on dbo.MSmerge_articlehistory(start_time)
    END

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_errors' and type = 'U')
    BEGIN
        CREATE TABLE dbo.MSrepl_errors
        (
            id                  int NOT NULL,
            time                datetime NOT NULL,
            error_type_id       int NULL,
            source_type_id      int NULL,
            source_name         nvarchar(100) NULL,
            error_code          sysname NULL,
            error_text          ntext NULL,
            xact_seqno          varbinary(16) NULL,
            command_id          int NULL,
            session_id          int null default 0
        )

        exec dbo.sp_MS_marksystemobject 'MSrepl_errors'

        CREATE CLUSTERED INDEX ucMSrepl_errors ON dbo.MSrepl_errors
            (id, time) -- WITH ALLOW_DUP_ROW
        create index nc1MSrepl_errors on MSrepl_errors (session_id)
    END
    ELSE
    BEGIN
        IF NOT EXISTS (select * from sys.columns
        where name = 'xact_seqno'
        and object_id=object_id('MSrepl_errors'))
            ALTER TABLE MSrepl_errors ADD xact_seqno varbinary(16) NULL

     IF NOT EXISTS (select * from sys.columns
        where name = 'command_id'
        and object_id=object_id('MSrepl_errors'))
            ALTER TABLE MSrepl_errors ADD command_id int NULL

        IF NOT EXISTS (select * from sys.columns
        where name = 'session_id'
        and object_id=object_id('MSrepl_errors'))
            ALTER TABLE MSrepl_errors ADD session_id int null default 0

        if not exists (select * from sysindexes where name='nc1MSrepl_errors')
        begin
            create nonclustered index nc1MSrepl_errors on MSrepl_errors (session_id)
        end
   END

	exec @retcode = sys.sp_MSupgrade_merge_history90
	if @@error <> 0 or @retcode <> 0
		goto error
	
	-- If the MSmerge_history table is in the pre-Yukon format, drop and recreate it.
	if exists (select * from sys.columns
									where name = 'publisher_insertcount' and object_id=object_id('MSmerge_history'))
		drop table dbo.MSmerge_history
											
	IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_history' and type = 'U')
	BEGIN
		CREATE TABLE dbo.MSmerge_history
		(
			session_id  int NULL,
			agent_id    int not NULL,
			comments    nvarchar(1000) NOT NULL,
			error_id    int NOT NULL,
			timestamp   NOT NULL,
			updateable_row  bit  NOT NULL default 0,
			time    datetime not null default getdate()
		)
		exec dbo.sp_MS_marksystemobject 'MSmerge_history'

		Create UNIQUE CLUSTERED INDEX ucMSmerge_history ON dbo.MSmerge_history (agent_id, timestamp)
		create nonclustered index nc1MSmerge_history on MSmerge_history(session_id, timestamp)
		create nonclustered index nc2MSmerge_history on MSmerge_history(timestamp)
	END
	ELSE
	BEGIN
       IF NOT EXISTS (select * from sys.columns
        where name = 'session_id'
        and object_id=object_id('MSmerge_history'))
       BEGIN
           ALTER TABLE MSmerge_history ADD session_id int NULL
       END

       IF NOT EXISTS (select * from sys.columns
        where name = 'updateable_row'
        and object_id=object_id('MSmerge_history'))
       BEGIN
           ALTER TABLE MSmerge_history ADD updateable_row bit not NULL default 0
       END

       IF NOT EXISTS (select * from sys.columns
        where name = 'time'
        and object_id=object_id('MSmerge_history'))
       BEGIN
           ALTER TABLE MSmerge_history ADD time datetime not null default getdate()
       END

       if not exists (select * from sysindexes where name='ucMSmerge_history')
       begin
           Create UNIQUE CLUSTERED INDEX ucMSmerge_history ON dbo.MSmerge_history (agent_id, timestamp)
       end

       if exists( select * from sysindexes where name = 'nc1MSmerge_history' AND
			id = OBJECT_ID('MSmerge_history'))
  	begin
		drop index dbo.MSmerge_history.nc1MSmerge_history
	end
	if exists(select * from sys.columns where object_id = object_id('MSmerge_history') and name = 'session_id')
	  and exists (select * from sys.columns where object_id = object_id('MSmerge_history') and name = 'timestamp')
       	create nonclustered index nc1MSmerge_history on MSmerge_history(session_id, timestamp)


       if not exists (select * from sysindexes where name='nc2MSmerge_history')
       begin
           create nonclustered index nc2MSmerge_history on MSmerge_history(timestamp)
       end

	END


    if @subside = 0
    begin
        if object_id('dbo.MSmerge_identity_range_allocations', 'U') is NULL
        begin
            CREATE TABLE dbo.MSmerge_identity_range_allocations
            (
                publisher_id        smallint NOT NULL,
                publisher_db        sysname NOT NULL,
                publication         sysname NOT NULL,
                article             sysname NOT NULL,
                subscriber          sysname NULL,
                subscriber_db       sysname NULL,
                is_pub_range        bit NOT NULL,
                ranges_allocated    tinyint 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,
                max_used            numeric(38,0) NOT NULL,
                time_of_allocation  datetime default getdate()
            )
            exec dbo.sp_MS_marksystemobject 'MSmerge_identity_range_allocations'

            CREATE CLUSTERED INDEX cMSmerge_identity_range_allocations ON dbo.MSmerge_identity_range_allocations
                (publisher_id, publisher_db, publication, article) -- it is not a unique index.
        end
    end

    return 0

error:
    return 1
end

 
Last revision SQL2008SP2
See also

  sp_MScreate_dist_tables (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSmerge_create_sub_table (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