Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_dist_tables

  No additional text.


Syntax
create procedure sys.sp_MScreate_dist_tables
AS
begin
    DECLARE @Cmd varchar(4000)

   -- Only sysadmin and db_owner of a distribution database are allowed
   -- to create replication tables in the distribution database
   if (is_srvrolemember('sysadmin') <> 1  and
       is_member('db_owner') <> 1)
   begin
      raiserror(21050, 14, -1)
      return (1)
   end

    
    -- create replmonitor role if it does not exist
    
    if not exists (select * from sys.database_principals
                where name = N'replmonitor' and type = 'R')
    begin
        EXEC  dbo.sp_addrole 'replmonitor'
    end

   /*
   ** Important:
   ** We use varbinary(16) for xact_id and xact_seqno, we don't want ending nulls
   ** to be truncated by the server
   **
   ** Also, in MSrepl_commands, we don't want ending space to be truncated.
   */
   SET ANSI_PADDING ON

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_version' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSrepl_version', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSrepl_version
      (
      major_version int NOT NULL,
      minor_version int NOT NULL,
      revision int NOT NULL,
      db_existed bit NULL
      )
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_version ON dbo.MSrepl_version
                    (major_version, minor_version, revision)

      exec dbo.sp_MS_marksystemobject 'MSrepl_version'

      INSERT INTO MSrepl_version VALUES (7,0,0,0)
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSpublisher_databases' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSpublisher_databases', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSpublisher_databases(
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        id int identity NOT NULL,
        publisher_engine_edition int null
    )
      exec dbo.sp_MS_marksystemobject 'MSpublisher_databases'

      raiserror('Creating clustered index ucMSpublisher_databases', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSpublisher_databases ON dbo.MSpublisher_databases
     (publisher_id, publisher_db, id)
   END
   ELSE
   BEGIN
		IF NOT EXISTS (select * from sys.columns
        where name = 'publisher_engine_edition'
        and object_id=object_id('MSpublisher_databases'))
      BEGIN
         ALTER TABLE MSpublisher_databases ADD publisher_engine_edition int null
      END
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSpublications' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSpublications', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSpublications (
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        publication sysname NOT NULL,
        publication_id int identity NOT NULL,   -- This id IS NOT the same as the SQL Server publisher's
        publication_type int NOT NULL,          -- 0 = Snapshot 1 = Transactional
        thirdparty_flag bit NOT NULL,           -- 0 = SQL Server 1 = Third Party
        independent_agent bit NOT NULL,
        immediate_sync bit NOT NULL,
        allow_push bit NOT NULL,
        allow_pull bit NOT NULL,
        allow_anonymous bit NOT NULL,
        description nvarchar(255) NULL,
        vendor_name nvarchar(100) NULL,
        retention   int NULL,
        sync_method int default 0 NOT NULL,
        allow_subscription_copy bit default 0 not null,
        thirdparty_options int null,
   allow_queued_tran bit default 0 not null,
        options int default 0 not null,
        retention_period_unit	tinyint default 0 not null,
        allow_initialize_from_backup	bit				not null default 0,
        min_autonosync_lsn      varbinary(16) NULL
        )
      exec dbo.sp_MS_marksystemobject 'MSpublications'

     -- publication_id needs to be the first columns in the index. It
     -- is used in sp_MSmaximum_cleanup_seqno.
      raiserror('Creating clustered index ucMSpublications', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSpublications ON dbo.MSpublications
     (publication_id,  publication, publisher_db, publisher_id)
      CREATE UNIQUE INDEX uc2MSpublications ON dbo.MSpublications
     (publication, publisher_db, publisher_id)
   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from sys.columns
        where name = 'sync_method'
        and object_id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD sync_method int default 0 not null
      END

      IF NOT EXISTS (select * from sys.columns
        where name = 'allow_subscription_copy'
        and object_id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD allow_subscription_copy bit default 0 not null
      END
      IF NOT EXISTS (select * from sys.columns
        where name = 'thirdparty_options'
        and object_id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD thirdparty_options int null
      END

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

      IF NOT EXISTS (select * from sys.columns
        where name = 'options'
        and object_id=object_id('MSpublications'))
      BEGIN
         exec ('ALTER TABLE MSpublications ADD options int default 0 not null')
         -- Set enabled for het sub bit in options column if sync_method = 1
         exec ('update MSpublications set options = 0x4 where sync_method = 1')
      END

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

      IF NOT EXISTS (select * from sys.columns
        where name = 'min_autonosync_lsn'
        and object_id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD min_autonosync_lsn varbinary(16) NULL
      END

    END


	IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSarticles' and type = 'U')
	BEGIN
		/****************************************************************************/
		raiserror('Creating table MSarticles', 0,1)
		/****************************************************************************/
		CREATE TABLE dbo.MSarticles (
			publisher_id smallint NOT NULL,
			publisher_db sysname NULL,
			publication_id int NOT NULL,
			article sysname NOT NULL,
			article_id int NOT NULL,                            -- This id is the same as a SQL Server Publisher's
			destination_object sysname NULL,
			source_owner    sysname NULL,
			source_object sysname NULL,
			description nvarchar(255) NULL,
			destination_owner sysname NULL
		)

		exec dbo.sp_MS_marksystemobject 'MSarticles'

	  	raiserror('Creating clustered index ucMSarticles', 0,1)
	  	CREATE UNIQUE CLUSTERED INDEX ucMSarticles ON dbo.MSarticles (publisher_db, publisher_id, article_id, article, publication_id)
	END
	ELSE
	BEGIN
		IF NOT EXISTS(select * from sys.columns
						where object_id = object_id(N'MSarticles')
							and name = N'destination_owner')
		BEGIN
			ALTER TABLE MSarticles ADD destination_owner sysname NULL
		END
	END

    IF NOT EXISTS( SELECT * from sys.objects WHERE name = 'MSsync_states' )
    BEGIN
        CREATE TABLE dbo.MSsync_states
        (
            publisher_id smallint not null,
            publisher_db sysname not null,
            publication_id int not null
        )
        exec dbo.sp_MS_marksystemobject 'MSsync_states'

        CREATE UNIQUE CLUSTERED INDEX ucMSsyncstates on dbo.MSsync_states
        ( publisher_id, publisher_db, publication_id )
    END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsubscriptions' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSsubscriptions', 0,1)
      /****************************************************************************/
       CREATE TABLE dbo.MSsubscriptions
      (
        publisher_database_id int NOT NULL,         -- Used to reference MSrepl_transactions and MSrepl_commands
        publisher_id smallint NOT NULL,
        publisher_db sysname NOT NULL,
        publication_id int NOT NULL,
        article_id int NOT NULL,
        subscriber_id smallint NOT NULL,
        subscriber_db sysname NOT NULL,
        subscription_type int NOT NULL,         -- 0 = push, 1 = pull, 2 = anonymous
        sync_type tinyint NOT NULL,                 -- 1 = automatic 2 = no sync
        status tinyint NOT NULL,                        -- 0 = inactive, 1 = subscribed, 2 = active
        subscription_seqno varbinary(16) NOT NULL,  -- publisher's database sequence number
        snapshot_seqno_flag bit NOT NULL,               -- 1 if subscription_seqno is the snapshot seqno
        independent_agent bit NOT NULL,             -- Value carried over from MSpublications
        subscription_time datetime NOT NULL,
        loopback_detection bit NOT NULL,
        agent_id int NOT NULL,
        update_mode tinyint NOT NULL,  -- 0 = read only, 1 = sync tran, 2 = queued tran, 3 = failover, 4 = sqlqueued tran,
                                                       -- 5 = sqlqueued failover, 6 = sqlqueued qfailover, 7 = qfailover
        publisher_seqno varbinary(16) NOT NULL,
        ss_cplt_seqno   varbinary(16) NOT NULL,
        nosync_type     tinyint DEFAULT 0   NOT NULL -- 0(none), 1(replication support only), 2(initialize with backup), 3(initialize from lsn)
      )

      exec dbo.sp_MS_marksystemobject 'MSsubscriptions'

      raiserror('Creating clustered index ucMSsubscirptions', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriptions ON dbo.MSsubscriptions
      (agent_id, article_id)

      -- Index used by sp_MSdelete_publisherdb_trans
      raiserror('Creating index iMSsubscriptions', 0,1)
      CREATE INDEX iMSsubscriptions ON dbo.MSsubscriptions
      (publisher_database_id, article_id, subscriber_id, subscriber_db, publication_id, publisher_db, publisher_id)
    END
    ELSE
    BEGIN
        if not exists ( select * from sys.columns
                        where object_id = object_id( N'MSsubscriptions' )
                        and name = N'ss_cplt_seqno' )
        begin
            exec ('alter table MSsubscriptions add ss_cplt_seqno varbinary(16) NULL')
            exec ('update MSsubscriptions set ss_cplt_seqno = publisher_seqno')
            exec ('alter table MSsubscriptions alter column ss_cplt_seqno varbinary(16) NOT NULL')
        end
    END

   -- For beta 3 upgrade, we need to create new index here.
   if not exists (select * from sysindexes where name = 'iMSsubscriptions2')
   begin
      -- Index used by sp_MSdelete_publisherdb_trans
      raiserror('Creating index iMSsubscriptions2', 0,1)
      CREATE INDEX iMSsubscriptions2 ON dbo.MSsubscriptions
      (publisher_database_id, subscription_seqno)
   end


   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_subscriptions' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSmerge_subscriptions', 0,1)
      /****************************************************************************/
       CREATE TABLE dbo.MSmerge_subscriptions
      (
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        publication_id int NOT NULL,
        subscriber_id smallint NULL,
        subscriber_db sysname NULL,
        subscription_type int NULL,         -- 0 = push, 1 = pull, 2 = anonymous
        sync_type tinyint NOT NULL,                 -- 1 = automatic 2 = no sync
        status tinyint NOT NULL,                        -- 0 = inactive, 1 = subscribed, 2 = active
        subscription_time datetime NOT NULL,
        description nvarchar(255) NULL,
        publisher sysname NULL,
        subscriber sysname NULL,
        subid uniqueidentifier NOT NULL,
        subscriber_version int NULL
      )
      exec dbo.sp_MS_marksystemobject 'MSmerge_subscriptions'

      raiserror('Creating clustered index ucMSmerge_subscriptions', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSmerge_subscriptions ON dbo.MSmerge_subscriptions(subid)

      CREATE UNIQUE INDEX unc1MSmerge_subscriptions ON dbo.MSmerge_subscriptions
		(subscriber, subscriber_db, publisher_id, publisher_db, publication_id)

   END
   else
	begin
	    /*
	     * Call proc to upgrade MSmerge_subscriptions
	    */
		exec sp_MSmerge_subscriptions_upgrade
	    if @@error <> 0
	       	return(1)
	
   END


   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_transactions' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSrepl_transactions', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSrepl_transactions
      (
      publisher_database_id int NOT NULL,
      xact_id varbinary(16) NULL,
      xact_seqno varbinary (16 )  NOT NULL,
      entry_time datetime  NOT NULL
      )

      exec dbo.sp_MS_marksystemobject 'MSrepl_transactions'

      raiserror('Creating clustered index usMSrepl_transactions', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_transactions ON dbo.MSrepl_transactions
         (publisher_database_id, xact_seqno)
         WITH STATISTICS_NORECOMPUTE

      CREATE STATISTICS stat_publisher_database_id
         ON MSrepl_transactions (publisher_database_id)
        WITH NORECOMPUTE

      CREATE STATISTICS stat_xact_id
         ON MSrepl_transactions (xact_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_xact_seqno
         ON MSrepl_transactions (xact_seqno)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_entry_time
         ON MSrepl_transactions (entry_time)
         WITH NORECOMPUTE

   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_commands' and type = 'U')
   BEGIN

      /****************************************************************************/
      raiserror('Creating table MSrepl_commands', 0,1)
      /****************************************************************************/


      CREATE TABLE dbo.MSrepl_commands (
	      publisher_database_id int not null,
	      xact_seqno varbinary(16) not null,
	      type int not null,
	      article_id int not null,
	      originator_id int not null,
	      command_id int not null,
	      partial_command bit not null,
	      command varbinary(1024) NULL,
	      hashkey int default 0,
	      originator_lsn varbinary(16) NULL
      )


      exec dbo.sp_MS_marksystemobject 'MSrepl_commands'

      raiserror('Creating clusterd index ucMSrepl_commands', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_commands ON dbo.MSrepl_commands
         (publisher_database_id, xact_seqno, command_id)
         WITH STATISTICS_NORECOMPUTE

	  -- CREATE INDEX uncMSrepl_commands_originator ON dbo.MSrepl_commands
	  -- (originator_id, originator_lsn)
	
      CREATE STATISTICS stat_xact_seqno
         ON MSrepl_commands (xact_seqno)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_type
         ON MSrepl_commands (type)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_article_id
         ON MSrepl_commands (article_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_originator_id
         ON MSrepl_commands (originator_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_command_id
         ON MSrepl_commands (command_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_partial_command
         ON MSrepl_commands (partial_command)
         WITH NORECOMPUTE

   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from sys.columns
        where name = 'hashkey'
        and object_id=object_id('MSrepl_commands'))
      BEGIN
         ALTER TABLE MSrepl_commands ADD hashkey int default 0
      END

      IF NOT EXISTS (select * from sys.columns
        where name = 'originator_lsn'
        and object_id=object_id('MSrepl_commands'))
      BEGIN
         ALTER TABLE MSrepl_commands ADD originator_lsn varbinary(16) NULL
      END
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_originators' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSrepl_orginators', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSrepl_originators
      (
	      id 					int identity not null,
	      publisher_database_id int not null,
	      srvname   			sysname not null,
	      dbname    			sysname not null,
		  publication_id 		int NULL,
		  dbversion 			int NULL
      )

      exec dbo.sp_MS_marksystemobject 'MSrepl_originators'

      raiserror('Creating clustered index usMSrepl_originators', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_originators ON dbo.MSrepl_originators
         (id, srvname, dbname, publication_id, dbversion)
   END
   ELSE
   BEGIN
   	  IF NOT EXISTS (select * from sys.columns
        where name = 'publication_id'
        and object_id=object_id('MSrepl_originators'))
      BEGIN
         ALTER TABLE MSrepl_originators ADD publication_id int NULL
      END

	  IF NOT EXISTS (select * from sys.columns
        where name = 'dbversion'
        and object_id=object_id('MSrepl_originators'))
      BEGIN
         ALTER TABLE MSrepl_originators ADD dbversion int NULL
      END

	  raiserror('Creating clustered index usMSrepl_originators', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_originators ON dbo.MSrepl_originators
         (id, srvname, dbname, publication_id, dbversion)
	  WITH DROP_EXISTING
   END
	
   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsubscriber_info' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSsubscriber_info', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSsubscriber_info
      (
      publisher  sysname NOT NULL,
      subscriber  sysname NOT NULL,
      type tinyint NOT NULL,           /* 0: MS SQL Server 1: ODBC Data Source */
      login sysname NULL,
      password nvarchar(524) NULL,
      description nvarchar(510) NULL,
      security_mode int NOT NULL
      )

      exec dbo.sp_MS_marksystemobject 'MSsubscriber_info'

      raiserror('Creating clustered index ucMSsubscriber_info', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_info ON dbo.MSsubscriber_info
     (publisher, subscriber)
   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from sys.columns
        where name = 'password'
        and object_id=object_id('MSsubscriber_info'))
      BEGIN
         ALTER TABLE MSsubscriber_info ADD password nvarchar (524) NULL
      END
      ELSE
      BEGIN
         ALTER TABLE MSsubscriber_info ALTER COLUMN password nvarchar (524) NULL
      END

      IF NOT EXISTS (select * from sys.columns
        where name = 'description'
        and object_id=object_id('MSsubscriber_info'))
      BEGIN
         ALTER TABLE MSsubscriber_info ADD description nvarchar (510) NULL
         UPDATE MSsubscriber_info SET description = 'SQL Server 6.0'
      END
      ELSE
      BEGIN
         ALTER TABLE MSsubscriber_info ALTER COLUMN description nvarchar (510) NULL
      END
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsubscriber_schedule' and type = 'U')
   BEGIN
         raiserror('Creating table MSsubscriber_schedule', 0,1)

        CREATE TABLE dbo.MSsubscriber_schedule
        (
            publisher  sysname NOT NULL,
            subscriber  sysname NOT NULL,
            agent_type      smallint NOT NULL,   -- 0 for distribution agent, 1 for merge agent
            frequency_type int NOT NULL,
            frequency_interval int NOT NULL,
            frequency_relative_interval int NOT NULL,
            frequency_recurrence_factor int NOT NULL,
            frequency_subday int NOT NULL,
            frequency_subday_interval int NOT NULL,
            active_start_time_of_day int NOT NULL,
            active_end_time_of_day int NOT NULL,
            active_start_date int NOT NULL,
            active_end_date int NOT NULL
        )
      exec dbo.sp_MS_marksystemobject 'MSsubscriber_schedule'

        CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_schedule ON dbo.MSsubscriber_schedule
     (publisher, subscriber, agent_type)

    END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsnapshot_history' and type = 'U')
   BEGIN
        /****************************************************************************/
         raiserror('Creating table MSsnapshot_history', 0,1)
        /****************************************************************************/
        CREATE TABLE dbo.MSsnapshot_history
        (
        agent_id int NOT NULL,
        runstatus int NOT NULL,
        start_time datetime NOT NULL,
        time datetime NOT NULL,
        duration int NOT NULL,
        comments nvarchar(1000) NOT NULL,

        -- Session summary statistics
        delivered_transactions int NOT NULL,
        delivered_commands int NOT NULL,
        delivery_rate float NOT NULL,

        error_id int NOT NULL,
        timestamp NOT NULL
    )

      exec dbo.sp_MS_marksystemobject 'MSsnapshot_history'

     raiserror('Creating clustered index ucMSsnapshot_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSsnapshot_history ON dbo.MSsnapshot_history
        (agent_id, timestamp, start_time, time)

   END
   ELSE
   BEGIN
       if exists (select * from sysindexes where name = 'nc1MSsnapshot_history' and id=object_id('MSsnapshot_history'))
	begin
	    drop index MSsnapshot_history.nc1MSsnapshot_history
	end
       if exists (select * from sys.columns where name = 'comments' and object_id=object_id('MSsnapshot_history'))
       begin
	    alter table MSsnapshot_history alter column comments nvarchar(1000) NOT NULL
       end
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSlogreader_history' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSlogreader_history', 0,1)
        /****************************************************************************/
        CREATE TABLE dbo.MSlogreader_history
        (
        agent_id int NOT NULL,
        runstatus int NOT NULL,
        start_time datetime NOT NULL,
        time datetime NOT NULL,
        duration int NOT NULL,
        comments nvarchar(4000) NOT NULL,
        xact_seqno varbinary(16) NULL,

        -- Session summary statistics
        delivery_time int NOT NULL,
        delivered_transactions int NOT NULL,
        delivered_commands int NOT NULL,
        average_commands int NOT NULL,
        delivery_rate float NOT NULL,
        delivery_latency int NOT NULL,

        error_id int NOT NULL,
        timestamp NOT NULL,
        updateable_row bit NOT NULL default 0
        )

      exec dbo.sp_MS_marksystemobject 'MSlogreader_history'

    raiserror('Creating clustered index ucMSlogreader_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSlogreader_history ON dbo.MSlogreader_history
        (agent_id, timestamp, runstatus, start_time, time)

   END
   ELSE
   BEGIN
        if exists (select * from sysindexes where name = 'nc1MSlogreader_history' and id=object_id('MSlogreader_history'))
	begin
	    drop index MSlogreader_history.nc1MSlogreader_history
	end
       if exists (select * from sys.columns where name = 'comments' and object_id=object_id('MSlogreader_history'))
       begin
	    alter table MSlogreader_history alter column comments nvarchar(4000) NOT NULL
       end	
       if not exists (select * from sys.columns where name = 'updateable_row' and object_id=object_id('MSlogreader_history'))
       begin
	    alter table MSlogreader_history add updateable_row bit NOT NULL default 0
       end	
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSdistribution_history' and type = 'U')
   BEGIN
        /****************************************************************************/
          raiserror('Creating table MSdistribution_history', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSdistribution_history
        (
        agent_id int NOT NULL,
        runstatus int NOT NULL,
        start_time datetime NOT NULL,
        time datetime NOT NULL,
        duration int NOT NULL,
        comments nvarchar(max) NOT NULL,
        xact_seqno varbinary(16) NULL,

        -- Current statistics
        current_delivery_rate float NOT NULL,
        current_delivery_latency int NOT NULL,

        -- Session summary statistics
        delivered_transactions int NOT NULL,
        delivered_commands int NOT NULL,
        average_commands int NOT NULL,
        delivery_rate float NOT NULL,
        delivery_latency int NOT NULL,

        -- Summary statistics across all sessions
        total_delivered_commands int NOT NULL,

        error_id int NOT NULL,
        updateable_row bit NOT NULL,
        timestamp NOT NULL
        )

    exec dbo.sp_MS_marksystemobject 'MSdistribution_history'

    raiserror('Creating clustered index ucMSdistribution_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSdistribution_history ON dbo.MSdistribution_history
        (agent_id, timestamp, runstatus, start_time, time)

   END
   ELSE
   BEGIN
		IF EXISTS (select * from sys.columns
			where name = 'comments'
			and object_id=object_id('MSdistribution_history'))
		BEGIN
			ALTER TABLE MSdistribution_history ALTER COLUMN comments nvarchar(max) NOT NULL
		END
		if exists (select * from sysindexes where name = 'nc1MSdistribution_history' and id=object_id('MSdistribution_history'))
		begin
		    drop index MSdistribution_history.nc1MSdistribution_history
		end
   END

   exec sys.sp_MScreate_common_dist_tables @subside = 0

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsnapshot_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSsnapshot_agents', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSsnapshot_agents
        (
        id                  int IDENTITY NOT NULL,
        name  nvarchar(100) NOT NULL,
        publisher_id        smallint NOT NULL,
        publisher_db        sysname NOT NULL,
        publication         sysname NOT NULL,
        publication_type    int NOT NULL,       -- 0 transactional 1 snapshot 2 merge
        local_job           bit NOT NULL,
        job_id              binary(16) NULL,
        profile_id  int     NOT NULL,
        dynamic_filter_login     sysname NULL, -- used only for merge dynamic snapshot. Should be null otherwise
        dynamic_filter_hostname  sysname NULL,  -- used only for merge dynamic snapshot. Should be null otherwise
        publisher_security_mode int NULL,
        publisher_login 		sysname NULL,
        publisher_password		nvarchar(524) NULL,
        job_step_uid		uniqueidentifier NULL
        )

      exec dbo.sp_MS_marksystemobject 'MSsnapshot_agents'

    raiserror('Creating clustered index ucMSsnapshot_agents', 0,1)
    CREATE CLUSTERED INDEX ucMSsnapshot_agents ON dbo.MSsnapshot_agents
        (publication, publisher_db, publisher_id)

    raiserror('Creatingindex iMSsnapshot_agents', 0,1)
    CREATE UNIQUE INDEX iMSsnapshot_agents ON dbo.MSsnapshot_agents
        (id)
   END
   ELSE
   BEGIN
		IF NOT EXISTS (select * from sys.columns
			where name = 'dynamic_filter_login'
			and object_id=object_id('MSsnapshot_agents'))
		BEGIN
			ALTER TABLE MSsnapshot_agents ADD dynamic_filter_login sysname NULL
		END

		IF NOT EXISTS (select * from sys.columns
			where name = 'dynamic_filter_hostname'
			and object_id=object_id('MSsnapshot_agents'))
		BEGIN
			ALTER TABLE MSsnapshot_agents ADD dynamic_filter_hostname sysname NULL
		END

		IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'publisher_security_mode')
		BEGIN
			ALTER TABLE MSsnapshot_agents ADD publisher_security_mode int NULL
		END

		IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'publisher_login')
		BEGIN
			ALTER TABLE MSsnapshot_agents ADD publisher_login sysname NULL
		END

		IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'publisher_password')
		BEGIN
			ALTER TABLE MSsnapshot_agents ADD publisher_password nvarchar(524) NULL
		END

		IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'job_step_uid')
		BEGIN
			ALTER TABLE MSsnapshot_agents ADD job_step_uid uniqueidentifier NULL
		END
   END


   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSlogreader_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSlogreader_agents', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSlogreader_agents
        (
        id                  int IDENTITY NOT NULL,
        name                nvarchar(100) NOT NULL,
        publisher_id        smallint NOT NULL,
        publisher_db        sysname NOT NULL,
        publication         sysname NOT NULL,-- Not used for SQL Server publisher
        local_job           bit NOT NULL,
        job_id              binary(16) NULL,
        profile_id  int     NOT NULL,
        publisher_security_mode smallint NULL,
        publisher_login 	sysname NULL,
        publisher_password 	nvarchar(524) NULL,
        job_step_uid		uniqueidentifier NULL
        )

      exec dbo.sp_MS_marksystemobject 'MSlogreader_agents'

    raiserror('Creating clustered index ucMSlogreader_agents', 0,1)
    CREATE CLUSTERED INDEX ucMSlogreader_agents ON dbo.MSlogreader_agents
        (publisher_db, publisher_id)

    raiserror('Creatingindex iMSlogreader_agents', 0,1)
    CREATE UNIQUE INDEX iMSlogreader_agents ON dbo.MSlogreader_agents
        (id)
   END
   ELSE
   BEGIN
	   IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'publisher_security_mode')
	   BEGIN
		   ALTER TABLE MSlogreader_agents ADD publisher_security_mode smallint NULL
	   END

	   IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'publisher_login')
	   BEGIN
		   ALTER TABLE MSlogreader_agents ADD publisher_login sysname NULL
	   END	

	   IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'publisher_password')
	   BEGIN	
		   ALTER TABLE MSlogreader_agents ADD publisher_password nvarchar(524) NULL
	   END
	
	   IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'job_step_uid')
	   BEGIN
		   ALTER TABLE MSlogreader_agents ADD job_step_uid uniqueidentifier NULL
	   END
   END

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSdistribution_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSdistribution_agents', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSdistribution_agents
        (
        id                  int IDENTITY NOT NULL,
	   	name 			    nvarchar(100) NOT NULL,
        publisher_database_id int NOT NULL,
        publisher_id        smallint NOT NULL,
        publisher_db        sysname NOT NULL,
        publication         sysname NOT NULL,
        subscriber_id       smallint NULL,
        subscriber_db       sysname NULL,
        subscription_type   int NOT NULL,
        local_job           bit NULL,
        job_id              binary(16) NULL,
        subscription_guid   binary(16) NOT NULL,
        profile_id  int     NOT NULL,
        anonymous_subid     uniqueidentifier NULL,
        subscriber_name     sysname NULL,
        virtual_agent_id    int NULL,
        anonymous_agent_id  int NULL,
        creation_date       datetime default (getdate()) not NULL,
        queue_id            sysname null,
        queue_status        int default 0 not null,
        offload_enabled     bit default 0 NOT NULL,
        offload_server      sysname NULL,
        dts_package_name    sysname NULL,
        dts_package_password nvarchar(524) NULL,
        dts_package_location int default 0 not null,
        sid                 varbinary(85) default suser_sid() not null,
        queue_server        sysname NULL,
		-- used for subscription based security
        subscriber_security_mode 	smallint NULL,
        subscriber_login			sysname NULL,
        subscriber_password 		nvarchar(524) NULL,
        reset_partial_snapshot_progress bit default 0 not null,
        job_step_uid				uniqueidentifier NULL
        ,subscriptionstreams tinyint NULL
        ,subscriber_type tinyint NULL
        ,subscriber_provider sysname NULL
        ,subscriber_datasrc nvarchar(4000) NULL
        ,subscriber_location nvarchar(4000) NULL
        ,subscriber_provider_string nvarchar(4000) NULL
        ,subscriber_catalog sysname NULL
        )

        exec dbo.sp_MS_marksystemobject 'MSdistribution_agents'

        raiserror('Creating clustered index ucMSdistribution_agents', 0,1)
        CREATE UNIQUE CLUSTERED INDEX ucMSdistribution_agents ON dbo.MSdistribution_agents
            (id)

        raiserror('Creatingindex iMSdistribution_agents', 0,1)
        CREATE INDEX iMSdistribution_agents ON dbo.MSdistribution_agents
            (publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)
   END

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'creation_date')
    begin
        alter table MSdistribution_agents add creation_date datetime default (getdate()) not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'offload_enabled')
    begin
        alter table MSdistribution_agents add offload_enabled bit default 0 not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'offload_server')
    begin
        alter table MSdistribution_agents add offload_server sysname null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'queue_id')
    begin
        alter table MSdistribution_agents add queue_id sysname null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'queue_status')
    begin
        alter table MSdistribution_agents add queue_status int default 0 not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'dts_package_name')
    begin
        alter table MSdistribution_agents add dts_package_name sysname null
    end
    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'dts_package_password')
    begin
        alter table MSdistribution_agents add dts_package_password nvarchar(524) null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'dts_package_location')
    begin
        alter table MSdistribution_agents add dts_package_location int default 0 not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'sid')
    begin
        -- set sid to be the upgrade user. db_owner or sysadmin
        -- can drop the agent entry
        alter table MSdistribution_agents add sid varbinary(85) default suser_sid() not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'queue_server')
    begin
        alter table MSdistribution_agents add queue_server sysname null
        EXEC sys.sp_MSupdate_mqserver_distdb
    end

	-- used for subscription based security
	if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_security_mode')
    begin
        alter table MSdistribution_agents add subscriber_security_mode smallint null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_login')
    begin
        alter table MSdistribution_agents add subscriber_login sysname null
    end

	if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_password')
    begin
        alter table MSdistribution_agents add subscriber_password nvarchar(524) null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'reset_partial_snapshot_progress')
    begin
        ALTER TABLE MSdistribution_agents ADD reset_partial_snapshot_progress bit default 0 not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'job_step_uid')
    begin
    	ALTER TABLE MSdistribution_agents ADD job_step_uid uniqueidentifier NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriptionstreams')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriptionstreams tinyint NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_type')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriber_type tinyint NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_provider')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriber_provider sysname NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_datasrc')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriber_datasrc nvarchar(4000) NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_location')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriber_location nvarchar(4000) NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_provider_string')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriber_provider_string nvarchar(4000) NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSdistribution_agents') and
        name = 'subscriber_catalog')
    begin
    	ALTER TABLE MSdistribution_agents ADD subscriber_catalog sysname NULL
    end

   IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSmerge_agents', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSmerge_agents
        (
        id                  int IDENTITY NOT NULL,
        name                nvarchar(100) NOT NULL,
        publisher_id        smallint NOT NULL,
        publisher_db        sysname NOT NULL,
        publication         sysname NOT NULL,
        subscriber_id       smallint NULL,
        subscriber_db       sysname NULL,
        local_job           bit NULL,
        job_id              binary(16) NULL,
        profile_id          int NULL,
        anonymous_subid     uniqueidentifier NULL,
        subscriber_name     sysname NULL,
        creation_date       datetime default (getdate()) not NULL,
        offload_enabled     bit default 0 NOT NULL,
        offload_server      sysname NULL,
        sid                 varbinary(85) default suser_sid() not null,
        -- used for subscription based security
        subscriber_security_mode 	smallint NULL,
        subscriber_login			sysname NULL,
        subscriber_password 		nvarchar(524) NULL,
        publisher_security_mode 	smallint NULL,
        publisher_login 			sysname NULL,
        publisher_password 			nvarchar(524) NULL,
        job_step_uid				uniqueidentifier NULL
        )

      exec dbo.sp_MS_marksystemobject 'MSmerge_agents'

		raiserror('Creating clustered index ucMSmerge_agents', 0,1)
		
		CREATE UNIQUE clustered INDEX ucMSmerge_agents ON dbo.MSmerge_agents(id)
		
		CREATE INDEX iMSmerge_agents ON dbo.MSmerge_agents
			(publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)
		
		CREATE INDEX i2MSmerge_agents ON dbo.MSmerge_agents
			(subscriber_id, subscriber_db)
	
    END
    else
    begin
		if EXISTS (select * from sys.indexes where name='ucMSmerge_agents' and object_id=object_id('MSmerge_agents'))
	    begin
		    drop index MSmerge_agents.ucMSmerge_agents
		end
		
		if EXISTS (select * from sys.indexes where name='iMSmerge_agents' and object_id=object_id('MSmerge_agents'))
	    begin
		    drop index MSmerge_agents.iMSmerge_agents
		end
		
		if EXISTS (select * from sys.indexes where name='i2MSmerge_agents' and object_id=object_id('MSmerge_agents'))
	    begin
		    drop index MSmerge_agents.i2MSmerge_agents
		end
		
		CREATE UNIQUE clustered INDEX ucMSmerge_agents ON dbo.MSmerge_agents(id)
		
		CREATE INDEX iMSmerge_agents ON dbo.MSmerge_agents
			(publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)
		
		CREATE INDEX i2MSmerge_agents ON dbo.MSmerge_agents
			(subscriber_id, subscriber_db)
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'creation_date')
    begin
        alter table MSmerge_agents add creation_date datetime default (getdate()) not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'offload_enabled')
    begin
        alter table MSmerge_agents add offload_enabled bit default 0 not null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'offload_server')
    begin
        alter table MSmerge_agents add offload_server sysname null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'sid')
    begin
        -- set sid to be the upgrade user. db_owner or sysadmin
        -- can drop the agent entry
        alter table MSmerge_agents add sid varbinary(85) default suser_sid() not null
    end

	-- used for subscription based security
	if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'subscriber_security_mode')
    begin
        alter table MSmerge_agents add subscriber_security_mode smallint null
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'subscriber_login')
    begin
        alter table MSmerge_agents add subscriber_login sysname null
    end

	if not exists (select * from sys.columns where
        object_id = object_id('MSmerge_agents') and
        name = 'subscriber_password')
    begin
        alter table MSmerge_agents add subscriber_password nvarchar(524) null
    end

   	if not exists (select * from sys.columns where
	   object_id = object_id('MSmerge_agents') and
	   name = 'publisher_security_mode')
    begin
	   alter table MSmerge_agents add publisher_security_mode smallint null
    end

    if not exists (select * from sys.columns where
	   object_id = object_id('MSmerge_agents') and
	   name = 'publisher_login')
    begin
	   alter table MSmerge_agents add publisher_login sysname null
    end

    if not exists (select * from sys.columns where
	   object_id = object_id('MSmerge_agents') and
	   name = 'publisher_password')
    begin
	   alter table MSmerge_agents add publisher_password nvarchar(524) null
    end

	if not exists (select * from sys.columns where
	   object_id = object_id('MSmerge_agents') and
	   name = 'job_step_uid')
    begin
	   alter table MSmerge_agents add job_step_uid uniqueidentifier NULL
    end

   	-- Need to re_visit the indexing of this table
	if not exists (select * from sys.objects where name = 'MSrepl_identity_range')
	begin 	
		raiserror('Creating table MSrepl_identity_range',0,1)

		create table dbo.MSrepl_identity_range (
			publisher 			  sysname not NULL,
			publisher_db			  sysname not NULL,
			tablename 			  sysname not NULL,
			identity_support		  int NULL,
			next_seed 			  bigint NULL, --resource control
			pub_range 			  bigint NULL, --publisher range
			range 				  bigint NULL, -- set by sp_addmergearticle
			max_identity			  bigint NULL, --resource control
			threshold 			  int NULL,   --in percentage, set by sp_addmergearticle
			current_max			  bigint NULL, --max value for current check constraint,set by sp_addmergearticle
			constraint pkMSrepl_identity_range
			    primary key(publisher,publisher_db,tablename)
		)
		exec dbo.sp_MS_marksystemobject MSrepl_identity_range
	end

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSpublication_access' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table MSpublication_access', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSpublication_access
        (
        publication_id      int NULL,       	-- Publication_id is unique in distribution database.
        login               sysname NOT NULL,	-- Logins in the publication access list, they nust
                                            	-- exist at both publisher and distributor side.
		sid					varbinary(85) NULL
        )

        exec dbo.sp_MS_marksystemobject 'MSpublication_access'

        raiserror('Creating clustered index ucMSpublication_access', 0,1)
        CREATE UNIQUE CLUSTERED INDEX ucMSpublication_access ON dbo.MSpublication_access
            (publication_id, sid)
    END

    -- For b3 upgrade
    if NOT EXISTS (select * from sys.columns where name='retention' and object_id=object_id('MSpublications'))
    begin
        alter table MSpublications add retention int NULL
        UPDATE msdb..MSdistributiondbs set max_distretention=72 where
            name = db_name() collate database_default
    end

    if NOT EXISTS (select * from sys.columns where name='retention_period_unit' and object_id=object_id('MSpublications'))
    begin
        alter table MSpublications add retention_period_unit tinyint default 0 not null
    end

    -- drop default_access column
    if exists (select * from sys.columns where object_id = object_id('MSpublications') and
        name = 'default_access')
    begin
        alter table MSpublications drop column default_access
    end

	IF NOT EXISTS (SELECT * FROM sys.columns WHERE name='sid' and object_id=object_id('MSpublication_access'))
    BEGIN
		ALTER TABLE dbo.MSpublication_access ADD sid varbinary(85) NULL

		EXEC('UPDATE MSpublication_access SET sid = SUSER_SID(login,0)')
    END

    -- Drop publisher_id column
    if EXISTS (select * from sys.columns where name='publisher_id' and object_id=object_id('MSpublication_access'))
    begin
        drop index MSpublication_access.ucMSpublication_access

        alter table MSpublication_access drop column publisher_id

        raiserror('Creating clustered index ucMSpublication_access', 0,1)
        CREATE CLUSTERED INDEX ucMSpublication_access ON dbo.MSpublication_access
        (publication_id, sid)
    end

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSqreader_agents' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table MSqreader_agents', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSqreader_agents
        (
        id                  int IDENTITY NOT NULL,
        name                nvarchar(100) NULL,
        job_id              binary(16) NULL,
        profile_id          int NULL,
        job_step_uid		uniqueidentifier NULL
        )

      exec dbo.sp_MS_marksystemobject 'MSqreader_agents'

    raiserror('Creating unique index ucMSqreader_agents', 0,1)
    CREATE UNIQUE INDEX ucMSqreader_agents ON dbo.MSqreader_agents
        (id)
   END

    -- add columns for existing table
    if not exists (select * from sys.columns where
        object_id = object_id('MSqreader_agents') and
        name = 'profile_id')
    begin
        alter table dbo.MSqreader_agents add profile_id int NULL
    end

   if not exists (select * from sys.columns where
	   object_id = object_id('MSqreader_agents') and
	   name = 'job_step_uid')
   begin
	   alter table dbo.MSqreader_agents add job_step_uid uniqueidentifier NULL
   end
		

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSqreader_history' and type = 'U')
    BEGIN
    /****************************************************************************/
    raiserror('Creating table MSqreader_history', 0,1)
    /****************************************************************************/

    CREATE TABLE dbo.MSqreader_history
    (
    agent_id int NOT NULL,
    publication_id int NULL,
    runstatus int NOT NULL,
    start_time datetime NOT NULL,
    time datetime NOT NULL,
    duration int NOT NULL,
    comments nvarchar(1000) NOT NULL,
    transaction_id nvarchar(40) NULL,
    transaction_status int NULL,
    transactions_processed int NULL DEFAULT 0,
    commands_processed int NULL DEFAULT 0,
    delivery_rate float NOT NULL DEFAULT 0.0,
    transaction_rate float NOT NULL DEFAULT 0.0,
    subscriber sysname NULL,
    subscriberdb sysname NULL,
    error_id int NULL,
    timestamp NOT NULL
    )

    exec dbo.sp_MS_marksystemobject 'MSqreader_history'

    raiserror('Creating clustered index ucMSqreader_history', 0,1)
    CREATE CLUSTERED INDEX ucMSqreader_history ON dbo.MSqreader_history
    (agent_id, timestamp, runstatus, start_time, time)
    END
    ELSE
    BEGIN
       if exists (select * from sys.columns where name = 'comments' and object_id=object_id('MSqreader_history'))
       begin
	    alter table MSqreader_history alter column comments nvarchar(1000) NOT NULL
       end	
    END

    -- alter column publication_id
    if not exists (select * from sys.columns where
        object_id = object_id('MSqreader_history') and
        name = 'publication_id')
    begin
        alter table dbo.MSqreader_history alter column publication_id int NULL
    end

    -- add columns for existing table
    if not exists (select * from sys.columns where
        object_id = object_id('MSqreader_history') and
        name = 'error_id')
    begin
        alter table dbo.MSqreader_history add error_id int NULL
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSqreader_history') and
        name = 'transactions_processed')
    begin
        alter table dbo.MSqreader_history add transactions_processed int NULL DEFAULT 0
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSqreader_history') and
        name = 'delivery_rate')
    begin
        alter table dbo.MSqreader_history add delivery_rate float NOT NULL DEFAULT 0.0
    end

    if not exists (select * from sys.columns where
        object_id = object_id('MSqreader_history') and
        name = 'transaction_rate')
    begin
        alter table dbo.MSqreader_history add transaction_rate float NOT NULL DEFAULT 0.0
    end

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_backup_lsns' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table MSrepl_backup_lsns', 0,1)
        /****************************************************************************/

        CREATE TABLE dbo.MSrepl_backup_lsns
        (
        publisher_database_id int NOT NULL,
        valid_xact_id varbinary(16) NULL,
        valid_xact_seqno varbinary (16 ) NULL,
        next_xact_id varbinary(16) NULL,
        next_xact_seqno varbinary (16 )  NULL
        )

        exec dbo.sp_MS_marksystemobject 'MSrepl_backup_lsns'

        raiserror('Creating clustered index ucMSrepl_backup_lsns', 0,1)
        CREATE UNIQUE CLUSTERED INDEX ucMSrepl_backup_lsns ON dbo.MSrepl_backup_lsns
            (publisher_database_id)

        if exists (select * from MSpublisher_databases)
    begin
            /****************************************************************************/
            raiserror('Upgrading MSrepl_backup_lsns', 0,1)
            /****************************************************************************/
            insert into MSrepl_backup_lsns select d.id,
                NULL, NULL, NULL, NULL from MSpublisher_databases d
            -- No need to set the lsns in the table since the 'sync with backup' option is
            -- new in 8.0
        end

    END

    
    -- Table for replication monitor thresholds for publications
    
    if (object_id('dbo.MSpublicationthresholds') is null)
    begin
        raiserror('Creating table MSpublicationthresholds', 0,1)
        create table dbo.MSpublicationthresholds
        (
            publication_id int not null
            ,metric_id int not null
            ,value sql_variant null
            ,shouldalert bit not null default 0
            ,isenabled bit not null default 0
        )
        exec dbo.sp_MS_marksystemobject 'MSpublicationthresholds'
        raiserror('Creating clustered index ucmspublicationthresholds', 0,1)
        create unique clustered index ucmspublicationthresholds
            on dbo.MSpublicationthresholds (publication_id,  metric_id)
        create nonclustered index nc1mspublicationthresholds
            on dbo.MSpublicationthresholds (publication_id)
        create nonclustered index nc2mspublicationthresholds
            on dbo.MSpublicationthresholds (metric_id)
    end

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishers' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table IHpublishers', 0,1)
        /****************************************************************************/

        create table IHpublishers
        (
		publisher_id		smallint			not null,	-- use same ID as dbo.sysservers.srvid
        vendor				sysname				not null,
        publisher_guid		uniqueidentifier	not null,
		flush_request_time	datetime			null,
		version				sysname				null
        -- ,CONSTRAINT pk_IHpublishers PRIMARY KEY (publisher_id)
        )

		exec dbo.sp_MS_marksystemobject 'IHpublishers'
    END

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishertables' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table IHpublishertables', 0,1)
        /****************************************************************************/

        create table IHpublishertables
        (
         table_id       int         IDENTITY	NOT NULL
        ,publisher_id   smallint    NOT NULL
        ,name           sysname     NOT NULL
        ,owner          sysname     NOT NULL
        -- ,CONSTRAINT pk_IHpublishertables PRIMARY KEY (table_id, publisher_id)
        -- ,CONSTRAINT fk_IHpublishertables_publisher FOREIGN KEY (publisher_id) REFERENCES IHpublishers (publisher_id)
        -- ,CONSTRAINT uk_IHpublishertables_name UNIQUE NONCLUSTERED (publisher_id, name, owner)
        )

        create unique clustered index idx_IHpublishertables_tableid ON IHpublishertables (table_id)
        create index idx_IHpublishertables_name ON IHpublishertables (name)

		exec dbo.sp_MS_marksystemobject 'IHpublishertables'
    END

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHarticles' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table IHarticles', 0,1)
        /****************************************************************************/

        create table IHarticles
        (
			article_id					int             NOT NULL IDENTITY,
			name						sysname         NOT NULL,
			publication_id				smallint     NOT NULL,
			table_id					int             NOT NULL,
			publisher_id				smallint        NOT NULL,
			creation_script				nvarchar(255)   NULL,
			del_cmd						nvarchar(255)   NULL,
			filter						int             NOT NULL,
			filter_clause				ntext           NULL,
			ins_cmd						nvarchar(255)   NULL,
			pre_creation_cmd			tinyint         NOT NULL,
			status						tinyint         NOT NULL,
			type						tinyint         NOT NULL,
			upd_cmd						nvarchar(255)   NULL,
			schema_option				binary(8)       NULL,
			dest_owner					sysname         NULL,
			dest_table					sysname         NOT NULL,
			tablespace_name				nvarchar(255)   NULL,
			objid						int             NULL,
			sync_objid					int             NULL,
			description					nvarchar(255)   NULL,
			publisher_status			int             NULL,
			article_view_owner			nvarchar(255)	NULL,
			article_view				nvarchar(255)	NULL,
			ins_scripting_proc			int             NULL,
			del_scripting_proc			int             NULL,
			upd_scripting_proc			int             NULL,
			custom_script				nvarchar(2048)  NULL,
			fire_triggers_on_snapshot	bit             NOT NULL DEFAULT 0,
			instance_id					int             NOT NULL DEFAULT 0,
			use_default_datatypes		bit				NOT NULL DEFAULT 0
			-- ,CONSTRAINT pk_IHarticles PRIMARY KEY (article_id)
			-- ,CONSTRAINT uk_IHarticles_article UNIQUE  (name, publication_id)
			-- ,CONSTRAINT fk_IHarticles_tableid FOREIGN KEY (table_id, publisher_id) REFERENCES IHpublishertables (table_id, publisher_id)
			)

        create unique clustered index idx_IHarticles_articleid ON IHarticles (article_id)
        create index idx_IHarticles_tableid ON IHarticles (table_id)
        create index idx_IHarticles_name ON IHarticles (name)

        exec dbo.sp_MS_marksystemobject 'IHarticles'
    END

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishercolumns' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table IHpublishercolumns', 0,1)
        /****************************************************************************/

        create table IHpublishercolumns
        (
         publishercolumn_id int             IDENTITY
        ,table_id           int             not null
        ,publisher_id       smallint        not null
        ,name               sysname         not null
        ,column_ordinal     int             not null
        ,type               varchar(255)    not null
        ,length             bigint          not null
        ,prec               int             null
        ,scale              int             null
        ,isnullable         bit             not null
        ,iscaptured         bit             not null -- Column is begin captured by tracking trigger but might not be in any articles
        -- ,CONSTRAINT pk_IHpublishercolumns PRIMARY KEY (publishercolumn_id)
        -- ,CONSTRAINT fk_IHpublishercolumns_tableid FOREIGN KEY (table_id, publisher_id) REFERENCES IHpublishertables (table_id, publisher_id)
        )

        create index idx_IHpublishercolumns_tableid on IHpublishercolumns (table_id)
        create index idx_IHpublishercolumns_name on IHpublishercolumns (name)
        create index idx_IHpublishercolumns_type on IHpublishercolumns (type)
        create index idx_IHpublishercolumns_pubcolumnid on IHpublishercolumns (publishercolumn_id)

        exec dbo.sp_MS_marksystemobject 'IHpublishercolumns'
    END

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHcolumns' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table IHcolumns', 0,1)
        /****************************************************************************/

        create table IHcolumns
        (
         column_id          int             IDENTITY
    ,publishercolumn_id int             not null
        ,name               sysname         not null
        ,article_id         int             not null
        ,column_ordinal     int             not null
        ,mapped_type        tinyint         not null
        ,mapped_length      bigint          null
        ,mapped_prec        int             null
        ,mapped_scale       int             null
        ,mapped_nullable	bit				default 1
        -- ,CONSTRAINT pk_IHcolumns PRIMARY KEY (column_id)
        -- ,CONSTRAINT uk_IHcolumns_pubcolid UNIQUE  (publishercolumn_id, article_id)
        -- ,CONSTRAINT uk_IHcolumns_name UNIQUE  (name, article_id)
        -- ,CONSTRAINT fk_IHcolumns_publishrecolumnid FOREIGN KEY(publishercolumn_id) REFERENCES IHpublishercolumns (publishercolumn_id)
        -- ,CONSTRAINT fk_IHcolumns_articleid FOREIGN KEY (article_id) REFERENCES IHarticles (article_id)
        )

        create clustered index idx_IHcolumns_pubcolumnid ON IHcolumns (publishercolumn_id)
        create index idx_IHcolumns_mappedtype ON IHcolumns (mapped_type)
        create index idx_IHcolumns_articleid ON IHcolumns (article_id)
        create index idx_IHcolumns_columnid ON IHcolumns (column_id)
        create index idx_IHcolumns_name ON IHcolumns (name)

        exec dbo.sp_MS_marksystemobject 'IHcolumns'
    END

    IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHindextypes' and type = 'U')
    BEGIN
        /****************************************************************************/
        raiserror('Creating table IHindextypes', 0,1)
        /****************************************************************************/

        create table IHindextypes
        (
         type   NVARCHAR(255)   NOT NULL
        -- ,CONSTRAINT pk_IHindextypes PRIMARY KEY (type)
        )

        insert into IHindextypes (type) values ('UNIQUE');
        insert into IHindextypes (type) values ('NONUNIQUE');

        exec dbo.sp_MS
 
Last revision SQL2008SP2
See also

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