Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_vupgrade_mergetables

  No additional text.


Syntax
create procedure sys.sp_vupgrade_mergetables( @remove_repl bit = 0 )
as
begin

    set nocount on

    declare @artnick int
    declare @objid  int
    declare @goodlen int
    declare @col_track int
    declare @article sysname
    declare @pubname sysname
    declare @artid uniqueidentifier
    declare @pubid uniqueidentifier
    declare @qualified_name nvarchar(257)
    declare @source_owner   sysname
    declare @source_object  sysname
    declare @table_name     sysname
    declare @cmd            nvarchar(1000)
    declare @default_name   nvarchar(258)
    declare @retcode    integer
    declare @snapshot_ready int
    declare @constraintname nvarchar(258)
    declare @dbname sysname

    declare @additive_resolver_clsid    nvarchar(60)
    declare @average_resolver_clsid     nvarchar(60)
    declare @download_resolver_clsid    nvarchar(60)
    declare @max_resolver_clsid         nvarchar(60)
    declare @mergetxt_resolver_clsid    nvarchar(60)
    declare @min_resolver_clsid         nvarchar(60)
    declare @subwins_resolver_clsid     nvarchar(60)
    declare @upload_resolver_clsid      nvarchar(60)
    declare @sp_resolver_clsid          nvarchar(60)

    declare @80additive_resolver_clsid  nvarchar(60)
    declare @80average_resolver_clsid   nvarchar(60)
    declare @80download_resolver_clsid  nvarchar(60)
    declare @80max_resolver_clsid       nvarchar(60)
    declare @80mergetxt_resolver_clsid  nvarchar(60)
    declare @80min_resolver_clsid       nvarchar(60)
    declare @80subwins_resolver_clsid   nvarchar(60)
    declare @80upload_resolver_clsid    nvarchar(60)
    declare @80sp_resolver_clsid        nvarchar(60)

    declare @column_name nvarchar(128)
    declare @column_type nvarchar(128)
    declare @alter_cmd nvarchar(max)

    DECLARE @cnt int, @idx  int    /* Loop counter, index */
    DECLARE @columnid smallint     /* Columnid-1 = bit to set */
    DECLARE @columns binary(128)   /* Temporary storage for the converted column */


    select @additive_resolver_clsid = '{D2CCB059-65DD-497B-8822-7660B7778DDF}'
    select @average_resolver_clsid  = '{91DD61BF-D937-4A21-B0EF-36204A328439}'
    select @download_resolver_clsid = '{9602B431-2937-4D51-8CC3-11F8AC1EC26D}'
    select @max_resolver_clsid      = '{77209412-47CF-49AF-A347-DCF7EE481277}'
    select @mergetxt_resolver_clsid = '{0045200C-9126-4432-BC9B-3186D141EB5A}'
    select @min_resolver_clsid      = '{2FF7564F-9D55-48C0-A4C1-C148076D9119}'
    select @subwins_resolver_clsid  = '{E93406CC-5879-4143-B70B-29B385BA80C9}'
    select @upload_resolver_clsid   = '{05614E0C-92A9-45F3-84A4-46C8E36424A9}'
    select @sp_resolver_clsid       = '{D264B5C0-1300-471A-80C9-9C1FC34A3691}'

    select @80additive_resolver_clsid   = '{08B0B2DB-3FB3-11D3-A4DE-00C04F610189}'
    select @80average_resolver_clsid    = '{08B0B2DC-3FB3-11D3-A4DE-00C04F610189}'
    select @80download_resolver_clsid   = '{08B0B2DD-3FB3-11D3-A4DE-00C04F610189}'
    select @80max_resolver_clsid        = '{08B0B2DE-3FB3-11D3-A4DE-00C04F610189}'
    select @80mergetxt_resolver_clsid   = '{08B0B2E1-3FB3-11D3-A4DE-00C04F610189}'
    select @80min_resolver_clsid        = '{08B0B2DF-3FB3-11D3-A4DE-00C04F610189}'
    select @80subwins_resolver_clsid    = '{08B0B2E0-3FB3-11D3-A4DE-00C04F610189}'
    select @80upload_resolver_clsid     = '{08B0B2E2-3FB3-11D3-A4DE-00C04F610189}'
    select @80sp_resolver_clsid         = '{08B0B2D6-3FB3-11D3-A4DE-00C04F610189}'

    select @dbname = db_name()

    exec @retcode = sys.sp_MSreplcheck_publish
    if (@retcode <> 0 or @@error <> 0)
        return 1


    -- Check if upgrade is needed. If the database is 90 then dispatch to sp_MSmerge_upgrade_from_90rtm
    if object_id('sysmergepublications') is not NULL
    begin
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'use_partition_groups')
        begin
         exec @retcode = sys.sp_MSmerge_upgrade_from_90rtm
            return @retcode
        end
    end

    begin tran
    save tran vupgrade_mergetables

    /*
     * dbo.sysmergepublications
    */
    if object_id('sysmergepublications') is not NULL
    begin
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'backward_comp_level')
        begin
            alter table dbo.sysmergepublications add backward_comp_level int not NULL default 10 -- defaulted to 70 RTM
            if @@error<>0 goto error
        end
    end

    if @remove_repl=0 and object_id('sysmergepublications') is not NULL
    begin
        /*
         * No direct select on sysmergepublications for public
        */
        -- default_access default no longer used
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and
            name = 'default_access')
        begin
            -- Get the name of the default associated with default_access and drop it
            select @default_name = QUOTENAME(object_name(constid))
                from sysconstraints
                where id = object_id('dbo.sysmergepublications')
                    and col_name(id, colid) = N'default_access'
            if @default_name is not null
            begin
                -- Drop the default
                exec (N'alter table dbo.sysmergepublications drop constraint ' + @default_name)
                if @@error<>0 goto error
            end

            alter table dbo.sysmergepublications drop column default_access
            if @@error<>0 goto error
        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. (Shiloh)
        */
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and
           name = 'enabled_for_internet')
        begin
            if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'ftp_address')
            begin
                exec(N'update dbo.sysmergepublications set enabled_for_internet = 0')
            end
            else
            begin
                exec(N'update dbo.sysmergepublications set enabled_for_internet = 0 where ftp_address is null')
            end
            if @@error<>0 goto error
        end

        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'snapshot_in_defaultfolder', col_type = 'bit default 0 not null'-- Portable snapshot (Shiloh)
                                                             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'-- Snapshot pre/post scripts (Shiloh)
                                                             union all
                                                             select col_name = 'post_snapshot_script', col_type = 'nvarchar(255) null'
                                                             union all
                                                             select col_name = 'compress_snapshot', col_type = 'bit default 0 not null'-- Snapshot compression (Shiloh)
                                                             union all
                                                             select col_name = 'ftp_address', col_type = 'sysname null'-- Ftp support at publication level (Shiloh)
                                                             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 = 'conflict_retention', col_type = 'int null' -- Conflict retention  (Shiloh)
                                                             union all
                                                             select col_name = 'keep_before_values', col_type = 'int null'-- Keep partition changes (SQL7.0 SP2 )
                                                             union all
                                                             select col_name = 'allow_subscription_copy', col_type = 'bit null default 0'-- Attach & Go (Shiloh)
                                                             union all
                                                             select col_name = 'allow_synctoalternate', col_type = 'bit null default 0'-- Sync to any hub (Shiloh)
                                                             union all
                                                             select col_name = 'web_synchronization_url', col_type = 'nvarchar(500) null'-- WebSync URL (Yukon)
                                                             union all
                                                             select col_name = 'retention_period_unit', col_type = 'tinyint default 0 not null'-- 0=day, 1=week, 2=month, 3=year, 4=hour, 5=minute
                                                             union all
                                                             select col_name = 'validate_subscriber_info', col_type = 'nvarchar(500) NULL'-- Dynamic partition rvalue validation (Shiloh)
                                                             union all
                                                             select col_name = 'ad_guidname', col_type = 'sysname NULL'-- Active directory registration for publications (Shiloh)
                                                             union all
                                                             select col_name = 'max_concurrent_merge', col_type = 'int not NULL default 0'-- max_concurrent_merge control the max # of concurrent merge process at publisher side (Shiloh)
                                                             union all
                                                             select col_name = 'max_concurrent_dynamic_snapshots', col_type = 'int not NULL default 0'-- Maximum number of current dynamic snapshot sessions
                                                             union all
                                                             select col_name = 'use_partition_groups', col_type = 'smallint NULL'
                                                             union all
                                                             select col_name = 'dynamic_filters_function_list', col_type = 'nvarchar(500) NULL'-- Semi-colon delimited list of functions used in all dynamic filters used in this publication
                                                             union all
                                                  select col_name = 'replicate_ddl', col_type = 'int not NULL default 0'-- Bitmask on how this publication accepts new objects
                                                             union all
                                                             select col_name = 'partition_id_eval_proc', col_type = 'sysname NULL'-- Partition id evaluation proc for this publication
                                                             union all
                                                             select col_name = 'publication_number', col_type = 'smallint identity NOT NULL' -- publication_number for this publication (just a mapped value to be used locally instead of the 16-byte guid)
                                                             union all
                                                             select col_name = 'allow_subscriber_initiated_snapshot', col_type = 'bit not NULL default 0'-- allow_subscriber_initiated_snapshot column
                                                             union all
                                                             select col_name = 'allow_partition_realignment', col_type = 'bit not NULL default 1' -- allow_partition_realignment column
                                                             union all
                                                             select col_name = 'generation_leveling_threshold', col_type = 'int null default 1000' -- generation leveling threshold
                                                             union all
                                                             select col_name = 'automatic_reinitialization_policy', col_type = 'bit not null default 0'-- whether or not to upload first on reinits that are triggered by certain publication/article property
changes
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergepublications', '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.sysmergepublications 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 goto error
        end
        close colcurs
        deallocate colcurs

        -- allow web sync (Yukon)
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'allow_web_synchronization')
        begin
            alter table dbo.sysmergepublications add allow_web_synchronization bit null default 0
            if @@error<>0 goto error

            -- we want to set allow_web_synchronization for ssce subscribers. So if we find a
            -- character mode snapshot type we will set this property.
            exec('update dbo.sysmergepublications set allow_web_synchronization = 1 where sync_mode = 1')
            if @@error<>0 goto error
        end

        --insure that index nc2sysmergepublications exists on status
        if exists( select * from sys.indexes where name = 'nc2sysmergepublications' AND
          			object_id = object_id('dbo.sysmergepublications') )
        begin
          	drop index nc2sysmergepublications on dbo.sysmergepublications
        end
     	 if exists(select * from syscolumns where id = object_id('sysmergepublications') and name = 'status')
	 begin
	 	create index nc2sysmergepublications on dbo.sysmergepublications(status)
	 end

				
	 --insure that default on generation_leveling_threshold is 1000 instead of 0 (or anything else)
	declare @defaultname sysname
	select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc
              	on sysdc.parent_object_id = sysc.object_id
                        and sysdc.parent_column_id = sysc.column_id
      		where sysdc.parent_object_id = object_id('sysmergepublications')
		       and sysc.name = 'generation_leveling_threshold'
	 if @defaultname is not null
	 begin
		select @alter_cmd = 'alter table dbo.sysmergepublications drop constraint ' + QUOTENAME(@defaultname)
		exec (@alter_cmd) -- drop old default		
	 end
	 if exists(select * from syscolumns where id = object_id('sysmergepublications') and name = 'generation_leveling_threshold')
	 begin
	 	select @alter_cmd = 'alter table dbo.sysmergepublications add default 1000 for generation_leveling_threshold'
		 exec (@alter_cmd) -- add new default
	 end
	
        -- conflict logging on both publisher and subscriber
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'decentralized_conflicts')
        begin
            alter table dbo.sysmergepublications add decentralized_conflicts int null
            if @@error<>0 goto error

            -- before upgrade, centralized_conflicts==1 means centralized logging,
            -- centralized_conflicts==0 means decentralized logging.
            -- We now map this to the two explicit columns.
            exec ('update dbo.sysmergepublications set decentralized_conflicts=1 where centralized_conflicts=0')
            if @@error<>0 goto error
            exec ('update dbo.sysmergepublications set decentralized_conflicts=0 where centralized_conflicts=1')
            if @@error<>0 goto error
        end

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

        -- in Yukon snapshot_jobid column has been moved from MSmerge_replinfo to sysmergepublications
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'snapshot_jobid')
        begin
            -- we cannot do alter table and update in the same batch so make this a dynamic sql
            alter table dbo.sysmergepublications add snapshot_jobid  binary(16) NULL
            if @@error <> 0 goto error
            -- get the values of snapshot_jobid from MSmerge_replinfo
            if exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'snapshot_jobid')
            begin
                -- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
                exec ('update dbo.sysmergepublications
                set snapshot_jobid = r.snapshot_jobid
                from dbo.sysmergepublications p, dbo.MSmerge_replinfo r
                where r.repid = p.pubid')
                if @@error <> 0
          goto error
            end
        end

        -- in Yukon distributor column has been moved from sysmergesubscriptions to sysmergepublications
        -- the following is only useful in shiloh to yukon upgrade. In 70 the column distributor did not even exist
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'distributor')
        begin
            -- we will move the value from sysmergesubscriptions to sysmergepublications
            alter table dbo.sysmergepublications add distributor sysname NULL
            if @@error<>0 goto error
            -- get the values of distributor from MSmerge_replinfo
            if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'distributor')
             and exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'publication')
            begin
                -- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
                -- add the colums for republisher's publications into sysmergepublications
                exec ('insert into dbo.sysmergepublications (pubid, name, publisher_db, publisher, distributor)
                select distinct s.pubid, s.publication, s.db_name, s.subscriber_server, s.distributor
                    from dbo.sysmergesubscriptions s, dbo.sysmergepublications p
                    where s.subid = s.pubid and s.pubid not in (select pubid from dbo.sysmergepublications)')
                 if @@error <> 0
                    goto error
                exec ('update dbo.sysmergepublications
                        set distributor = s.distributor
                        from dbo.sysmergesubscriptions s, dbo.sysmergepublications p
                        where s.subid = s.pubid and p.pubid = s.pubid')
                if @@error <> 0
                    goto error
            end
            else
            begin
                -- this is probably a 70 upgrade
                exec ('update dbo.sysmergepublications
                        set distributor = publisher')
                if @@error <> 0
                    goto error
            end
        end

        -- dynamic_snapshot_queue_timeout column this was in Yukon beta2 but has been removed since
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_queue_timeout')
        begin
            -- now drop the default constraint
            select @constraintname = quotename(name) from sys.default_constraints where parent_object_id = object_id('dbo.sysmergepublications') and parent_column_id =
            	(select column_id from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_queue_timeout')

            exec ('alter table dbo.sysmergepublications drop constraint ' +  @constraintname)
            if @@error<>0 goto error

            alter table dbo.sysmergepublications drop column dynamic_snapshot_queue_timeout
            if @@error<>0 goto error
        end

        -- dynamic_snapshot_ready_timeout column this was in Yukon beta2 but has been removed since
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_ready_timeout')
        begin
            -- now drop the default constraint
            select @constraintname = quotename(name) from sys.default_constraints where parent_object_id = object_id('dbo.sysmergepublications') and parent_column_id =
            	(select column_id from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_ready_timeout')

            exec ('alter table dbo.sysmergepublications drop constraint ' +  @constraintname)
            if @@error<>0 goto error

            alter table dbo.sysmergepublications drop column dynamic_snapshot_ready_timeout
            if @@error<>0 goto error
        end
    end -- end sysmergepublications modifications

    -- dbo.MSmerge_contents
    if object_id('dbo.MSmerge_contents') is not null and @remove_repl=0
    begin
    	-- insure that index nc4Msmerge_contents exists on rowguid
	if exists( select * from sys.indexes where name = 'nc4MSmerge_contents' and
		object_id = object_id('MSmerge_contents') )
	begin
		drop index nc4MSmerge_contents on dbo.MSmerge_contents
	end
	if exists(select * from syscolumns where id = object_id('MSmerge_contents') and name = 'rowguid')
	begin
		create index nc4MSmerge_contents on dbo.MSmerge_contents(rowguid)
	end
    end

    -- dbo.MSmerge_genhistory
    if object_id('dbo.MSmerge_genhistory') is not null and @remove_repl=0
    begin
	-- insure that index nc2Msmerge_genhistory exists on rowguid
	if exists( select * from sys.indexes where name = 'nc2MSmerge_genhistory' and
		object_id = object_id('MSmerge_genhistory') )
	begin
		drop index nc2MSmerge_genhistory on dbo.MSmerge_genhistory
	end
	if exists(select * from syscolumns where id = object_id('MSmerge_genhistory') and name = 'genstatus')
		and exists(select * from syscolumns where id = object_id('MSmerge_genhistory') and name = 'art_nick')
		and exists(select * from syscolumns where id = object_id('MSmerge_genhistory') and name = 'changecount')
	begin
		create  index nc2MSmerge_genhistory on MSmerge_genhistory(genstatus, art_nick,changecount)
	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 sys.indexes where name = 'nc1MSmerge_history' AND
                object_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

    -- dbo.MSmerge_replinfo
    if @remove_repl=0 and (object_id('MSmerge_replinfo') is not NULL and
    exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'replnickname'))
    begin
        -- recgen is int in Shiloh and before, bigint in Yukon and after
        if 56 = (select system_type_id from sys.columns where
                    object_id = object_id('MSmerge_replinfo') and name = 'recgen')
        begin
            alter table dbo.MSmerge_replinfo alter column recgen bigint null
            if @@error<>0 goto error
        end

        -- sentgen is int in Shiloh and before, bigint in Yukon and after
        if 56 = (select system_type_id from sys.columns where
                    object_id = object_id('MSmerge_replinfo') and name = 'sentgen')
        begin
            alter table dbo.MSmerge_replinfo alter column sentgen bigint null
            if @@error<>0 goto error
        end

        -- replnickname is int in Shiloh and before, binary(6) in Yukon and after
        if 56 = (select system_type_id from sys.columns where
                    object_id = object_id('MSmerge_replinfo') and name = 'replnickname')
        begin
            begin tran
            save tran tran_replinfonick80to90
                alter table dbo.MSmerge_replinfo alter column replnickname binary(6) not null
                if @@error<>0 goto err_replinfonick80to90
                exec ('update dbo.MSmerge_replinfo set replnickname= substring(replnickname, 6, 1) + substring(replnickname, 5, 1) + substring(replnickname, 4, 1) + substring(replnickname, 3, 1) + substring(replnickname, 2, 1) + substring(replnickname, 1,
 1)')
                if @@error<>0 goto err_replinfonick80to90
            commit tran
            goto after_replinfonick80to90

            err_replinfonick80to90:
            rollback tran tran_replinfonick80to90
            commit tran
            goto error
        end

        after_replinfonick80to90:

        -- this column and its values been added to sysmergepublications in the sysmergepublications if block
        if exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'snapshot_jobid')
        begin
            alter table dbo.MSmerge_replinfo drop column snapshot_jobid
            if @@error <> 0 goto error
        end

        -- hostname column added for Yukon
        if not exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'hostname')
        begin
            alter table dbo.MSmerge_replinfo add hostname sysname NULL
            if @@error <> 0 goto error
        end
    end -- dbo.MSmerge_replinfo

    /*
    * dbo.sysmergesubscriptions
    */
    -- the following modifications to sysmergesubscriptions have to be done even if replication is being removed
    if (object_id('sysmergesubscriptions') is not NULL)
    begin
        -- subscriber_server (Shiloh)
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
                            name = 'subscriber_server')
        begin
            alter table dbo.sysmergesubscriptions add subscriber_server sysname null
            if @@error <> 0 goto error

            -- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
            exec( N'update dbo.sysmergesubscriptions set subscriber_server =
                        (select srvname from master.dbo.sysservers where srvid = dbo.sysmergesubscriptions.srvid)' )
            if @@error <> 0 goto error
        end

        -- last_makegeneration_datetime
        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
                            name = 'last_makegeneration_datetime')
        begin
            alter table dbo.sysmergesubscriptions add last_makegeneration_datetime datetime null
            if @@error <> 0 goto error
        end



        if exists (select * from sysconstraints where id = object_id('dbo.sysmergesubscriptions') and
                            object_name(constid) = 'unique_pubsrvdb')
        begin
            alter table dbo.sysmergesubscriptions drop constraint unique_pubsrvdb
            if @@error <> 0 goto error
        end

        IF EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc2sysmergesubscriptions' AND
                        id = object_id('dbo.sysmergesubscriptions') )
        begin
            drop index nc2sysmergesubscriptions on dbo.sysmergesubscriptions
            if @@error <> 0 goto error
        end

        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
                            name = 'srvid')
        begin

            alter table dbo.sysmergesubscriptions drop column srvid
            if @@error <> 0 goto error
        end

        if not exists (select * from sysconstraints where id = object_id('dbo.sysmergesubscriptions') and
                            object_name(constid) = 'unique_pubsrvdb')
        begin
            exec(N'alter table dbo.sysmergesubscriptions
                    add constraint unique_pubsrvdb
                    unique nonclustered (pubid, subscriber_server, db_name)')
            if @@error <> 0 goto error
        end
    end

    if @remove_repl=0 and (object_id('sysmergesubscriptions') is not NULL)
    begin
        -- rename partnerid to replicastate... since we will be copying the table
        -- we will do this first so that the remaining changes will not need to be copied
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'partnerid')
        begin
            -- because sp_rename does not allow the renaming of tables marked as system objects
            -- we have to take this round about way of doing things. This table should always be
            -- relatively small so perf here is not a really big concern and should not be affected
            --  exec sys.sp_rename @objname = 'sysmergesubscriptions.partnerid', @newname = 'replicastate', @objtype = 'COLUMN'
            --  if @@error <> 0 goto error
            if object_id(N'sysmergesubscriptions_tmp_name') is not null
            begin
                drop table sysmergesubscriptions_tmp_name
                if @@error <> 0 goto error
            end

            select *
                into sysmergesubscriptions_tmp_name
                from dbo.sysmergesubscriptions
            if @@error <> 0 goto error

            exec sys.sp_rename @objname = 'sysmergesubscriptions_tmp_name.partnerid', @newname = 'replicastate', @objtype = 'COLUMN'
            if @@error <> 0
            begin
                drop table sysmergesubscriptions_tmp_name
                goto error
            end

            drop table sysmergesubscriptions
            if @@error <> 0 goto error

            exec sys.sp_rename @objname = 'sysmergesubscriptions_tmp_name', @newname = 'sysmergesubscriptions'
            if @@error <> 0
            begin
                drop table sysmergesubscriptions_tmp_name
                goto error
            end
  		

            -- recreate indexes
            create unique clustered index uc1sysmergesubscriptions on dbo.sysmergesubscriptions (subid)
            if @@error <> 0 goto error

            create index nc2sysmergesubscriptions on dbo.sysmergesubscriptions (subscriber_server, db_name)
            if @@error <> 0 goto error

            -- mark as system object
            exec sp_MS_marksystemobject 'sysmergesubscriptions'
            if @@error <> 0 goto error
        end

        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'use_interactive_resolver', col_type = 'bit NOT NULL default 0'-- Interactive resolver support (Shiloh)
                                                             union all
                                                             select col_name = 'validation_level', col_type = 'int NOT NULL default 0'-- merge validation level (Shiloh)
                                                             union all
                                                             select col_name = 'resync_gen', col_type = 'bigint not NULL default -1'
                                                             union all
                                                             select col_name = 'attempted_validate', col_type = 'datetime NULL' -- date of the last attempted validate (Shiloh)
                                                             union all
                                                             select col_name = 'last_sync_status', col_type = 'int NULL'-- status of the last sync (Shiloh)
                                                             union all
                                                             select col_name = 'last_sync_date', col_type = 'datetime NULL'-- date of the last sync (Shiloh)
                                                             union all
                                                             select col_name = 'last_sync_summary', col_type = 'sysname NULL'-- summary message of the last sync (Shiloh)
                                               union all
                                                             select col_name = 'metadatacleanuptime', col_type = 'datetime not NULL default getdate()'-- metadata cleanup time
                                                             union all
                                                             select col_name = 'cleanedup_unsent_changes', col_type = 'bit NOT NULL default 0'-- cleanedup_unsent_changes(Yukon)
                                                             union all
                                                             select col_name = 'replica_version', col_type = 'int NOT NULL default 60'-- replica_version (Yukon)
                                                             union all
                                                             select col_name = 'supportability_mode', col_type = 'int NOT NULL default 0'-- supportability_mode (Yukon)
                                                             union all
                                                             select col_name = 'application_name', col_type = 'sysname NULL'-- application_name and subscriber_number added in yukon
                                                             union all
                                                             select col_name = 'subscriber_number', col_type = 'int identity not NULL'
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergesubscriptions', '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.sysmergesubscriptions 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
            begin
            		goto error
            end

        end
        close colcurs
        deallocate colcurs

		-- drop old views
		declare @old_view_name sysname
		declare drop_old_views_cursor cursor LOCAL FAST_FORWARD FOR
		select name from sys.objects where (name like 'ctsv_%' or name like 'tsvw_%') AND type ='V' and  ObjectProperty(object_id, 'IsMSShipped')=1
		for read only
        open drop_old_views_cursor
        fetch drop_old_views_cursor into @old_view_name
		while(@@fetch_status <> -1)
		begin
			declare @drop_view_cmd nvarchar(max)
			select @drop_view_cmd = N'drop view ' + QUOTENAME(@old_view_name)
			exec(@drop_view_cmd)
			if @@error <> 0 goto error
			fetch drop_old_views_cursor into @old_view_name
		end	
		close drop_old_views_cursor
        deallocate drop_old_views_cursor


        IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc2sysmergesubscriptions' AND
            id = object_id('dbo.sysmergesubscriptions') )
        begin
            create index nc2sysmergesubscriptions on dbo.sysmergesubscriptions (subscriber_server, db_name)
            if @@error <> 0 goto error
        end

        -- Remove alternate_pubid column from sysmergesubscriptions (Shiloh)
        -- This column is dropped in 8.0 Beta 2
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
                            name = 'alternate_pubid')
        begin
            alter table dbo.sysmergesubscriptions drop column alternate_pubid
            if @@error <> 0 goto error
        end

        -- this column and its values been added to sysmergepublications in the sysmergepublications if block (yukon)
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'distributor')
        begin
            alter table sysmergesubscriptions drop column distributor
            if @@error <> 0 goto error
        end

        -- this column and its values been added to sysmergepublications in the sysmergepublications if block (yukon)
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'publication')
        begin
            alter table sysmergesubscriptions drop column publication
            if @@error <> 0 goto error
        end
    end



    -- move of columns from sysmergesubscriptions to MSmerge_replinfo and vice versa for the sake of
    -- better consistency and subscriber tracking.
    if @remove_repl=0 and object_id('sysmergesubscriptions') is not NULL and
         (object_id('MSmerge_replinfo') is not NULL and
         exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'replnickname'))
    begin
        select *
            into #sysmergesubscriptions
            from sysmergesubscriptions
        if @@error <> 0 goto error

        select *
            into #MSmerge_replinfo
            from MSmerge_replinfo
        if @@error <> 0 goto error
        begin tran
        save tran tran_upgrademergesubtables
            drop table dbo.sysmergesubscriptions
            if @@error<>0
                goto err_upgrademergesubtables

            drop table dbo.MSmerge_replinfo
            if @@error<>0
                goto err_upgrademergesubtables

            -- this creates the sysmergesubscription and MSmerge_replinfo tables with new schema
            exec @retcode = sys.sp_MSmerge_create_sub_table
            if @retcode<>0 or @@error<>0
                goto err_upgrademergesubtables
            -- insert the values for the new set of columns
            exec('insert into dbo.sysmergesubscriptions
                (subscriber_server, db_name, pubid, datasource_type, subid, replnickname,
                 replicastate, status, subscriber_type, subscription_type, sync_type,
                 description, priority, recgen, recguid, sentgen, sentguid,
                 schemaversion, schemaguid, last_validated, attempted_validate,
                 last_sync_date, last_sync_status, last_sync_summary,
                 metadatacleanuptime, cleanedup_unsent_changes)
            select sub.subscriber_server, sub.db_name, sub.pubid, sub.datasource_type, sub.subid, rep.replnickname,
                   sub.replicastate, sub.status, sub.subscriber_type, sub.subscription_type, sub.sync_type,
                   sub.description, sub.priority, rep.recgen, rep.recguid, rep.sentgen, rep.sentguid,
                   rep.schemaversion, rep.schemaguid, sub.last_validated, sub.attempted_validate,
                   sub.last_sync_date, sub.last_sync_status, sub.last_sync_summary,
                   sub.metadatacleanuptime, sub.cleanedup_unsent_changes
            from #sysmergesubscriptions sub, #MSmerge_replinfo rep
            where sub.subid = rep.repid')
            if @retcode<>0 or @@error<>0
                goto err_upgrademergesubtables
            exec('insert into dbo.MSmerge_replinfo
                (repid, use_interactive_resolver, validation_level, resync_gen,
                  login_name, merge_jobid)
            select rep.repid, sub.use_interactive_resolver, sub.validation_level, sub.resync_gen,
                  sub.login_name, rep.merge_jobid
            from #sysmergesubscriptions sub, #MSmerge_replinfo rep
            where sub.subid = rep.repid')
            if @retcode<>0 or @@error<>0
         goto err_upgrademergesubtables		

        commit tran
        goto after_upgrademergesubtables

        err_upgrademergesubtables:
        rollback tran tran_upgrademergesubtables
        commit tran
        goto error

        after_upgrademergesubtables:
        -- if we got here everything was successful.
        drop table #sysmergesubscriptions
        drop table #MSmerge_replinfo
    end

    if @remove_repl=0 and object_id('dbo.sysmergesubscriptions') is not NULL
    begin
	 --Dropping columns use_interactive_resolver, validation_level, resync_gen
	 if exists (select * from sys.columns where name = N'use_interactive_resolver' and object_id = object_id('dbo.sysmergesubscriptions'))
	 begin
	 	-- drop constraint on this if it exists
	 	select @defaultname = null --temp stores constraint name to be removed
		select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
				(sysdc.parent_object_id = sysc.object_id and
					sysdc.parent_column_id = sysc.column_id)		
			where sysc.object_id = object_id('dbo.sysmergesubscriptions') and
				sysc.name = 'use_interactive_resolver'
	 	if @defaultname is not null
	 	begin
	 		select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop constraint ' + QUOTENAME(@defaultname)
	 		exec (@alter_cmd)
	 	end
	 	-- drop column for Yukon
	 	select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop column use_interactive_resolver'
	 	exec (@alter_cmd)
	 end
	 if exists (select * from sys.columns where name = N'validation_level' and object_id = object_id('dbo.sysmergesubscriptions'))	
	 begin
	 	-- drop constraint on this if it exists
	 	select @defaultname = null -- temp stores constraint name to be removed
		select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
				(sysdc.parent_object_id = sysc.object_id and
					sysdc.parent_column_id = sysc.column_id)		
			where sysc.object_id = object_id('dbo.sysmergesubscriptions') and
					sysc.name = 'validation_level'
	 	if @defaultname is not null
	 	begin
	 		select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop constraint ' + QUOTENAME(@defaultname)
	 		exec (@alter_cmd)
	 	end
	 	-- drop column for Yukon
	 	select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop column validation_level'
	 	exec (@alter_cmd)
	 end
	 if exists (select * from sys.columns where name = N'resync_gen' and object_id = object_id('dbo.sysmergesubscriptions'))
	 begin	
	 	-- constraint (default) must be deleted before we can delete resync_gen
		select @defaultname = null --temp stores constraint name to be removed
	 	select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
				(sysdc.parent_object_id = sysc.object_id and
					sysdc.parent_column_id = sysc.column_id)		
			where sysc.object_id = object_id('dbo.sysmergesubscriptions') and
					sysc.name = 'resync_gen'
	 	if @defaultname is not null
	 	begin
	 		select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop constraint ' + QUOTENAME(@defaultname)
	 		exec (@alter_cmd)
	 	end
	 	-- drop column for Yukon
	 	select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop column resync_gen'
	 	exec (@alter_cmd)
	 end
	 	
	 --insure index nc3sysmergesubscriptions exists on replnickname
	 if exists( select * from sys.indexes where name = 'nc3sysmergesubscriptions' and object_id = object_id('dbo.sysmergesubscriptions') )
	 begin
	 	drop index dbo.sysmergesubscriptions.nc3sysmergesubscriptions
	 end
	 if exists(select * from syscolumns where id = object_id('dbo.sysmergesubscriptions') and name = 'replnickname')
	 begin  -- if column replnickname exists create the index on it
	 	create index nc3sysmergesubscriptions on dbo.sysmergesubscriptions(replnickname)
	 end
    end


    /*
     * dbo.sysmergearticles
    */
    -- the following have to be done for sp_MSremovedbreplication to work correctly
    -- even we are going to remove replication
    if object_id('sysmergearticles') is not NULL
    begin
        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'lightweight', col_type = 'bit not null default 0'
                                                             union all
                                                             select col_name = 'before_upd_view_objid', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'metadata_select_proc', col_type = 'sysname NULL'
                                                             union all
                                                             select col_name = 'delete_proc', col_type = 'sysname NULL'
                                                             union all
                                                             select col_name = 'before_image_objid', col_type = 'int NULL'-- Keep partition changes (SQL7.0 SP2)
                                                             union all
                                                             select col_name = 'before_view_objid', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'preserve_rowguidcol', col_type = 'bit not null default 1'
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergearticles', '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.sysmergearticles 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 goto error
        end
        close colcurs
        deallocate colcurs
    end

    if @remove_repl=0 and object_id('sysmergearticles') is not NULL
    begin
        -- Set all invalid dbo.sysmergearticles.sync_objid to the corresponding
        -- objid, this will allow regeneration of article procs to succeed
        update dbo.sysmergearticles
           set sync_objid = objid
         where object_name(sync_objid) is null
        if @@error <> 0 goto error

        -- Make sure that article type in dbo.sysmergearticles is not null
        -- for upgraded republisher
        update dbo.sysmergearticles
        set type = 0x0a
        where type is null
        if @@error <> 0 goto error

        -- Turn on the trigger schema option, FK schema option, check
        -- constraint schema option, and default schema option    by default
        -- so merge articles will retain the old scripting behaviour (Shiloh)
        -- Also reset the 0x8000 option (PKUK as constraints) as it is
        -- deprecated starting from yukon.
        update dbo.sysmergearticles
            set schema_option = (convert(bigint, schema_option) | convert(bigint, 0x00000F00)) & ~convert(bigint, 0x00008000)
            from sys.objects so
            inner join dbo.sysmergearticles sa
            on so.object_id = sa.objid
            where so.type = 'U' -- table articles only
        if @@error <> 0 goto error

        -- Add new non-clustered idx on nickname.
        if not exists (select * from sys.indexes where name = 'nc1sysmergearticles')
        begin
            create nonclustered index nc1sysmergearticles on dbo.sysmergearticles(nickname)
            if @@error <> 0 goto error
        end

        -- Resolver info column datatype change
        if exists( select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = 'resolver_info' )
        begin
            alter table dbo.sysmergearticles alter column resolver_info nvarchar(517) NULL
            if @@error <> 0 goto error
        end

        exec @retcode = sys.sp_MSUpgradeConflictTable
        if @@ERROR<>0 or @retcode<>0
            goto error

        if object_id('MSmerge_delete_conflicts') is not NULL
        begin
            drop table dbo.MSmerge_delete_conflicts
            if @@error <> 0 goto error
        end

        /* Update the columns column sysmergearticles by counting the number of columns int the
           table. 70 did not have vertical partitioning so the columns column is NULL */
        declare articlescurs cursor LOCAL FAST_FORWARD
        for (select sma.name, sma.objid, sma.pubid from dbo.sysmergearticles sma
                    where sma.columns is NULL and sys.fn_MSmerge_islocalpubid(pubid) = 1)

        for read only
        open articlescurs
        fetch articlescurs into @article, @objid, @pubid
        while(@@fetch_status <> -1)
        begin


            SELECT @cnt = max(column_id), @idx = 1 FROM sys.columns WHERE object_id = @objid
            SELECT @columns = NULL
            WHILE @idx <= @cnt
            BEGIN
                /* to make sure column holes will not be included */
                if exists (select * from sys.columns where column_id=@idx and object_id=@objid and
                    (is_computed<>1 and system_type_id <> type_id('timestamp')))
                begin
                    exec sys.sp_MSsetbit @bm=@columns OUTPUT, @coltoadd=@idx, @toset = 1
                    if @@ERROR<>0 or @retcode<>0
                    begin
                        close articlescurs
                        deallocate articlescurs
                        goto error
                    end

                end
                SELECT @idx = @idx + 1
            END
            UPDATE dbo.sysmergearticles SET columns = @columns WHERE name = @article AND pubid = @pubid

            fetch articlescurs into @article, @objid, @pubid
        end

        close articlescurs
        deallocate articlescurs

        if @@error <> 0 goto error

        if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and
                            name = 'excluded_col_count')
        begin
            -- make sure 7.5's column bit map does not get messed up.
            if exists (select * from dbo.sysmergearticles)
            begin
            -- enough to hold 246 columns in one table - after upgrade all columns are in partition
            -- as in sphinx there is no way to kick any of them out.
            -- in case a publication exists in downlevel subscriber without having ran snapshot.
            -- missing_col_count would be NULL, which can cause problems when being upgraded to
            -- latest version.
                update dbo.sysmergearticles set missing_col_count=0,missing_cols=0x00 where missing_col_count is NULL
                if @@error <> 0 goto error
            end

            alter table dbo.sysmergearticles add excluded_col_count int NULL
           if @@error <> 0 goto error
        end

        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'gen_cur', col_type = 'bigint NULL'-- Current generation for new generation assignment added in SQL7.0 SP1
                                                             union all
                                                             select col_name = 'excluded_cols', col_type = 'varbinary(128) NULL'-- Vertical Partitioning (Shiloh)
                                                             union all
                                                             select col_name = 'vertical_partition', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'identity_support', col_type = 'int default 0 NOT NULL '-- Auto identity management (Shiloh)
                                                             union all
                                                             select col_name = 'destination_owner', col_type = 'sysname default ''dbo'' not NULL'-- Destination owner support (Shiloh)
                                                             union all
                                                             select col_name = 'verify_resolver_signature', col_type = 'int NULL default 1'-- Resolver certificate support (Shiloh)
                                                             union all
                                                             select col_name = 'allow_interactive_resolver', col_type = 'bit NOT NULL default 0'-- Interactive resolver (Shiloh)
                                                             union all
                                                             select col_name = 'published_in_tran_pub', col_type = 'bit NOT NULL default 0'
                                                             union all
                                                             select col_name = 'fast_multicol_updateproc', col_type = 'bit NOT NULL default 0'-- Whether update proc should do one update per column or multiple columns in one update (Shiloh)
                                                             union all
                                                             select col_name = 'check_permissions', col_type = 'int NOT NULL default 0'
                                                             union all
                                                             select col_name = 'processing_order', col_type = 'int NOT NULL default 0'
                                                             union all
                                                             select col_name = 'maxversion_at_cleanup', col_type = 'int not null default 1'
                                                             union all
                                                             select col_name = 'upload_options', col_type = 'tinyint not null default 0'
                                                             union all
                                                             select col_name = 'procname_postfix', col_type = 'nchar(32) null'
                                                             union all
                                                             select col_name = 'well_partitioned_lightweight', col_type = 'bit null'
                                                             union all
                                                             select col_name = 'delete_tracking', col_type = 'bit not null default 1'
                                                             union all
                                                             select col_name = 'compensate_for_errors', col_type = 'bit not null default 0'
                                                             union all
                                     select col_name = 'pub_range', col_type = 'bigint null'
                                                             union all
                                                             select col_name = 'range', col_type = 'bigint NULL'
                                                             union all
                                                             select col_name = 'threshold', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'stream_blob_columns', col_type = 'bit not NULL default 0'
                                                             union all
                                                             select col_name = 'deleted_cols', col_type = 'varbinary(128) NULL default 0x0'
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergearticles', '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.sysmergearticles 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 goto error
        end
        close colcurs
        deallocate colcurs

		-- add default constraint on verify_resolver_signature column
		if not exists(
		select *
        from sysconstraints as con join sys.columns as col
            on con.colid = col.column_id
                and con.id = col.object_id
                and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1
                and col.object_id = object_id('dbo.sysmergearticles')
                and col.name = 'verify_resolver_signature')and exists
				(select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = 'verify_resolver_signature')
		begin
			exec('alter table dbo.sysmergearticles add default 1 for verify_resolver_signature')
			if @@error <> 0 goto error	
		end

		
		-- change default constraint on compensate_for_errors column to be 0 and also update all values for this column to be 0
		-- we think that compensate_for_errors=1 (sql2k default)is not very usefull and can cause more harm then good.
		if exists(select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = 'compensate_for_errors')
		begin
			declare @default_compensate_for_errors_constraint_name nvarchar(258)
			select @default_compensate_for_errors_constraint_name = obj.name
			from sysconstraints as con join sys.columns as col
				on con.colid = col.column_id
					and con.id = col.object_id
					and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1
					and col.object_id = object_id('dbo.sysmergearticles')
					and col.name = 'compensate_for_errors'
					join sys.objects as obj
					on obj.object_id=con.constid

			if(@default_compensate_for_errors_constraint_name is not  null)
			begin
				select @default_compensate_for_errors_constraint_name = quotename(@default_compensate_for_errors_constraint_name)
				exec ('alter table dbo.sysmergearticles  drop constraint ' + @default_compensate_for_errors_constraint_name)
				if @@error <> 0 goto error
			end			
			exec('alter table dbo.sysmergearticles add default 0 for compensate_for_errors')
			if @@error <> 0 goto error
			exec('update dbo.sysmergearticles set compensate_for_errors = 0 ')			
			if @@error <> 0 goto error
		end

        -- gen_cur is int in SQL8 and earlier, bigint in SQL9
        if 56 = (select system_type_id from sys.columns where
                        object_id = object_id('dbo.sysmergearticles') and name = 'gen_cur')
        begin
            alter table dbo.sysmergearticles alter column gen_cur bigint null
            if @@error <> 0 goto error
        end

        -- Set default value of column destination_owner if NULL - could happen if first upgraded
        -- from 7.0 to Beta 2, which does not have the default value and then to 80 RTM.

        -- Destination owner support (Shiloh)
        if exists (select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and
                        name = 'destination_owner')
        begin
            exec ('update dbo.sysmergearticles set destination_owner=''dbo'' where destination_owner is NULL')
            if @@error <> 0 goto error
        end

        if not exists (select * from sys.indexes where name = 'nc2sysmergearticles')
        begin
            create nonclustered index nc2sysmergearticles on sysmergearticles(processing_order)
            if @@error <> 0 goto error
        end

        if not exists (select * from sys.indexes where name = 'nc3sysmergearticles')
        begin
            create unique nonclustered index nc3sysmergearticles on dbo.sysmergearticles(objid, pubid)
            if @@ERROR <> 0    goto error
        end
        
        -- add default for compensate_for_errors
        
        if not exists (select 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(N'dbo.sysmergearticles')
                            and c.name = N'compensate_for_errors')
        begin
            alter table dbo.sysmergearticles add default 0 for compensate_for_errors
        end
        
        -- add default for excluded_col_count
        
        if not exists (select dc.name
                        from sys.default_constraints as dc
                            
 
Last revision SQL2008SP2
See also

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