Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_vupgrade_distdb

  No additional text.


Syntax
create procedure sys.sp_vupgrade_distdb
as
begin
    set nocount on

    declare @database sysname
                ,@table_name sysname
                ,@retcode integer
                ,@column_name nvarchar(128)
                ,@column_type nvarchar(128)
                ,@alter_cmd nvarchar(max)

    -- raiserror('sp_vupgrade_publisher', 0,1) with nowait
    SELECT @database = DB_NAME()

   	
    -- drop obsolete local procedures
    
    if object_id(N'dbo.sp_MSadd_repl_commands10', 'local') is not null
        drop procedure dbo.sp_MSadd_repl_commands10
    if object_id(N'dbo.sp_MSdrop_agent_entry', 'local') is not null
        drop procedure dbo.sp_MSdrop_agent_entry
    if object_id(N'dbo.sp_MSgetlasthistorytimestamp', 'local') is not null
        drop procedure dbo.sp_MSgetlasthistorytimestamp

    /*
     * MSrepl_version
     * Add new unique idx for correctness iff there are no uniqueness violations.
    */
    select @table_name = N'MSrepl_version'
    if object_id('MSrepl_version') is not NULL
    begin
        if exists( select major_version, minor_version, revision
            from MSrepl_version
            group by major_version, minor_version, revision
            having count(*) > 1 )
            raiserror(21203, 10, 9, @table_name)
        else
            if not exists( select * from sysindexes where name = 'ucMSrepl_version' AND
                id = OBJECT_ID('MSrepl_version') )
                create unique clustered index ucMSrepl_version on dbo.MSrepl_version
                    (major_version, minor_version, revision)
    end
    
    -- MSrepl_version
    -- Add new unique idx iff there are no uniqueness violations.
    
    select @table_name = N'MSpublications'
    if object_id('dbo.MSpublications') is not NULL
    begin
        if exists( select publication, publisher_db, publisher_id
            from dbo.MSpublications
            group by publication, publisher_db, publisher_id
            having count(*) > 1 )
            raiserror(21203, 10, 9, @table_name)
        else
            if not exists( select * from sysindexes where name = 'uc2MSpublications' AND
                id = OBJECT_ID('dbo.MSpublications') )
              CREATE UNIQUE INDEX uc2MSpublications ON dbo.MSpublications
                 (publication, publisher_db, publisher_id)

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

        if not exists (select * from sys.columns where name = 'min_autonosync_lsn' and
            object_id = object_id('dbo.MSpublications'))
        begin
            alter table dbo.MSpublications add min_autonosync_lsn varbinary(16) NULL
        end
    end
    /*
     * MSsnapshot_history
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    select @table_name = N'MSsnapshot_history'
    if object_id('MSsnapshot_history') is not NULL
    begin
        if exists( select agent_id, timestamp, start_time, time
            from MSsnapshot_history
            group by agent_id, timestamp, start_time, time
            having count(*) > 1 )
            raiserror(21203, 10, 10, @table_name)
        else
        begin
            if exists( select * from sys.indexes where name = 'ucMSsnapshot_history' AND
                object_id = OBJECT_ID('MSsnapshot_history'))
            begin
                drop index dbo.MSsnapshot_history.ucMSsnapshot_history
            end
            create unique clustered index ucMSsnapshot_history on dbo.MSsnapshot_history
                        (agent_id, timestamp, start_time, time)
    end

        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

    /*
     * MSlogreader_history
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index.
    */
    select @table_name = N'MSlogreader_history'
    if object_id('MSlogreader_history') is not NULL
    begin
        if exists( select agent_id, timestamp, runstatus, start_time, time
            from MSlogreader_history
            group by agent_id, timestamp, runstatus, start_time, time
            having count(*) > 1 )
            raiserror(21203, 10, 11, @table_name)
        else
        begin
            if exists( select * from sysindexes where name = 'ucMSlogreader_history' AND
                id = OBJECT_ID('MSlogreader_history'))
            begin
                drop index dbo.MSlogreader_history.ucMSlogreader_history
            end
            create unique clustered index ucMSlogreader_history on dbo.MSlogreader_history
                      (agent_id, timestamp, runstatus, start_time, time)
        end
        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 object_id('MSqreader_history') is not NULL
    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


    /*
     * MSdistribution_history
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    select @table_name = N'MSdistribution_history'
    if object_id('MSdistribution_history') is not NULL
    begin
        if exists( select agent_id, timestamp, runstatus, start_time, time
            from MSdistribution_history
            group by agent_id, timestamp, runstatus, start_time, time
            having count(*) > 1 )
            raiserror(21203, 10, 12, @table_name)
        else
        begin
            if exists( select * from sysindexes where name = 'ucMSdistribution_history' AND
                id = OBJECT_ID('MSdistribution_history'))
            begin
                drop index dbo.MSdistribution_history.ucMSdistribution_history
            end
            create unique clustered index ucMSdistribution_history on dbo.MSdistribution_history
                    (agent_id, timestamp, runstatus, start_time, time)
        end

        if exists (select * from sysindexes where name = 'nc1MSdistribution_history' and id=object_id('MSdistribution_history'))
        begin
            drop index MSdistribution_history.nc1MSdistribution_history
        end

        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
    end

    /*
     * MSmerge_history
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    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')
    begin
    	create nonclustered index nc1MSmerge_history on MSmerge_history(session_id, timestamp)
    end

    exec @retcode = sys.sp_MSupgrade_merge_history90
    if @@error <> 0 or @retcode <> 0
        return 1

    /*
     * MSsnapshot_agents
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    select @table_name = N'MSsnapshot_agents'
    if object_id('MSsnapshot_agents') is not NULL
    begin
        if exists( select id
            from MSsnapshot_agents
            group by id
            having count(*) > 1 )
            raiserror(21203, 10, 14, @table_name)
        else
        begin
            if exists( select * from sysindexes where name = 'iMSsnapshot_agents' AND
                id = OBJECT_ID('MSsnapshot_agents'))
            begin
                drop index dbo.MSsnapshot_agents.iMSsnapshot_agents
            end

            create unique index iMSsnapshot_agents on dbo.MSsnapshot_agents    (id)
        end

        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
    end

    /*
     * MSlogreader_agents
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    select @table_name = N'MSlogreader_agents'
    if object_id('MSlogreader_agents') is not NULL
    begin
        if exists( select id
            from MSlogreader_agents
            group by id
            having count(*) > 1 )
            raiserror(21203, 10, 15, @table_name)
        else
        begin
            if exists( select * from sysindexes where name = 'iMSlogreader_agents' AND
                id = OBJECT_ID('MSlogreader_agents'))
            begin
                drop index dbo.MSlogreader_agents.iMSlogreader_agents
            end
            create unique index iMSlogreader_agents on dbo.MSlogreader_agents (id)
        end
    end

    /*
     * MSdistribution_agents
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    select @table_name = N'MSdistribution_agents'
    if object_id('MSdistribution_agents') is not NULL
    begin
        if exists( select id
            from MSdistribution_agents
            group by id
            having count(*) > 1 )
            raiserror(21203, 10, 16, @table_name)
        else
begin
            if exists( select * from sysindexes where name = 'ucMSdistribution_agents' AND
                id = OBJECT_ID('MSdistribution_agents'))
            begin
                drop index dbo.MSdistribution_agents.ucMSdistribution_agents
            end
            create unique clustered index ucMSdistribution_agents on dbo.MSdistribution_agents (id)
        end

        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'subscriber_security_mode', col_type = 'smallint NULL'-- subscriber_security_mode
                                                             union all
                                                             select col_name = 'subscriber_login', col_type = 'sysname NULL'-- subscriber_login
                                                             union all
                                                             select col_name = 'subscriber_password', col_type = 'nvarchar(524) NULL'-- subscriber_password
                                                             union all
                                                             select col_name = 'reset_partial_snapshot_progress', col_type = 'bit default 0 not null'-- reset_partial_snapshot_progress
                                                             union all
                                                             select col_name = 'subscriptionstreams', col_type = 'tinyint NULL' -- subscriptionstreams
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.MSdistribution_agents', 'U'))
               where t2.name is null) --This query gives all the columns in t1 that are not in syscolums
        for read only
        open colcurs
        fetch colcurs into @column_name, @column_type
        if (@@fetch_status <> -1)
        begin
            select @alter_cmd = 'alter table dbo.MSdistribution_agents add ' + @column_name + ' ' + @column_type
            fetch colcurs into @column_name, @column_type
            while(@@fetch_status <> -1)
            begin
                select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
                fetch colcurs into @column_name, @column_type
            end
            exec (@alter_cmd)
            if @@error <> 0 return 1
        end
        close colcurs
        deallocate colcurs
    end

    /*
     * MSmerge_agents
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    select @table_name = N'MSmerge_agents'
    if object_id('MSmerge_agents') is not NULL
    begin
        if exists( select id
            from dbo.MSmerge_agents
            group by id
            having count(*) > 1 )
            raiserror(21203, 10, 17, @table_name)
        else
        begin
            if exists( select * from sysindexes where name = 'iMSmerge_agents' AND
                id = OBJECT_ID('MSmerge_agents'))
            begin
                drop index dbo.MSmerge_agents.iMSmerge_agents
            end
            create index iMSmerge_agents ON dbo.MSmerge_agents (publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)

        end
    end

    /*
     * MSpublication_access
     * Add new unique idx for correctness iff there are no uniqueness violations. Drop old
     * index in favor of new column order in this index. Add new non-clustered index as needed.
    */
    if object_id('MSpublication_access') is not NULL
    begin
    	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
	   		
        EXEC('if exists( select publication_id, sid
			            from MSpublication_access
			            group by publication_id, sid
			            having count(*) > 1 )
				begin
					raiserror(21203, 10, 18, N''MSpublication_access'' )
				end
		        else
		        begin
		            if exists( select * from sysindexes where name = ''ucMSpublication_access'' AND
		                id = OBJECT_ID(''MSpublication_access''))
		            begin
		                drop index dbo.MSpublication_access.ucMSpublication_access
		            end

		            IF EXISTS (SELECT *
		            			FROM sys.columns
		            			WHERE name = ''publisher_id''
		            				AND object_id = object_id(''MSpublication_access''))
				    BEGIN
				    	ALTER TABLE MSpublication_access DROP COLUMN publisher_id
				    END	
				
		            create unique clustered index ucMSpublication_access on dbo.MSpublication_access
		                (publication_id, sid)
		        end')
    end

    
    -- MSreplication_monitordata
    -- Upgrade the indices
    
    if object_id(N'MSreplication_monitordata') is not NULL
    begin
        -- drop old indices
        if exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc1MSreplication_monitordata')
        begin
            drop index dbo.MSreplication_monitordata.nc1MSreplication_monitordata
        end
        if exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc2MSreplication_monitordata')
        begin
            drop index dbo.MSreplication_monitordata.nc2MSreplication_monitordata
        end
        if exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc3MSreplication_monitordata')
        begin
            drop index dbo.MSreplication_monitordata.nc3MSreplication_monitordata
        end
        if exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc4MSreplication_monitordata')
        begin
            drop index dbo.MSreplication_monitordata.nc4MSreplication_monitordata
        end
        if exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc5MSreplication_monitordata')
        begin
            drop index dbo.MSreplication_monitordata.nc5MSreplication_monitordata
        end
        -- create new ones
        if not exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc2MSreplication_monitordata')
        begin
            create nonclustered index nc2MSreplication_monitordata
                on dbo.MSreplication_monitordata(publication, publisher_db)
        end
        if not exists (select * from sys.indexes where object_id = object_id(N'MSreplication_monitordata')
                            and name = N'nc6MSreplication_monitordata')
        begin
            create nonclustered index nc6MSreplication_monitordata
                on dbo.MSreplication_monitordata(lastrefresh)
        end
    end
    
    -- Add default warnings in MSpublicationthresholds for existing publications
    
    if (object_id(N'dbo.MSpublicationthresholds') is not null
        and object_id(N'dbo.MSpublications') is not null
        and object_id(N'msdb.dbo.MSreplmonthresholdmetrics') is not null)
    begin
        declare @publication_id int
                    ,@publication_type int
        
        -- select publications that do not have entries in MSpublicationthresholds
        
        declare #hc cursor LOCAL FAST_FORWARD for
            select publication_id, publication_type
            from dbo.MSpublications
            where publication_id not in (select publication_id
                                                from dbo.MSpublicationthresholds)
        for read only
        open #hc
        fetch #hc into @publication_id, @publication_type
        while (@@fetch_status != -1)
        begin
            -- check publication type
            if (@publication_type in (0,1))
            begin
                -- Snapshot or transactional publication
                -- warnings are enabled by default
                insert into dbo.MSpublicationthresholds (publication_id,metric_id,value, isenabled)
                    select @publication_id, metric_id, default_value, 1
                    from msdb.dbo.MSreplmonthresholdmetrics
                    where metric_id = 1
                if (@publication_type = 0)
                begin
                    insert into dbo.MSpublicationthresholds (publication_id,metric_id,value, isenabled)
                        select @publication_id, metric_id, default_value, 1
                        from msdb.dbo.MSreplmonthresholdmetrics
                        where metric_id = 2
                end
            end
            else
            begin
                -- Merge publication
                insert into dbo.MSpublicationthresholds (publication_id,metric_id,value)
                    select @publication_id, metric_id, default_value
                    from msdb.dbo.MSreplmonthresholdmetrics
                    where metric_id in (4,5,6,7,8)
            end
            -- Check for error in insert
            if @@error <> 0
                return 1
            -- fetch next row
            fetch #hc into @publication_id, @publication_type
        end -- while (fetch)
        close #hc
        deallocate #hc
    end --if (object_id(N'dbo.MSpublicationthresholds') is not null
    /*
     * Upgrade replication passwords in distribution database.
    */
    begin transaction
    save tran sp_vupgrade_distdb

    /*
     * MSsubscriptions
    */
    if object_id(N'MSsubscriptions') is not NULL
    begin
        if not exists (select * from sys.columns where object_id = object_id(N'MSsubscriptions') and name = N'nosync_type')
        begin
            alter table MSsubscriptions add nosync_type tinyint DEFAULT 0 NOT NULL -- 0(none), 1(replication support only), 2(initialize with backup), 3(initialize from lsn)
        end
    end

    /*
     * MSsubscriber_info
    */
    if object_id('MSsubscriber_info') is not NULL
    begin
        if not exists (select * from sys.columns where object_id = Object_Id('MSSubscriber_info') and name = 'password' and max_length = '1048')
        begin
            /*
             * alter password column from sysname to nvarchar(524)
            */
            alter table MSsubscriber_info alter column password nvarchar(524)
        end

        /*
         * convert all the passwords to new encryption
         */
        exec('declare @password nvarchar(524)

		        declare cur_MSsubscriber_info CURSOR LOCAL FORWARD_ONLY for
		            select password
		            from MSsubscriber_info
		            for update of password
		
		        open cur_MSsubscriber_info
		        fetch next from cur_MSsubscriber_info into @password
		        while ( @@fetch_status <> -1 )
		        begin
		            EXEC sys.xp_repl_convert_encrypt_sysadmin_wrapper @password OUTPUT
		
		            update MSsubscriber_info
		            set password=@password
		            where current of cur_MSsubscriber_info

		            fetch next from cur_MSsubscriber_info into @password
		        end')
    end

    /*
     * MSdistribution_agents
    */
    if object_id('MSdistribution_agents') is not NULL
    begin
        if not exists (select * from sys.columns where object_id = Object_Id('MSdistribution_agents') and name = 'dts_package_password' and max_length = '1048')
        begin
            /*
             * alter dts_package_password column from sysname to nvarchar(524)
            */
            alter table MSdistribution_agents alter column dts_package_password nvarchar(524)
        end

        /*
         * convert all the dts_package_passwords to new encryption
         */
        exec('declare @dts_package_password nvarchar(524)

		        declare cur_MSdistribution_agents CURSOR LOCAL FORWARD_ONLY for
		            select dts_package_password
		            from MSdistribution_agents
		            for update of dts_package_password
		
		        open cur_MSdistribution_agents
		        fetch next from cur_MSdistribution_agents into @dts_package_password
		        while ( @@fetch_status <> -1 )
		        begin
		            EXEC sys.xp_repl_convert_encrypt_sysadmin_wrapper @dts_package_password OUTPUT
		
		            update MSdistribution_agents
		            set dts_package_password=@dts_package_password
		            where current of cur_MSdistribution_agents

		            fetch next from cur_MSdistribution_agents into @dts_package_password
		        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
    end

    /*
     * MSdistpublishers
    */
    if object_id('MSdistpublishers') is not NULL
    begin
        if not exists (select * from sys.columns where object_id = Object_Id('MSdistpublishers') and name = 'password' and max_length = '1048')
        begin
            /*
             * alter password column from sysname to nvarchar(524)
            */

            alter table MSdistpublishers alter column password nvarchar(524)
        end

        /*
         * convert all the passwords to new encryption
         */
        exec('declare @password nvarchar(524)

		        declare cur_MSdistpublishers CURSOR LOCAL FORWARD_ONLY for
		            select password
		            from MSdistpublishers
		            for update of password
		
		        open cur_MSdistpublishers
		        fetch next from cur_MSdistpublishers into @password
		        while ( @@fetch_status <> -1 )
		        begin
		            EXEC sys.xp_repl_convert_encrypt_sysadmin_wrapper @password OUTPUT

		            update MSdistpublishers
		            set password=@password
		            where current of cur_MSdistpublishers

		            fetch next from cur_MSdistpublishers into @password
		        end')
    end

    /*
     * MSrepl_commands
    */
    if object_id('MSrepl_commands') is not NULL
    begin
        if not exists (select * from sys.columns where object_id = Object_Id('MSrepl_commands') and name = 'hashkey')
        begin
            alter table MSrepl_commands add hashkey int default 0
        end

        if not exists (select * from sys.columns where object_id = Object_Id('MSrepl_commands') and name = 'originator_lsn')
        begin
            alter table MSrepl_commands add originator_lsn varbinary(16) NULL
        end
    end
    
    -- MSrepl_identity_range
    
    if object_id(N'dbo.MSrepl_identity_range') is not NULL
    begin
        
        -- add primary key constraint if needed
        
        if not exists (select * from sys.key_constraints where parent_object_id = object_id(N'dbo.MSrepl_identity_range')
                            and name = N'pkMSrepl_identity_range')
        begin
            alter table dbo.MSrepl_identity_range
                add constraint pkMSrepl_identity_range
                    primary key(publisher,publisher_db,tablename)
        end
    end

    -- ================================================================================
    -- HREPL OBJECTS
    -- ================================================================================

    -- IHarticles
    IF OBJECT_ID('IHarticles') IS NOT NULL
    BEGIN
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.columns
        	WHERE	object_id = OBJECT_ID('IHarticles') and name = 'use_default_datatypes'
        )
        BEGIN
            ALTER TABLE IHarticles ADD use_default_datatypes bit NOT NULL DEFAULT 0
        END

        IF EXISTS
        (
        	SELECT *
        	FROM sys.columns
        	WHERE name = 'custom_script'
        	  AND object_id=object_id('IHarticles')
        	  AND user_type_id <> 231
        )
        BEGIN
            ALTER TABLE IHarticles alter column custom_script nvarchar(2048)
        END
    END

    -- IHpublishertables
    IF OBJECT_ID('IHpublishertables') IS NOT NULL
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishertables')
        	  AND   name = 'idx_IHpublishertables_tableid'
        )
        BEGIN
            DROP INDEX dbo.IHpublishertables.idx_IHpublishertables_tableid
        END
        CREATE UNIQUE CLUSTERED INDEX idx_IHpublishertables_tableid ON IHpublishertables (table_id)

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishertables')
        	  AND   name = 'idx_IHpublishertables_name'
        )
        BEGIN
            CREATE INDEX idx_IHpublishertables_name ON IHpublishertables (name)
        END
	END
	
    -- IHarticles
    IF OBJECT_ID('IHarticles') IS NOT NULL
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHarticles')
        	  AND   name = 'idx_IHarticles_articleid'
        )
        BEGIN
            DROP INDEX dbo.IHarticles.idx_IHarticles_articleid
        END
        CREATE UNIQUE CLUSTERED INDEX idx_IHarticles_articleid ON IHarticles (article_id)

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHarticles')
        	  AND   name = 'idx_IHarticles_tableid'
        )
        BEGIN
            CREATE INDEX idx_IHarticles_tableid ON IHarticles (table_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHarticles')
        	  AND   name = 'idx_IHarticles_name'
        )
        BEGIN
            CREATE INDEX idx_IHarticles_name ON IHarticles (name)
        END
	END
	
	-- IHpublishercolumns
    IF OBJECT_ID('IHpublishercolumns') IS NOT NULL
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumns')
        	  AND   name = 'idx_IHpublishercolumns_name'
        )
        BEGIN
            DROP INDEX dbo.IHpublishercolumns.idx_IHpublishercolumns_name
        END
        CREATE INDEX idx_IHpublishercolumns_name on IHpublishercolumns (name)

        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumns')
        	  AND   name = 'idx_IHpublishercolumns_tableid'
        )
        BEGIN
            DROP INDEX dbo.IHpublishercolumns.idx_IHpublishercolumns_tableid
        END
        CREATE INDEX idx_IHpublishercolumns_tableid on IHpublishercolumns (table_id)

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumns')
        	  AND   name = 'idx_IHpublishercolumns_type'
        )
        BEGIN
            CREATE INDEX idx_IHpublishercolumns_type on IHpublishercolumns (type)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumns')
        	  AND   name = 'idx_IHpublishercolumns_pubcolumnid'
        )
        BEGIN
            CREATE INDEX idx_IHpublishercolumns_pubcolumnid on IHpublishercolumns (publishercolumn_id)
        END
	END

	-- IHcolumns
    IF OBJECT_ID('IHcolumns') IS NOT NULL
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHcolumns')
        	  AND   name = 'idx_IHcolumns_pubcolumnid'
        )
        BEGIN
            DROP INDEX dbo.IHcolumns.idx_IHcolumns_pubcolumnid
        END
        CREATE CLUSTERED INDEX idx_IHcolumns_pubcolumnid ON IHcolumns (publishercolumn_id)

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHcolumns')
        	  AND   name = 'idx_IHcolumns_name'
        )
        BEGIN
            CREATE INDEX idx_IHcolumns_name ON IHcolumns (name)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHcolumns')
        	  AND   name = 'idx_IHcolumns_columnid'
        )
        BEGIN
            CREATE INDEX idx_IHcolumns_columnid ON IHcolumns (column_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHcolumns')
        	  AND   name = 'idx_IHcolumns_articleid'
        )
        BEGIN
            CREATE INDEX idx_IHcolumns_articleid ON IHcolumns (article_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHcolumns')
        	  AND   name = 'idx_IHcolumns_mappedtype'
        )
        BEGIN
            CREATE INDEX idx_IHcolumns_mappedtype ON IHcolumns (mapped_type)
        END
	END
	
	-- IHpublisherindexes
    IF OBJECT_ID('IHpublisherindexes') IS NOT NULL
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublisherindexes')
        	  AND   name = 'idx_IHpublisherindexes_pubindexid'
        )
        BEGIN
            DROP INDEX dbo.IHpublisherindexes.idx_IHpublisherindexes_pubindexid
        END
        CREATE UNIQUE CLUSTERED INDEX idx_IHpublisherindexes_pubindexid ON IHpublisherindexes (publisherindex_id)

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublisherindexes')
        	  AND   name = 'idx_IHpublisherindexes_tableid'
        )
        BEGIN
            CREATE INDEX idx_IHpublisherindexes_tableid ON IHpublisherindexes (table_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublisherindexes')
        	  AND   name = 'idx_IHpublisherindexes_name'
        )
        BEGIN
            CREATE INDEX idx_IHpublisherindexes_name ON IHpublisherindexes (name)
        END
 	END	

    -- IHpublishercolumnindexes
    IF OBJECT_ID('IHpublishercolumnindexes') IS NOT NULL
    BEGIN
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumnindexes')
        	  AND   name = 'idx_IHpublishercolumnindexes_pubcolumnid'
        )
        BEGIN
            CREATE CLUSTERED INDEX idx_IHpublishercolumnindexes_pubcolumnid ON  IHpublishercolumnindexes (publishercolumn_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumnindexes')
        	  AND   name = 'idx_IHpublishercolumnindexes_pubindexid'
        )
        BEGIN
            CREATE INDEX idx_IHpublishercolumnindexes_pubindexid ON  IHpublishercolumnindexes (publisherindex_id)
        END
	END
	
    -- IHpublications
    IF OBJECT_ID('IHpublications') IS NOT NULL
    BEGIN
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublications')
        	  AND   name = 'idx_IHpublications_replfreq'
        )
        BEGIN
        	CREATE INDEX idx_IHpublications_replfreq ON IHpublications (repl_freq)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublications')
        	  AND   name = 'idx_IHpublications_name'
        )
        BEGIN
        	CREATE INDEX idx_IHpublications_name ON IHpublications (name)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublications')
        	  AND   name = 'idx_IHpublications_pubid'
        )
        BEGIN
        	CREATE INDEX idx_IHpublications_pubid ON IHpublications (pubid)
        END

        if not exists(select * from sys.columns where object_id = object_id('IHpublications') and name = 'originator_id')
        	alter table dbo.IHpublications add originator_id int NULL

	END
	
    -- IHpublisherconstraints
    IF OBJECT_ID('IHpublisherconstraints') IS NOT NULL
    BEGIN
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublisherconstraints')
        	  AND   name = 'idx_IHpublisherconstraints_pubconstraintid'
        )
        BEGIN
        	CREATE UNIQUE CLUSTERED INDEX idx_IHpublisherconstraints_pubconstraintid ON IHpublisherconstraints (publisherconstraint_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublisherconstraints')
        	  AND   name = 'idx_IHpublisherconstraints_tableid'
        )
        BEGIN
        	CREATE INDEX idx_IHpublisherconstraints_tableid ON IHpublisherconstraints (table_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublisherconstraints')
        	  AND   name = 'idx_IHpublisherconstraints_name'
        )
        BEGIN
        	CREATE INDEX idx_IHpublisherconstraints_name ON IHpublisherconstraints (name)
        END
	END
	
    -- IHpublishercolumnconstraints
    IF OBJECT_ID('IHpublishercolumnconstraints') IS NOT NULL
    BEGIN
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumnconstraints')
        	  AND   name = 'idx_IHpublishercolumnconstraints_pubcolumnid'
        )
        BEGIN
        	CREATE CLUSTERED INDEX idx_IHpublishercolumnconstraints_pubcolumnid ON IHpublishercolumnconstraints (publishercolumn_id)
        END

        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHpublishercolumnconstraints')
        	  AND   name = 'idx_IHpublishercolumnconstraints_pubconstraintid'
        )
        BEGIN
        	CREATE INDEX idx_IHpublishercolumnconstraints_pubconstraintid ON IHpublishercolumnconstraints (publisherconstraint_id)
        END
	END	
	
	-- IHsubscriptions
    IF OBJECT_ID('IHsubscriptions') IS NOT NULL
    BEGIN
        IF EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('IHsubscriptions')
        	  AND   name = 'idx_IHsubscriptions_article_id'
        )
        BEGIN
            DROP INDEX dbo.IHsubscriptions.idx_IHsubscriptions_article_id
        END
        CREATE UNIQUE CLUSTERED INDEX [idx_IHsubscriptions_article_id] ON [dbo].[IHsubscriptions]
        (
	        [article_id] ASC,
	        [srvid] ASC,
	        [dest_db] ASC
        )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF ) ON [PRIMARY]
	END
	
	-- sysschemaarticles
    IF OBJECT_ID('sysschemaarticles') IS NOT NULL
    BEGIN
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sys.indexes
        	WHERE	object_id = OBJECT_ID('sysschemaarticles')
        	  AND   name = 'c1sysschemaarticles'
        )
        BEGIN
            CREATE UNIQUE CLUSTERED INDEX [c1sysschemaarticles] ON [dbo].[sysschemaarticles]
            (
                [artid] ASC,
                [pubid] ASC
            )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
        END
	END

	-- If the view 'syspublications' exists, drop it and recreate it.
	-- add column originator_id.
	IF EXISTS (SELECT * from sys.objects WHERE name = 'syspublications' and type = 'V')
       BEGIN
       	DROP VIEW dbo.syspublications
       END

	EXEC('create view syspublications (description, name, pubid, repl_freq, status, sync_method, snapshot_jobid,
        			independent_agent, immediate_sync, enabled_for_internet, allow_push, allow_pull, allow_anonymous, immediate_sync_ready,
				allow_sync_tran, autogen_sync_procs, retention, allow_queued_tran, snapshot_in_defaultfolder, alt_snapshot_folder,
				pre_snapshot_script, post_snapshot_script, compress_snapshot, ftp_address, ftp_port, ftp_subdirectory, ftp_login,
				ftp_password, allow_dts, allow_subscription_copy, centralized_conflicts, conflict_retention, conflict_policy, queue_type,
				ad_guidname, backward_comp_level, allow_initialize_from_backup, min_autonosync_lsn, replicate_ddl, options, originator_id) AS
		   SELECT ihpub.description,
				ihpub.name,
				ihpub.pubid,
				ihpub.repl_freq,
				ihpub.status,
				ihpub.sync_method,
				ihpub.snapshot_jobid,
				ihpub.independent_agent,
				ihpub.immediate_sync,
				ihpub.enabled_for_internet,
				ihpub.allow_push,
				ihpub.allow_pull,
				ihpub.allow_anonymous,
				ihpub.immediate_sync_ready,
				ihpub.allow_sync_tran,
				ihpub.autogen_sync_procs,
				ihpub.retention,
				ihpub.allow_queued_tran,
				ihpub.snapshot_in_defaultfolder,
				ihpub.alt_snapshot_folder,
				ihpub.pre_snapshot_script,
				ihpub.post_snapshot_script,
				ihpub.compress_snapshot,
				ihpub.ftp_address,
				ihpub.ftp_port,
				ihpub.ftp_subdirectory,
				ihpub.ftp_login,
				ihpub.ftp_password,
				ihpub.allow_dts,
				ihpub.allow_subscription_copy,
				ihpub.centralized_conflicts,
				ihpub.conflict_retention,
				ihpub.conflict_policy,
				ihpub.queue_type,
				ihpub.ad_guidname,
				ihpub.backward_comp_level,
				ihpub.allow_initialize_from_backup,
				ihpub.min_autonosync_lsn,
				ihpub.replicate_ddl,
				ihpub.options,
				ihpub.originator_id
		 FROM   IHpublications ihpub')

      exec dbo.sp_MS_marksystemobject 'syspublications'
	
	-- If the view 'syssubscriptions' exists, drop it and recreate it.
	-- One of the column names in syssubscriptions had a typeo.
	IF EXISTS
	(
        	SELECT	*
        	FROM	sys.objects
        	WHERE	type = 'V'
        	  AND   name = 'syssubscriptions'
	)
	BEGIN
            DROP VIEW dbo.syssubscriptions
	END
	EXEC ( 'CREATE VIEW dbo.syssubscriptions (artid, srvid, dest_db, status, sync_type, login_name, subscription_type,
            distribution_jobid, timestamp, update_mode, loopback_detection, queued_reinit, nosync_type, srvname)
            AS
            SELECT ihsub.article_id,
                 ihsub.srvid,
                 ihsub.dest_db,
                 ihsub.status,
                 ihsub.sync_type,
                 ihsub.login_name,
                 ihsub.subscription_type,
                 ihsub.distribution_jobid,
                 ihsub.timestamp,
                 ihsub.update_mode,
           ihsub.loopback_detection,
                 ihsub.queued_reinit,
                 ihsub.nosync_type,
                 ihsub.srvname
            FROM   dbo.IHsubscriptions ihsub' )

    EXEC dbo.sp_MS_marksystemobject 'syssubscriptions'

	-- ================================================================================
	-- END HREPL OBJECTS
	-- ================================================================================

    /*
     * Call proc to upgrade MSmerge_subscriptions
    */
    exec sp_MSmerge_subscriptions_upgrade
    if @@error <> 0
           goto UNDO

    /*
    * Call proc to upgrade security meta-data.
    * ONLY DO THIS IF WE KNOW MSDB IS UPGRADED
    */
    IF EXISTS(SELECT *
                FROM msdb.sys.columns
                WHERE object_id = OBJECT_ID(N'msdb.dbo.sysjobsteps')
                    AND name = N'step_uid')
    BEGIN
        EXEC @retcode = sys.sp_MSupgrade_distdb_security_metadata
        IF @@ERROR <> 0 or @retcode <> 0
            goto UNDO
    END
    ELSE
    BEGIN
        -- "The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s."
        RAISERROR(21450, 10, -1, 'distribution security meta-data', @database,  'msdb database', 'sp_vupgrade_replsecurity_metadata') WITH NOWAIT
    END
    /*
    * Call proc to upgrade heterogeneous subscriber connect information
    */
    exec @retcode = sys.sp_MSupgrade_heterogeneous_subscriber_connectinfo
    IF @@ERROR <> 0 or @retcode <> 0
        goto UNDO
    
    -- all done
    
    commit transaction
    return 0

UNDO:
    rollback tran sp_vupgrade_distdb
    commit tran
    return 1
end

 
Last revision SQL2008SP2
See also

  sp_MSrestoredbreplication (Procedure)
sp_vupgrade_replication (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