June 15, 2012

sp_vupgrade_mergetables (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_vupgrade_mergetables(bit @remove_repl)

MetaData:

 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 @additive_resolver nvarchar(80)
declare @average_resolver nvarchar(80)
declare @download_resolver nvarchar(80)
declare @max_resolver nvarchar(80)
declare @mergetxt_resolver nvarchar(80)
declare @min_resolver nvarchar(80)
declare @subwins_resolver nvarchar(80)
declare @upload_resolver nvarchar(80)
declare @sp_resolver nvarchar(80)
declare @priority_resolver nvarchar(80)
declare @earlierwins_resolver nvarchar(80)
declare @laterwins_resolver nvarchar(80)

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 = '{8FE7FF34-7C5D-4BE7-8056-ADB6D6F692DC}'
select @average_resolver_clsid = '{376F678E-4691-43E8-8AE7-DAD8CAA644EF}'
select @download_resolver_clsid = '{3BB9F418-3407-4F5B-8DB3-9E9147C3A710}'
select @max_resolver_clsid = '{7365BF95-62E8-4B72-A0F7-E238FE413DB7}'
select @mergetxt_resolver_clsid = '{9DCD5250-86BB-433D-8C1F-561460105CF0}'
select @min_resolver_clsid = '{93277AB4-C338-48B8-9A4A-CA5A32587AB7}'
select @subwins_resolver_clsid = '{77E52C5E-0016-4EDF-9391-8C07BFB668CE}'
select @upload_resolver_clsid = '{3D43EBE7-063C-4447-91E7-DE7A264C8441}'
select @sp_resolver_clsid = '{3BB074FA-0836-4A63-BE0C-AF49DDD42A1C}'

select @additive_resolver = 'Microsoft SQL Server Additive Conflict Resolver'
select @average_resolver = 'Microsoft SQL Server Averaging Conflict Resolver'
select @download_resolver = 'Microsoft SQL Server Download Only Conflict Resolver'
select @max_resolver = 'Microsoft SQL Server Maximum Conflict Resolver'
select @mergetxt_resolver = 'Microsoft SQL Server Merge Text Columns Conflict Resolver'
select @min_resolver = 'Microsoft SQL Server Minimum Conflict Resolver'
select @subwins_resolver = 'Microsoft SQL Server Subscriber Always Wins Conflict Resolver'
select @upload_resolver = 'Microsoft SQL Server Upload Only Conflict Resolver'
select @sp_resolver = 'Microsoft SQLServer Stored Procedure Resolver'
select @priority_resolver = 'Microsoft SQL Server Priority Column Resolver'
select @earlierwins_resolver = 'Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver'
select @laterwins_resolver = 'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver'

select @dbname = db_name()

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


if object_id('sysmergearticles') is not NULL
begin
-- Update to 110 resolver clsids
update dbo.sysmergearticles set resolver_clsid =
case article_resolver
when @additive_resolver then @additive_resolver_clsid
when @average_resolver then @average_resolver_clsid
when @download_resolver then @download_resolver_clsid
when @max_resolver then @max_resolver_clsid
when @mergetxt_resolver then @mergetxt_resolver_clsid
when @min_resolver then @min_resolver_clsid
when @subwins_resolver then @subwins_resolver_clsid
when @upload_resolver then @upload_resolver_clsid
when @sp_resolver then @sp_resolver_clsid
when @priority_resolver then @max_resolver_clsid
when @earlierwins_resolver then @min_resolver_clsid
when @laterwins_resolver then @max_resolver_clsid
else resolver_clsid
end
end

-- 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
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = N'allow_partition_realignment' and is_nullable = 0)
alter table dbo.sysmergepublications alter column allow_partition_realignment bit null

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
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = N'delete_tracking' and is_nullable = 0)
alter table dbo.sysmergearticles alter column delete_tracking bit null
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
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'excluded_col_count')
begin
alter table dbo.sysmergearticles add default 0 for excluded_col_count
end
--
-- add default for vertical_partition
--
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'vertical_partition')
begin
alter table dbo.sysmergearticles add default 0 for vertical_partition
end
--
-- remove the default for column destination_owner
--
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.sysmergearticles')
and c.name = N'destination_owner'
if (@column_name is not null)
begin
select @alter_cmd = N'alter table dbo.sysmergearticles drop constraint ' + quotename(@column_name)
exec(@alter_cmd)
if @@error <> 0
return 1
end

-- this table exists at publisher and subscriber dbs
if object_id('MSmerge_identity_range') is NULL
begin
create table dbo.MSmerge_identity_range (
subid uniqueidentifier not NULL,
artid uniqueidentifier not NULL,
range_begin numeric(38,0) NULL,
range_end numeric(38,0) NULL,
next_range_begin numeric(38,0) NULL,
next_range_end numeric(38,0) NULL,
is_pub_range bit not NULL,
max_used numeric(38,0) NULL
)
if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_identity_range
if @@error <> 0 goto error

create unique clustered index uclidrange on MSmerge_identity_range(subid, artid, is_pub_range)
if @@error <> 0 goto error
end

if object_id('MSmerge_settingshistory') is NULL
begin
-- raiserror('Creating table MSmerge_settingshistory',0,1)

-- This table records the history of when merge related settings
-- were changed. It can also bo used to record important events
-- that affect behavior of merge replication.

-- eventtype can have one of the following values
-- 1 Initial publication level property setting.
-- 2 Change in publication property.
-- 101 Initial article level property setting.
-- 102 Change in article property.
-- In future add publication related event below 100 and
-- article related events about 100 to make searching easier

create table dbo.MSmerge_settingshistory
(
eventtime datetime null default getdate(),
pubid uniqueidentifier NOT NULL,
artid uniqueidentifier NULL,
eventtype tinyint NOT NULL,
propertyname sysname NULL,
previousvalue sysname NULL,
newvalue sysname NULL,
eventtext nvarchar(2000) NULL
)

if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_settingshistory
if @@error <> 0 goto error

create clustered index c1MSmerge_settingshistory on MSmerge_settingshistory(pubid,eventtype)
if @@error <> 0 goto error

end
else
begin
if exists (select * from sys.columns where object_id = object_id('dbo.MSmerge_settingshistory') and name = N'eventtime' and is_nullable = 0)
alter table dbo.MSmerge_settingshistory alter column eventtime datetime null
end

if object_id('sysmergepartitioninfo') is NULL
begin
create table dbo.sysmergepartitioninfo
(
artid uniqueidentifier NOT NULL,
pubid uniqueidentifier NOT NULL,
partition_view_id int NULL,
repl_view_id int NULL,
partition_deleted_view_rule nvarchar(max) NULL,
partition_inserted_view_rule nvarchar(max) NULL,
membership_eval_proc_name sysname NULL,
column_list nvarchar(max) NULL,
column_list_blob nvarchar(max) NULL,
expand_proc sysname NULL,
logical_record_parent_nickname int NULL,
logical_record_view int NULL,
logical_record_deleted_view_rule nvarchar(max) NULL,
logical_record_level_conflict_detection bit null default 0,
logical_record_level_conflict_resolution bit null default 0,
partition_options tinyint null default 0
)
if @@error <> 0 goto error

create unique clustered index uc1sysmergepartitioninfo
on dbo.sysmergepartitioninfo(artid, pubid)
if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject sysmergepartitioninfo
if @@error <> 0 goto error

-- we need to insert a row for every article in sysmergearticles into sysmergepartitioninfo
insert dbo.sysmergepartitioninfo (artid, pubid)
select artid, pubid from dbo.sysmergearticles
if @@error <> 0 goto error

end
else
begin
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepartitioninfo') and name = N'logical_record_level_conflict_detection' and is_nullable = 0)
alter table dbo.sysmergepartitioninfo alter column logical_record_level_conflict_detection bit null
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepartitioninfo') and name = N'logical_record_level_conflict_resolution' and is_nullable = 0)
alter table dbo.sysmergepartitioninfo alter column logical_record_level_conflict_resolution bit null
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepartitioninfo') and name = N'partition_options' and is_nullable = 0)
alter table dbo.sysmergepartitioninfo alter column partition_options tinyint null
end

if object_id('sysmergepartitioninfoview') is not NULL
begin
drop view dbo.sysmergepartitioninfoview
end

exec ('create view dbo.sysmergepartitioninfoview as
select sma.*, smaw.partition_view_id,
smaw.repl_view_id,
smaw.partition_deleted_view_rule,
smaw.partition_inserted_view_rule,
smaw.membership_eval_proc_name,
smaw.column_list,
smaw.column_list_blob,
smaw.expand_proc,
smaw.logical_record_parent_nickname,
smaw.logical_record_view,
smaw.logical_record_deleted_view_rule,
smaw.logical_record_level_conflict_detection,
smaw.logical_record_level_conflict_resolution,
smaw.partition_options
from dbo.sysmergearticles sma, dbo.sysmergepartitioninfo smaw
where sma.artid = smaw.artid and sma.pubid = smaw.pubid'
)
if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject sysmergepartitioninfoview
if @@error <> 0 goto error

-- Schema only articles (Shiloh)
if object_id('dbo.sysmergeschemaarticles', 'U') is null
begin
exec @retcode= sys.sp_MScreate_sysmergeschemaarticles_table
if @@error <> 0 or @retcode <> 0 goto error
end
else
begin
if not exists (select * from sys.columns where object_id = object_id('sysmergeschemaarticles') and
name = 'processing_order')
begin
alter table sysmergeschemaarticles add processing_order int NOT NULL default 0
if @@error <> 0 goto error
end
end

-- create view now that sysmergearticles is altered and sysmergeextendedarticles is created
if object_id('sysmergeextendedarticlesview') is not NULL
begin
drop view dbo.sysmergeextendedarticlesview
end

-- cannot create view directly in proc
exec @retcode= sys.sp_MScreate_sysmergeextendedarticlesview
if @@error<>0 or @retcode<>0 goto error

exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview

-- vertical partitioning requires a view based sync obj; SQL7.0 used zero as sync_objid
-- when a non-partitioned article was created; later versions must use the explicit base table
-- as sync_objid; fixup article sync_objid's prior to remaking the article procs (Shiloh)
update dbo.sysmergearticles set sync_objid = objid where sync_objid = 0

-- Do not regenerate views, procs if this is called from sp_restoredbreplication. Restore only
-- needs to update schema, then it can call existing system procs to remove db replication cleanly
if @remove_repl = 0
begin
-- when upgrading to yukon we have a huge amount of metadata upgrade to do. We do not want to
-- do this as part of the upgrade or restore process. We want the subsequent merge or snapshot to
-- take care of it. Hence here we will drop all triggers on publisher tables and create triggers
-- which do not allow the DML to happen. When the snapshot or merge has been run valid triggers will
-- be created and change tracking with work fine after the metadata has been upgraded.
select @artnick = min(nickname) from dbo.sysmergearticles
while @artnick is not null
begin
select @objid = NULL
select @source_object = NULL
select top 1 @objid = objid, @artid = artid from dbo.sysmergearticles where nickname = @artnick
select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
if @objid is NULL or @source_object is NULL
goto error

exec @retcode = sys.sp_MSdroparticletriggers @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- generate the disable dml trigger
exec sys.sp_MScreatedisabledmltrigger @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- set the article status to inactive so that the subsequent snapshot prepares the article
update dbo.sysmergearticles set status = 1 where artid = @artid and status = 2
update dbo.sysmergearticles set status = 5 where artid = @artid and status = 6

-- find next article
select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick
end -- end article while
end -- end @remove_repl

-- MSmerge_errorlineage (Shiloh)
if object_id('MSmerge_errorlineage') is NULL
begin
create table dbo.MSmerge_errorlineage (
tablenick int NOT NULL,
rowguid uniqueidentifier NOT NULL,
lineage varbinary(311) null
)
exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
if @@ERROR <> 0
goto error

create unique clustered index uc1errorlineage on MSmerge_errorlineage(tablenick, rowguid)
if @@ERROR <> 0
goto error
end
else
begin
if exists (select * from sys.columns where object_id = object_id('dbo.MSmerge_errorlineage') and name = N'lineage' and is_nullable = 0)
alter table dbo.MSmerge_errorlineage alter column lineage varbinary(311) null
end

-- lineage is varbinary(255) in Shiloh and before, varbinary(311) in Yukon and after
-- in addition, the format has changed from 4 to 6 byte nicknames, and there are new mergenicks
if 311 > col_length('MSmerge_errorlineage', 'lineage')
begin
begin tran
save tran tran_errlin80to90
alter table dbo.MSmerge_errorlineage alter column lineage varbinary(311) not null
if @@error<>0 goto err_errlin80to90
update dbo.MSmerge_errorlineage set lineage= {fn LINEAGE_80_TO_90(lineage)}
if @@error<>0 goto err_errlin80to90
commit tran
goto after_errlin80to90

err_errlin80to90:
rollback tran tran_errlin80to90
commit tran
goto error
end

after_errlin80to90:

-- MSmerge_altsyncpartners (Shiloh)
if object_id('MSmerge_altsyncpartners') is NULL
begin
create table dbo.MSmerge_altsyncpartners (
subid uniqueidentifier not null,
alternate_subid uniqueidentifier not null,
description nvarchar(255) NULL
)
if @@ERROR <> 0
goto error

exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners
if @@ERROR <> 0
goto error

create unique clustered index uciMSmerge_altsyncpartners on
dbo.MSmerge_altsyncpartners(subid, alternate_subid)
if @@ERROR <> 0
goto error
end

-- new tables added for the first time after SQL2000.
if object_id('MSmerge_partition_groups') is NULL
begin
create table dbo.MSmerge_partition_groups (partition_id int identity not null primary key clustered,
publication_number smallint not null, maxgen_whenadded bigint null,
using_partition_groups bit null default 0, is_partition_active bit default 1 not null)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_partition_groups
if @@ERROR <> 0 goto error

create nonclustered index nc1MSmerge_partition_groups on dbo.MSmerge_partition_groups (publication_number)
if @@ERROR <> 0 goto error

grant select on dbo.MSmerge_partition_groups to public
end
else
begin
-- Column is_partition_active was added after IDW15 for SQL 2005
if not exists (select * from sys.columns where object_id = object_id('MSmerge_partition_groups') and
name = 'is_partition_active')
begin
alter table MSmerge_partition_groups add is_partition_active bit default 1 not null
if @@error <> 0 goto error
end

if exists (select * from sys.columns where object_id = object_id('dbo.MSmerge_partition_groups') and name = N'using_partition_groups' and is_nullable = 0)
alter table dbo.MSmerge_partition_groups alter column using_partition_groups bit null
end

if object_id('MSmerge_generation_partition_mappings') is NULL
begin
create table dbo.MSmerge_generation_partition_mappings
(
publication_number smallint not null,
generation bigint not null,
partition_id int not null,
changecount int NOT NULL default 0
)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_generation_partition_mappings
if @@ERROR <> 0 goto error

create clustered index cMSmerge_generation_partition_mappings on dbo.MSmerge_generation_partition_mappings (partition_id, publication_number)
if @@ERROR <> 0 goto error

create nonclustered index nc1MSmerge_generation_partition_mappings on dbo.MSmerge_generation_partition_mappings (generation) include (changecount)
if @@ERROR <> 0 goto error
end

if object_id('MSmerge_current_partition_mappings') is NULL
begin
create table dbo.MSmerge_current_partition_mappings (publication_number smallint not null, tablenick int not null, rowguid uniqueidentifier not null, partition_id int not null)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_current_partition_mappings
if @@ERROR <> 0 goto error

create clustered index cMSmerge_current_partition_mappings on dbo.MSmerge_current_partition_mappings (tablenick, rowguid)
if @@ERROR <> 0
goto error

create nonclustered index ncMSmerge_current_partition_mappings on dbo.MSmerge_current_partition_mappings (publication_number, partition_id)
if @@ERROR <> 0
goto error

end

if object_id('MSmerge_past_partition_mappings') is NULL
begin
create table dbo.MSmerge_past_partition_mappings (publication_number smallint not null, tablenick int not null, rowguid uniqueidentifier not null, partition_id int not null, generation bigint null, reason tinyint not null default(0))
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_past_partition_mappings
if @@ERROR <> 0 goto error

create clustered index cMSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (tablenick, rowguid)
if @@ERROR <> 0
goto error

create nonclustered index nc1MSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (publication_number, partition_id)
if @@ERROR <> 0
goto error

create nonclustered index nc2MSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (generation)
if @@ERROR <> 0
goto error
end

if object_id('MSmerge_dynamic_snapshots') is NULL
begin
create table dbo.MSmerge_dynamic_snapshots (
partition_id int not null primary key clustered foreign key references dbo.MSmerge_partition_groups(partition_id) on delete cascade,
dynamic_snapshot_location nvarchar(255) null,
last_updated datetime null,
last_started datetime null)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_dynamic_snapshots
if @@ERROR <> 0 goto error
end
else
begin
if not exists (select * from sys.columns where object_id = object_id('MSmerge_dynamic_snapshots') and
name = 'last_started')
begin
alter table MSmerge_dynamic_snapshots add last_started datetime NULL
if @@error <> 0 goto error
end
end

-- Added in Yukon
if object_id('MSmerge_supportability_settings') is NULL
begin
create table dbo.MSmerge_supportability_settings (
pubid uniqueidentifier NULL,
subid uniqueidentifier NULL,
web_server sysname NULL,
constraint unique_supportpubsrvdb unique nonclustered (pubid, subid, web_server),
support_options int NOT NULL default(0), -- check the SUPPORT_OPTIONS enum in agent code.
log_severity int NOT NULL default(2),
log_modules int NOT NULL default(0),
log_file_path nvarchar(255) NULL,
log_file_name sysname NULL,
log_file_size int NOT NULL default(10000000),
no_of_log_files int NOT NULL default(5),
upload_interval int NOT NULL default(0),
delete_after_upload int NOT NULL default(0),
custom_script nvarchar(2048) NULL,
message_pattern nvarchar(2000) NULL,
last_log_upload_time datetime NULL,
agent_xe varbinary(max) NULL,
agent_xe_ring_buffer varbinary(max) NULL,
sql_xe varbinary(max) NULL
)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_supportability_settings
end
else
begin
-- Column agent_xe was added for SQL 11
if not exists (select * from sys.columns where object_id = object_id('MSmerge_supportability_settings') and
name = 'agent_xe')
begin
alter table MSmerge_supportability_settings add agent_xe varbinary(max)
if @@ERROR <> 0 goto error
end

-- Column agent_xe_ring_buffer was added for SQL 11
if not exists (select * from sys.columns where object_id = object_id('MSmerge_supportability_settings') and
name = 'agent_xe_ring_buffer')
begin
alter table MSmerge_supportability_settings add agent_xe_ring_buffer varbinary(max)
if @@ERROR <> 0 goto error
end

-- Column sql_xe was added for SQL 11
if not exists (select * from sys.columns where object_id = object_id('MSmerge_supportability_settings') and
name = 'sql_xe')
begin
alter table MSmerge_supportability_settings add sql_xe varbinary(max)
if @@ERROR <> 0 goto error
end

end

-- Added in Yukon
if object_id('MSmerge_log_files') is NULL
begin
create table dbo.MSmerge_log_files (
id int identity(1,1),
pubid uniqueidentifier NULL,
subid uniqueidentifier NULL,
web_server sysname NULL,
file_name nvarchar(2000) NOT NULL,
upload_time datetime NOT NULL default getdate(),
log_file_type int NOT NULL, -- Check UPLOAD_LOG_FILE_TYPE enum in agent code.
log_file varbinary(max) NULL
)
if @@ERROR <> 0 goto error

create clustered index ucMSmerge_log_files on MSmerge_log_files(pubid, subid, id)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_log_files
end

-- Added in Yukon
if object_id('dbo.MSmerge_metadataaction_request', 'U') is null
begin
create table dbo.MSmerge_metadataaction_request
(
tablenick int not null,
rowguid uniqueidentifier not null,
action tinyint not null,
generation bigint null, -- for hws cleanup
changed int null -- for lws cleanup
)
if @@ERROR <> 0 goto error

create clustered index ucMSmerge_metadataaction_request on MSmerge_metadataaction_request(tablenick, rowguid)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_metadataaction_request
if @@ERROR <> 0 goto error
end

-- Added in Yukon
if object_id('dbo.MSmerge_agent_parameters', 'U') is null
begin
-- raiserror('Creating table MSmerge_agent_parameters',0,1)

create table dbo.MSmerge_agent_parameters
(
profile_name sysname NOT NULL,
parameter_name sysname NOT NULL,
value nvarchar(255) NOT NULL
)

if @@ERROR <> 0
goto error

exec dbo.sp_MS_marksystemobject MSmerge_agent_parameters
if @@ERROR <> 0
goto error
end

-- we will now set the snapshot_ready status of all local publications. We invalidate the snapshot
-- so that the metadata upgrade can be run at snapshot time.
update dbo.sysmergepublications set snapshot_ready=2
where UPPER(publisher) collate database_default = UPPER(publishingservername()) collate database_default and publisher_db = db_name()

-- revoke select access to public on table which were previously granted to public
if object_id('dbo.sysmergepublications') is not NULL
revoke select on dbo.sysmergepublications from public

if object_id('dbo.MSmerge_errorlineage') is not NULL
revoke select on dbo.MSmerge_errorlineage from public

if object_id('dbo.sysmergearticles') is not NULL
begin
revoke select on dbo.sysmergearticles from public
grant select(nickname, maxversion_at_cleanup, objid) on dbo.sysmergearticles to public
end

if object_id('dbo.sysmergesubscriptions') is not NULL
revoke select on dbo.sysmergesubscriptions from public

if object_id('dbo.MSmerge_replinfo') is not NULL
revoke select on dbo.MSmerge_replinfo from public

if object_id('dbo.MSmerge_tombstone') is not NULL
revoke select on dbo.MSmerge_tombstone from public

if object_id('dbo.MSmerge_contents') is not NULL
revoke select on dbo.MSmerge_contents from public

if object_id('dbo.MSmerge_genhistory') is not NULL
revoke select on dbo.MSmerge_genhistory from public

if object_id('dbo.sysmergeschemachange') is not NULL
revoke select on dbo.sysmergeschemachange from public

if object_id('dbo.sysmergesubsetfilters') is not NULL
revoke select on dbo.sysmergesubsetfilters from public

end -- end dbo.sysmergearticles modifications

if object_id('sysmergearticles') is not NULL
begin
-- always drop down level triggers since in yukon the triggers are named differently
if exists (select * from dbo.sysmergearticles)
begin
declare @artidstr sysname
declare @instrigger nvarchar(517)
declare @updtrigger nvarchar(517)
declare @deltrigger nvarchar(517)

select @artnick = min(nickname) from dbo.sysmergearticles
while @artnick is not null
begin
select @objid = NULL
select @source_object = NULL
select top 1 @objid = objid, @artid = artid from dbo.sysmergearticles where nickname = @artnick
select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
if @objid is NULL or @source_object is NULL
goto error

exec @retcode=sys.sp_MSguidtostr @artid, @artidstr out
if @retcode<>0 or @@ERROR<>0
goto error

-- the following are downlevel trigger names
select @instrigger = QUOTENAME(@source_owner) + '.ins_' + @artidstr
select @updtrigger = QUOTENAME(@source_owner) + '.upd_' + @artidstr
select @deltrigger = QUOTENAME(@source_owner) + '.del_' + @artidstr
if object_id(@instrigger) is not NULL
begin
exec ('drop trigger ' + @instrigger)
if @@ERROR<>0 return (1)
end
if object_id(@updtrigger) is not NULL
begin
exec ('drop trigger ' + @updtrigger)
if @@ERROR<>0 return (1)
end
if object_id(@deltrigger) IS NOT NULL
begin
exec ('drop trigger ' + @deltrigger)
if @@ERROR<>0 return (1)
end

-- find next article
select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick
end -- end article while
end
end




-- Merge dynamic snapshot --

-- Make sure that the database is enabled for merge replication before MSdynamicsnapshotviews is created --
if @remove_repl = 0 and object_id('sysmergepublications') is not NULL
begin

--
-- MSdynamicsnapshotviews -- Created from Shiloh Beta2 onwards
--

if object_id('MSdynamicsnapshotviews') is NULL
begin
create table dbo.MSdynamicsnapshotviews (
dynamic_snapshot_view_name sysname primary key,
)
if @@ERROR <> 0 goto error
end

exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews

--
-- MSdynamicsnapshotjobs -- Created from Shiloh Beta2 onwards
--
if object_id('MSdynamicsnapshotjobs') is NULL
begin
create table dbo.MSdynamicsnapshotjobs (
id int identity,
name sysname not null unique,
pubid uniqueidentifier not null,
job_id uniqueidentifier not null,
agent_id int not null default 0,
dynamic_filter_login sysname null,
dynamic_filter_hostname sysname null,
dynamic_snapshot_location nvarchar(255) not null,
partition_id int not NULL default -1,
computed_dynsnap_location bit not NULL default 0
)
if @@ERROR <> 0 goto error
end

-- Update MSdynamicsnapshotjobs so that it has:
-- agent_id default 1
-- partition_id default -1
-- We are know herer that dbo.MSdynamicsnapshotjobs is not null so no need to check.

-- Adding DEFAULT on column agent_id
if exists(select * from sys.columns where object_id = object_id('dbo.MSdynamicsnapshotjobs')
and name = 'agent_id')
begin
select @defaultname = null
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.MSdynamicsnapshotjobs') and
sysc.name = 'agent_id'
if @defaultname is not null -- check if default exists
begin
-- delete default if one exists
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- add the default we want
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs add default 0 for agent_id'
exec (@alter_cmd)
end
if exists(select * from sys.columns where object_id = object_id('dbo.MSdynamicsnapshotjobs')
and name = 'partition_id')
begin
-- Adding DEFAULT on column partition_id
select @defaultname = null
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.MSdynamicsnapshotjobs') and
sysc.name = 'partition_id'
if @defaultname is not null -- check if default exists
begin
-- delete default if one exists
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- add the default we want
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs add default -1 for partition_id'
exec (@alter_cmd)
end

declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'partition_id', col_type = 'int not NULL default -1'
union all
select col_name = 'agent_id', col_type = 'int not NULL default 0'
union all
select col_name = 'computed_dynsnap_location', 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.MSdynamicsnapshotjobs', '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.MSdynamicsnapshotjobs 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

if not exists (select *
from sys.indexes
where object_id = object_id('MSdynamicsnapshotjobs')
and name = ('uciMSdynamicsnapshotjobs'))
begin
create unique clustered index uciMSdynamicsnapshotjobs on
dbo.MSdynamicsnapshotjobs(job_id, pubid)
if @@ERROR <> 0 goto error
end

if not exists (select *
from sys.indexes
where object_id = object_id('MSdynamicsnapshotjobs')
and name = ('nciMSdynamicsnapshotjobs'))
begin
create nonclustered index nciMSdynamicsnapshotjobs on
dbo.MSdynamicsnapshotjobs(partition_id)
if @@ERROR <> 0 goto error
end

exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs
if @@ERROR <> 0 goto error
end

-- Index updates (SQL7.0 SP1)
if @remove_repl = 0
begin
SELECT @table_name = N'sysmergepublications'
IF object_id('sysmergepublications') is not NULL
BEGIN
IF EXISTS ( SELECT pubid
FROM dbo.sysmergepublications
GROUP BY pubid
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 4, @table_name)
goto error
end
ELSE
IF NOT EXISTS ( SELECT * FROM sys.indexes WHERE name = 'nc1sysmergepublications' AND
object_id = object_id('dbo.sysmergepublications') )
CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
ON dbo.sysmergepublications(pubid)
if @@ERROR <> 0 goto error

IF NOT EXISTS ( SELECT * FROM sys.indexes WHERE name = 'nc2sysmergepublications' AND
object_id = object_id('dbo.sysmergepublications') )
CREATE NONCLUSTERED INDEX nc2sysmergepublications
ON sysmergepublications(status)
if @@ERROR <> 0 goto error


-- add default constraint on allow_anonymous 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.sysmergepublications')
and col.name = 'allow_anonymous')and exists
(select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'allow_anonymous')
begin
exec(N'alter table dbo.sysmergepublications add default 1 for allow_anonymous')
end


-- Changing default constraint on publisher from @@servername to publishingservername()
if exists(select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'publisher')
begin
declare @default_publisher_c_name nvarchar(258)
select @default_publisher_c_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.sysmergepublications')
and col.name = 'publisher'
join sys.objects as obj
on obj.object_id=con.constid

if(@default_publisher_c_name is not null)
begin
select @default_publisher_c_name = quotename(@default_publisher_c_name)
exec (N'alter table dbo.sysmergepublications drop constraint ' + @default_publisher_c_name)
exec(N'alter table dbo.sysmergepublications add default publishingservername() for publisher')
end
end

END


-- MSmerge_conflicts_info
SELECT @table_name = N'MSmerge_conflicts_info'
IF object_id('MSmerge_conflicts_info') is not NULL
BEGIN
IF EXISTS ( SELECT tablenick, rowguid, origin_datasource, conflict_type
FROM MSmerge_conflicts_info
GROUP BY tablenick, rowguid, origin_datasource, conflict_type
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 6, @table_name)
goto error
end
ELSE
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1MSmerge_conflicts_info' AND
id = OBJECT_ID('MSmerge_conflicts_info') )
CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_conflicts_info
ON MSmerge_conflicts_info(tablenick, rowguid, origin_datasource, conflict_type)
if @@ERROR <> 0 goto error
END

-- sysmergeschemachange
SELECT @table_name = N'sysmergeschemachange'
IF object_id('sysmergeschemachange') is not NULL
BEGIN
IF EXISTS ( SELECT schemaversion, pubid
FROM dbo.sysmergeschemachange
GROUP BY schemaversion, pubid
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 7, @table_name)
goto error
end
ELSE
BEGIN
IF EXISTS ( SELECT * FROM sys.indexes WHERE name = 'schemachangeversion' AND
object_id = OBJECT_ID('sysmergeschemachange') )
DROP INDEX sysmergeschemachange.schemachangeversion
if @@ERROR <> 0 goto error

-- Recreate this index as unique clustered with one more field in index key.
CREATE UNIQUE CLUSTERED INDEX schemachangeversion ON sysmergeschemachange(schemaversion, pubid)
if @@ERROR <> 0 goto error
END

-- In Yukon we no longer have a schema version SCHEMA_TYPE_SYSTABLE which
-- indicates a system table schema script file. This type was deprecated because
-- it was used only by JET consumers. In Yukon Jet subscribers are not supported
-- Hence deleting any entries which have schema type SCHEMA_TYPE_SYSTABLE (20)
delete from dbo.sysmergeschemachange where schematype=20

-- Adding schemastatus column.
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergeschemachange') and
name = 'schemastatus')
begin
alter table dbo.sysmergeschemachange add schemastatus tinyint NOT NULL default(1)
if @@error <> 0 goto error
end

if not exists (select * from sys.columns where object_id = object_id('sysmergeschemachange') and
name = 'schemasubtype')
begin
alter table sysmergeschemachange add schemasubtype int NOT NULL default 0
if @@error <> 0 goto error
end

-- Modifying the type of the schematext from nvarchar(2000) to nvarchar(max)
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergeschemachange') and
name = 'schematext')
begin
exec (' alter table dbo.sysmergeschemachange alter column schematext nvarchar(max) NOT NULL ')
if @@error <> 0 goto error
end

END


-- sysmergesubsetfilters
SELECT @table_name = N'sysmergesubsetfilters'
IF object_id('sysmergesubsetfilters') is not NULL
BEGIN
IF EXISTS ( SELECT join_filterid
FROM dbo.sysmergesubsetfilters
GROUP BY join_filterid
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 8, @table_name)
goto error
end
ELSE
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1sysmergesubsetfilters' AND
id = OBJECT_ID('sysmergesubsetfilters') )
begin
CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters ON dbo.sysmergesubsetfilters(join_filterid, pubid)
if @@ERROR <> 0 goto error
end

IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'uc2sysmergesubsetfilters' AND
id = OBJECT_ID('sysmergesubsetfilters') )
begin
CREATE UNIQUE CLUSTERED INDEX uc2sysmergesubsetfilters ON dbo.sysmergesubsetfilters(pubid, filtername)
if @@ERROR <> 0 goto error
end

if not exists (select * from sys.columns where object_id = object_id('sysmergesubsetfilters') and
name = 'filter_type')
begin
alter table dbo.sysmergesubsetfilters add filter_type tinyint NOT NULL default 1
if @@ERROR <> 0 goto error
end
END -- end index updates from SQL7.0 SP1


declare @binames table (biname sysname)
insert into @binames select name from sys.objects where type='U' and is_ms_shipped=1 and name like 'MS_bi%'
declare @biname sysname
set @biname= (select top 1 biname from @binames)
while @biname is not null
begin
set @cmd= 'drop index ' + quotename(@biname) + '.' + quotename(@biname + '_gen')
exec sys.sp_executesql @cmd
if @@ERROR <> 0 goto error
set @cmd= 'create clustered index ' + quotename(@biname + '_gen') + ' on ' + quotename(@biname) + '(generation)'
exec sys.sp_executesql @cmd
if @@ERROR <> 0 goto error
delete from @binames where biname=@biname
set @biname= (select top 1 biname from @binames)
end


if object_id('sysmergearticles') is not NULL
begin
exec sys.sp_MScreate_common_dist_tables @subside=1
if @@error <> 0
goto error
end

-- in Yukon we will create the merge ddl triggers, only for databases that have merge replication enabled.
if object_id('sysmergepublications', 'U') is NOT NULL
begin
if exists (select * from sys.triggers where name = 'MSmerge_tr_altertable' and type = 'TR')
begin
execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'
if @@ERROR <> 0 or @retcode <> 0
goto error
end
if not exists (select * from sys.triggers where name = 'MSmerge_tr_altertable' and type = 'TR')
begin
execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='add'
if @@ERROR <> 0 or @retcode <> 0
goto error
end
end
-- we will also add a system table MSmerge_upgrade_in_progress which would indicate that
-- the metadata upgrade has not been completed yet.
if @remove_repl=0 and object_id('sysmergearticles') is not NULL
begin
if object_id('MSmerge_upgrade_in_progress', 'U') is NULL
begin
create table dbo.MSmerge_upgrade_in_progress
(
status tinyint not NULL
)
if @@ERROR <> 0 goto error
end
end
end
commit tran
return 0

error:
rollback tran vupgrade_mergetables
commit tran
return 1

end

No comments:

Post a Comment

Total Pageviews