Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_vupgrade_publisherdb

  No additional text.


Syntax
create procedure sys.sp_vupgrade_publisherdb @ver_old int, @ver_retention int
as
begin

    set nocount on

    declare @default_name sysname,
            @retcode int,
            @column_name nvarchar(128),
            @column_type nvarchar(128),
            @alter_cmd nvarchar(max)

    set @retcode = 0

    -- raiserror('sp_vupgrade_publisherdb', 0,1) with nowait

    -- Safety check - with Yukon, there are now objects in the dist db that can make
    -- a distribution db look like a publisher db.  In case the previous checks for
    -- distribution db mode fail, this is a last ditch check to make sure we don't
    -- add stuff to the distribution db
    IF sys.fn_MSrepl_isdistdb(DB_NAME()) = 1 OR
       object_id(N'dbo.IHpublishers', 'U') IS NOT NULL OR
       object_id(N'dbo.syspublications', 'V') IS NOT NULL OR
       object_id(N'dbo.MSdistribution_history', 'U') IS NOT NULL
    BEGIN
        -- The above evidence makes it extremely likely this is a distribution db
        -- Don't do anything further
        RETURN (0)
    END

    if object_id('systranschemas') is not null
    begin
        if not exists (select * from sys.columns where object_id = object_id('systranschemas') and
            name = 'typeid')
        begin
            alter table systranschemas add typeid int default 52
        end
    end

    -- syssubscriptions
    if object_id('dbo.syssubscriptions', 'U') is not null
    begin
         if not exists (select * from sys.columns where object_id = object_id('syssubscriptions') and
            name = 'queued_reinit')
        begin
            alter table syssubscriptions add queued_reinit bit default 0 not null
        end

        if not exists (select * from sys.columns where object_id = object_id('dbo.syssubscriptions', 'U') and
            name = 'nosync_type')
        begin
            alter table dbo.syssubscriptions add nosync_type tinyint default 0 not null
        end

        if not exists (select * from sys.columns where object_id = object_id('dbo.syssubscriptions', 'U') and
            name = 'srvname')
        begin
            alter table dbo.syssubscriptions add srvname sysname not null default N'' with values
		if exists (select * from master.dbo.sysdatabases where dbid = db_id() and cmptlevel > 70)
	            exec('update dbo.syssubscriptions set srvname = upper(ss.srvname collate database_default) from dbo.syssubscriptions sub
	            	join master.dbo.sysservers ss on ss.srvid = sub.srvid')
		else
	            exec('update dbo.syssubscriptions set srvname = upper(ss.srvname) from dbo.syssubscriptions sub
	            	join master.dbo.sysservers ss on ss.srvid = sub.srvid')
			if exists(select * from sys.indexes where name = 	N'unc1syssubscriptions' and object_id = object_id(N'syssubscriptions'))
				drop index syssubscriptions.unc1syssubscriptions
           create unique nonclustered index unc1syssubscriptions on syssubscriptions (artid, srvid, dest_db, srvname)
        end
    end -- end of syssubscriptions upgrade

    -- sysarticles
    if object_id('dbo.sysarticles', 'U') is not null
    begin
        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'ins_scripting_proc', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'del_scripting_proc', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'upd_scripting_proc', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'custom_script', col_type = 'nvarchar(2048) NULL'
                                                             union all
                                                             select col_name = 'fire_triggers_on_snapshot', col_type = 'bit NOT NULL default 0') as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysarticles', '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.sysarticles 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

	    -- sysarticlecolumns
	    --create sysarticlecolumns before reading columns info, do it within this block so it's NOT always
	    --created regardless of transactional replication
	    if object_id('sysarticlecolumns') is NULL
	    begin
	        create table dbo.sysarticlecolumns
	        (
	            artid           int NOT NULL,
	            colid           int NOT NULL,
	            is_udt 			bit default 0,
	            is_xml 			bit default 0,
	            is_max 			bit default 0
	        )
	        create unique clustered index idx_sysarticlecolumns on sysarticlecolumns (artid, colid)
	        exec dbo.sp_MS_marksystemobject 'sysarticlecolumns'
	    end

    	if exists (select * from sys.columns where object_id = object_id('dbo.sysarticles', 'U') and
            name = 'columns')
        begin
            exec @retcode = sys.sp_vupgrade_sysarticlecolumns
            if @@error <> 0 or @retcode <> 0 return 1

            alter table dbo.sysarticles drop column columns
            if @@error <> 0 return 1
        end

        if exists (select * from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id
                where object_id = object_id('dbo.sysarticlecolumns', 'U') and sc.name = N'colid' and st.name = N'smallint')
        begin
            if exists (select * from sysindexes where id = object_id('dbo.sysarticlecolumns') and name ='idx_sysarticlecolumns')
            begin
                drop index sysarticlecolumns.idx_sysarticlecolumns
                if @@error <> 0 return 1
            end

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

            create unique clustered index idx_sysarticlecolumns on sysarticlecolumns (artid, colid)
            if @@error <> 0 return 1
        end

        if exists (select * from sysindexes where id = object_id('dbo.sysarticles') and name = 'unc1sysarticles' )
        begin
        	if not exists (select * from sysindexes where id = object_id('dbo.sysarticles') and name = 'c1sysarticles' )
			begin
				drop index sysarticles.unc1sysarticles
				create unique clustered index c1sysarticles on sysarticles (artid, pubid)
			end
        end

	end


    --  dbo.MSpeer_lsns
    if object_id('dbo.MSpeer_lsns') is not null
    begin

       -- add columns originator_version and originator_id
       if not exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_version')
       begin
       	select @alter_cmd = 'alter table dbo.MSpeer_lsns add originator_version int NULL'
		exec (@alter_cmd)
       end

       if not exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_id')
       begin
       	select @alter_cmd = 'alter table dbo.MSpeer_lsns add originator_id int NULL'
		exec (@alter_cmd)
       end

	-- Changing index PK from clustered to non-clustered
	-- drop clustered PK so we can drop index
	declare @PKConstraintName sysname
	select top 1 @PKConstraintName = sysdc.name from sys.key_constraints sysdc
		where sysdc.parent_object_id = object_id('dbo.MSpeer_lsns')
			and sysdc.type = 'PK'
	if @PKConstraintName is not null
	begin
		select @alter_cmd = 'alter table dbo.MSpeer_lsns drop constraint ' + QUOTENAME(@PKConstraintName)
		exec (@alter_cmd)   -- drop primary key (also drops clustered key index)
	end
	select @alter_cmd = 'alter table dbo.MSpeer_lsns add constraint ' + QUOTENAME(@PKConstraintName) + ' PRIMARY KEY NONCLUSTERED (id)'
	exec (@alter_cmd) -- Make PK w/ index unclustered
	
	
       -- Changing index uci_MSpeer_lsns from (originator, originator_db, originator_publication, originator_publication_id, originator_db_version, originator_lsn)
       -- to (originator, originator_db, originator_publication_id, originator_db_version, originator_lsn)
	if exists (select * from sys.indexes where name = 'uci_MSpeer_lsns' and object_id = object_id('dbo.MSpeer_lsns'))
	begin
	    -- drop old index
	    drop index dbo.MSpeer_lsns.uci_MSpeer_lsns
	end
	if exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator')
		and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_db')
		and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_publication_id')
		and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_db_version')
		and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_lsn')
	begin
		-- create new one
	       create unique clustered index uci_MSpeer_lsns on dbo.MSpeer_lsns(originator, originator_db, originator_publication_id, originator_db_version, originator_lsn)
       end
    end



    -- syspublications
    if object_id('dbo.syspublications', 'U') is not null
    begin
        -- default_access column no longer used
        if exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
            name = 'default_access')
        begin
            alter table dbo.syspublications drop column default_access
        end

 	 -- insure index nc3syspublications is there
 	 if exists (select * from sys.indexes where name = 'nc3syspublications' and
 	 	object_id = object_id('dbo.syspublications') )
 	 begin
 		drop index dbo.syspublications.nc3syspublications
 	 end
 	 if exists(select * from sys.columns where object_id = object_id('dbo.syspublications') and name = 'status')
 	 begin
 	 	create index nc3syspublications on syspublications(status)
 	 end
		
        /*
         * Since the ftp_address is now required to enable a publication for internet, publications
         * that were enabled for internet can not be upgraded automatically. So the enabled for
         * internet option is reset in the upgrade process. New FTP columns added later in script. (Sphinx -> Shiloh)
        */
        if exists (select * from sys.columns where object_id = object_id('syspublications') and
           name = 'enabled_for_internet')
        begin
            if not exists (select * from sys.columns where object_id = object_id('syspublications') and name = 'ftp_address')
            begin
                update dbo.syspublications
                   set enabled_for_internet = 0
            end
            else
            begin
                exec('update dbo.syspublications set enabled_for_internet = 0 where ftp_address is null')
            end
        end

        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'allow_queued_tran', col_type = 'bit default 0 not null' --Queued updating subscriptions option; conflict management settings added later in this proc
                                                             union all
                                                             select col_name = 'snapshot_in_defaultfolder', col_type = 'bit default 1 not null' --Portable snapshot
                                                             union all
                                                             select col_name = 'alt_snapshot_folder', col_type = 'nvarchar(255) null'
                                                             union all
                                                             select col_name = 'pre_snapshot_script', col_type = 'nvarchar(255) null' -- Pre-snapshot commands
                                                             union all
                                                             select col_name = 'post_snapshot_script', col_type = 'nvarchar(255) null' -- Post-snapshot commands
                                                             union all
                                                             select col_name = 'compress_snapshot', col_type = 'bit default 0 not null' -- Snapshot compression
                                                             union all
                                                             select col_name = 'ftp_address', col_type = 'sysname null' -- Post SQL7.0 FTP configuration stored at publisher
                                                             union all
                                                             select col_name = 'ftp_port', col_type = 'int not null default 21'
                                                             union all
                                                             select col_name = 'ftp_subdirectory', col_type = 'nvarchar(255) null'
                                                             union all
                                                             select col_name = 'ftp_login', col_type = 'sysname null default N''anonymous'''
                                                             union all
                                                             select col_name = 'ftp_password', col_type = 'nvarchar(524) null'
                                                             union all
                                                             select col_name = 'allow_dts', col_type = 'bit default 0 not null' --Transformable subscriptions
                                                             union all
                                                             select col_name = 'allow_subscription_copy', col_type = 'bit default 0 not null' -- Attach & Go
                                                             union all
                                                             select col_name = 'queue_type', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'centralized_conflicts', col_type = 'bit NULL' -- Transactional conflict management
                                                             union all
                                                             select col_name = 'conflict_retention', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'conflict_policy', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'ad_guidname', col_type = 'sysname null' -- Active Directory
                                                             union all
                                                             select col_name = 'min_autonosync_lsn', col_type = 'binary(10) NULL' 
                                                             union all
                                                             select col_name = 'replicate_ddl', col_type = 'int default 1' -- DDL
                                                            ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.syspublications', '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.syspublications 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

        
        -- The following code for upgrading schema options is applicable
        -- to 8.0->9.0 upgrade only, and it is needed because of subtle
        -- differences between how index-related schema options are interpreted
        -- between these two releases. 7.0's interpretation of index-related
        -- schema options is actually quite close to the 9.0 interpretation so
        -- there is no need to modify existing 7.0 schema options. The
        -- means for detecting an 8.0->9.0 upgrade is by checking for the
        -- presence of the backward_comp_level column and the absence of
        -- the allow_initialize_from_backup colum in syspublications
        
        -- The differences between how index-related schema options are
        -- interpreted in 8.0 and 9.0 are as follow:
        -- When the 0x10 (clustered index) and 0x40 (non-clustered indexes)
        -- options are specified in 8.0, only "pure" clustered and non
        -- clustered indexes not created for backing primary key or unique
        -- constraints are replicated, indexes for primary key and unique
        -- constraints are included only if the 0x80 (primary key) and 0x4000
        -- (unique constraint) options are specified respectively. There is
        -- also this special consideration of always implicitly including the
        -- 0x80 (primary key) option for transactional (not snapshot)
        -- publications even if the option is unspecified. Once either 0x80
        -- and/or 0x4000 are included, the 0x8000 (replicate primary key and
        -- unique constraints as contraints rather than just indexes) can be
        -- used to decide whether the included primary key or unique
        -- contraints are replicated as pure indexes or constraints.
        -- For 9.0 publications, specifying the 0x10 and 0x40 options will
        -- cause all the clustered and non-clustered indexes including those
        -- for backing primary key or unique constraints to be replicated
        -- as "create index" statements at least. If the 0x80 and/or 0x4000
        -- options are specified, the primary key and/or unique constraints
        -- will be replicated as contraints respectively regardless of whether
        -- the underlying index type (clustered or non-clustered) are covered
        -- by the 0x10 and 0x40 options. In a way, the 0x80 and 0x4000 options
        -- can also be seen as modifiers of key constraint indexes covered by
        -- 0x10 and 0x40 that "upgrade" those "pure indexes" to "full-fledged
        -- constraints" thereby providing a more flexible mechanism than
        -- the 0x8000 option in 8.0. Thus, the 0x8000 option will be
        -- deprecated in 9.0. In 9.0, it is also possible to completely
        -- disable the primary key for read-only (i.e. non-synctran, non
        -- queued) publications by not specifying the 0x40 option as well as
        -- the option (0x10 or 0x40) specifying the underlying index type
        -- (clustered or non-clustered) of the primary key.
        
        -- Based on the above description of how interpretation of
        -- index-related schema options differs from 8.0 to 9.0, here
        -- is the plan for upgrading the schema options from 8.0 to 9.0 which
        -- tries preserve the old behavior as much as possible. There are
        -- two main cases to consider:
        -- i) The 0x8000 option was not specified - This leads to the
        --    following two sub-cases:
        --    a) At least one of 0x80 or 0x4000 was specified - Under the 9.0
        --       interpretation, these will cause the primary key and\or the
        --       unique constraints to be replicated as constraints rather than
        --       indexes which is inapproprate because the 0x8000 option is
        --       explicitly disabled. The best way to upgrade this class of
        --       schema options is to force enable both 0x10 and 0x40 to
        --       ensure that the underlying index(es) for the primary key and
        --       unique constraints are replicated. This is actually what
        --       happened in the 8.0 snapshot agent so there is essentially
        --       no change in behavior.
        --    b) Neither 0x80 nor 0x4000 was specified - The only
        --       issue of concern with this class of schema options is that
        --       the 0x10 and 0x40 options now covers a broader range of
        --       indexes. As such, it not necessary to change these schema
        --       options during upgrade.
        -- ii) The 0x8000 option was specified - In this case, at least
        --     one of 0x80 or 0x4000 is probably specified. Under the 9.0
        --     interpretation, mere inclusion of 0x80 and 0x4000 will cause
        --     the corresponding primary key and unique constraints to be
        --     replicated as constraints anyway so the only thing that needs
        --     to be done is to disable the 0x8000 option during upgrade.
        --     Note that broader interpretation of 0x10 and 0x40 in 9.0 will
        --     also cause a broader range of indexes to be included with
        --     this class of schema options.
        

        
        -- Use transaction encompassing the addition of the
        -- backward_comp_level and the allow_initialize_from_backup columns to
        -- syspublications to ensure that schema_option upgrade is done at
        -- most once even in failure scenarios.
        
        begin transaction
        save transaction schema_options_upgrade
        if object_id('dbo.sysarticles', 'U') is not null and
           exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and name = 'backward_comp_level') and
           not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and name = 'allow_initialize_from_backup')
        begin
            update dbo.sysarticles
               set schema_option = case
                    when (sys.fn_replgetbinary8lodword(schema_option) & 0x8000) = 0 and (sys.fn_replgetbinary8lodword(schema_option) & 0x4080) <> 0 then sys.fn_replcombinehilodwordintobinary8(sys.fn_replgetbinary8hidword(schema_option),sys.fn_replgetbinar
y8lodword(schema_option) | 0x50 & ~convert(int, 0x4080)) -- i)a)
                    when (sys.fn_replgetbinary8lodword(schema_option) & 0x8000) <> 0 then sys.fn_replcombinehilodwordintobinary8(sys.fn_replgetbinary8hidword(schema_option),sys.fn_replgetbinary8lodword(schema_option) & ~convert(int, 0x8000)) -- ii)
                    else schema_option
                end
            if @@error <> 0
            begin
                if @@trancount > 0
                begin
                    rollback transaction schema_options_upgrade
                    commit transaction
                end
                return 1
            end
        end

        if not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
            name = 'backward_comp_level')
        begin
            alter table dbo.syspublications add backward_comp_level int default 10 not null
            if @@error <> 0
            begin
                if @@trancount > 0
                begin
                    rollback transaction schema_options_upgrade
                    commit transaction
                end
                return 1
            end
        end

        /*
         * Transactional replication automated nosync subscription setup
         * support
         */
        if not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
            name = 'allow_initialize_from_backup')
        begin
            alter table dbo.syspublications add allow_initialize_from_backup bit default 0 not null
            if @@error <> 0
            begin
                if @@trancount > 0
                begin
                    rollback transaction schema_options_upgrade
                    commit transaction
                end
                return 1
            end
        end


        /*
         * Revoke public select permission from synchronization views
         */
        declare @synchronization_object_name nvarchar(517)
        declare #syncobject_name cursor local fast_forward for
            select quotename(schema_name(so.schema_id)) + N'.' + quotename(so.name)
              from sysarticles sa
            inner join sys.objects so
                on sa.sync_objid = so.object_id
             where (sa.type & 0x05) = 0x01 -- Must be logbased article with an automatically generated view
               and rtrim(so.type) = 'V'
        open #syncobject_name

        fetch #syncobject_name into @synchronization_object_name
        while (@@fetch_status <> -1)
        begin
            exec (N'revoke select on ' + @synchronization_object_name + N' from public')
            fetch #syncobject_name into @synchronization_object_name
        end
        close #syncobject_name
        deallocate #syncobject_name
        commit transaction

        /*
         * Publication options
         */
        if not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
            name = 'options')
        begin
            exec ('alter table dbo.syspublications add options int default 0 not null')

            /*
             * Set enabled for het sub bit in options column if sync_method = 1
             */
            exec ('update dbo.syspublications set options = 0x4 where sync_method = 1')
        end
        /*
         * Schema only articles (views, procs, udfs)
        */
        if object_id(N'dbo.sysschemaarticles') is null
        begin
            -- table does not exist
            create table dbo.sysschemaarticles
            (
                artid               int                 NOT NULL,
                creation_script     nvarchar(255)       NULL,
                description         nvarchar(255)       NULL,
                dest_object         sysname             NOT NULL,
                name                sysname             NOT NULL,
                objid               int                 NOT NULL,
                pubid               int                 NOT NULL,
                pre_creation_cmd    tinyint             NOT NULL,
                status              int                 NOT NULL,
                type                tinyint             NOT NULL,
                schema_option       binary(8)           NULL,
                dest_owner          sysname             NULL
            )
            exec dbo.sp_MS_marksystemobject N'sysschemaarticles'

            if not exists (select * from sys.indexes where object_id = object_id(N'dbo.sysschemaarticles') and name = N'c1sysschemaarticles')
            begin
                create unique clustered index c1sysschemaarticles
                    on dbo.sysschemaarticles(artid, pubid)
            end
        end
        else
        begin
            -- table exists - upgrade indices
            if exists (select * from sys.indexes where object_id = object_id(N'dbo.sysschemaarticles') and name = N'unc1sysschemaarticles' )
            begin
                drop index dbo.sysschemaarticles.unc1sysschemaarticles
            end
            if not exists (select * from sys.indexes where object_id = object_id(N'dbo.sysschemaarticles') and name = N'c1sysschemaarticles' )
            begin
                create unique clustered index c1sysschemaarticles on dbo.sysschemaarticles (artid, pubid)
            end
        end

        if exists (select * from sys.objects where name = 'sysextendedarticlesview')
        begin
            drop view dbo.sysextendedarticlesview
        end

        -- cannot create view directly in stored procedure
        exec ( 'create view dbo.sysextendedarticlesview
                   as
                    select artid, creation_script, del_cmd, description,
                                dest_table, filter, filter_clause, ins_cmd, name, objid, pubid,
                                pre_creation_cmd, status, sync_objid, type, upd_cmd,
                                schema_option, dest_owner, ins_scripting_proc, del_scripting_proc,
                                upd_scripting_proc, custom_script, fire_triggers_on_snapshot
                            from sysarticles

                    union all

                    select artid, creation_script, NULL, description,
                            dest_object, NULL, NULL, NULL, name, objid, pubid,
                            pre_creation_cmd, status, NULL, type, NULL,
                            schema_option, dest_owner, NULL, NULL, NULL, NULL, 0
                        from sysschemaarticles')

        exec dbo.sp_MS_marksystemobject 'sysextendedarticlesview'

        exec sys.sp_vupgrade_syscol_status

    end
    -- sysarticleupdates
    if object_id('sysarticleupdates') is not null
    begin
        declare #colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'sync_upd_trig', col_type = 'int default 0 NOT NULL'
                                                             union all
                                                             select col_name = 'conflict_tableid', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'ins_conflict_proc', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'identity_support', col_type = 'bit default 0 NOT NULL'
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysarticleupdates', '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.sysarticleupdates 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
        
        -- remove the default for column sync_upd_trig
        
        select @column_name = NULL
        select @column_name = dc.name
        from sys.default_constraints as dc
            join sys.columns as c
                on dc.parent_object_id = c.object_id
                    and dc.parent_column_id = c.column_id
        where c.object_id = object_id('dbo.sysarticleupdates')
            and c.name = N'sync_upd_trig'
        if (@column_name is not null)
        begin
            select @alter_cmd = N'alter table dbo.sysarticleupdates drop constraint ' + sys.fn_replreplacesinglequote(quotename(@column_name))
            exec(@alter_cmd)
            if @@error <> 0
                return 1
        end
    end

    -- Upgrade dbt->distbackuplsn
    -- Make sure the upgrade is done for 8.0 Beta 2 customers.
    -- no need to do this if called during restoration of sql70 backup
    -- so to avoid sp_repldone error out with 18757,
    -- master..sysdatbases.category is bound to be 0 when 70 db is restored into 80
    -- since 70 dbtable did not have this field
    if exists (select * from sys.objects where name = 'sysarticles') and
        not exists (select * from sys.objects where name = 'systranschemas') and
            exists( select * from master..sysdatabases
                        where dbid = db_id() and category & 1 = 1)
		--if cdc is still enabled, don't call sp_repldone
		AND not exists(select * from sys.databases where db_id() = database_id and is_cdc_enabled = 1)
    begin
        -- Force to get in even if the logreader is running.
        exec sys.sp_replflush
        exec sys.sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,
            @time = 0, @reset = 0, @code = 1
        -- Unmark the connection as the logreader.
        exec sys.sp_replflush
    end

    -- Create new tran tables if the db is enabled for tran publishing.
    if exists (select name from sys.objects where name='syspublications')
    begin
        exec sys.sp_MScreate_pub_tables
    end

    /*
     * syspublications
    */
    if object_id('syspublications', 'U') is not null
    begin
        if not exists (select * from sys.columns where object_id = Object_Id('syspublications') and name = 'ftp_password' and max_length = '1048')
        begin
            /*
             * syspublications ftp_password
             * no need to upgrade passwords since this column is new in 8.0.
            */
            declare @cmptlevel tinyint
            select @cmptlevel = cmptlevel from master.dbo.sysdatabases where dbid = db_id()
            if @cmptlevel < 70
            begin
                                raiserror (15048, -1, -1, 70, 70, 70, 80)
            end
            else
            begin
                exec( 'alter table syspublications alter column ftp_password nvarchar(524)' )
            end
        end

        --create originator_id column if it does not exist
        if not exists (select * from sys.columns where object_id = object_id('syspublications') and name = 'originator_id')
        begin
            alter table syspublications add originator_id int NULL
        end

        -- change non-clustered index on syspublications.pubid to clustered
        -- index
        if exists( select * from sys.indexes where name = 'unc1syspublications' and object_id = object_id('dbo.syspublications') )
        begin
           drop index dbo.syspublications.unc1syspublications
        end
        if not exists ( select * from sys.indexes where name = 'uc1syspublications' and object_id = object_id('dbo.syspublications') )
        begin
            create unique clustered index uc1syspublications
                on syspublications (pubid)
        end
        
        -- Upgrade updating publications
        
        exec @retcode = sys.sp_vupgrade_updatingpublicationarticle
        if @@error != 0 or @retcode != 0
            return 1

    	--if pk of base table is disabled or lo-based iv becomes regular view,
		--reinit article per SQLBU#335193
		
		if object_id('dbo.sysarticles', 'U') is not null
			and object_id('dbo.syssubscriptions', 'U') is not null
			and DatabasePropertyEx(DB_NAME(), 'IsPublished') = 1
		begin
			exec sp_checkinvalidivarticle @mode = 0, @publication = NULL
		end
		
    end
    
    -- all done
    
end

 
Last revision SQL2008SP2
See also

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