create procedure sys.sp_vupgrade_subscription_tables
as
begin
-- raiserror('sp_vupgrade_subscription_tables', 0,1) with nowait
set nocount on
DECLARE @table_name sysname
declare @retcode int
declare @column_name nvarchar(128)
declare @column_type nvarchar(128)
declare @alter_cmd nvarchar(max)
-- Safety check - with Yukon, there are now objects in the dist db that can make
-- a distribution db look like a publisher db. In case the previous checks for
-- distribution db mode fail, this is a last ditch check to make sure we don't
-- add stuff to the distribution db
IF sys.fn_MSrepl_isdistdb(DB_NAME()) = 1 OR
object_id(N'dbo.IHpublishers', 'U') IS NOT NULL OR
object_id(N'dbo.syspublications', 'V') IS NOT NULL OR
object_id(N'dbo.MSdistribution_history', 'U') IS NOT NULL
BEGIN
-- The above evidence makes it extremely likely this is a distribution db
-- Don't do anything further
RETURN (0)
END
begin tran
save tran sp_vupgrade
-- dbo.MSpeer_lsns
if object_id('dbo.MSpeer_lsns') is not null and not EXISTS (select * from sys.objects where name = 'syspublications') --read-only subscriptions
begin
-- add columns originator_version and originator_id
if not exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_version')
begin
select @alter_cmd = 'alter table dbo.MSpeer_lsns add originator_version int NULL'
exec (@alter_cmd)
end
if not exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_id')
begin
select @alter_cmd = 'alter table dbo.MSpeer_lsns add originator_id int NULL'
exec (@alter_cmd)
end
-- Changing index PK from clustered to non-clustered
-- drop clustered PK so we can drop index
declare @PKConstraintName sysname
select top 1 @PKConstraintName = sysdc.name from sys.key_constraints sysdc
where sysdc.parent_object_id = object_id('dbo.MSpeer_lsns')
and sysdc.type = 'PK'
if @PKConstraintName is not null
begin
select @alter_cmd = 'alter table dbo.MSpeer_lsns drop constraint ' + QUOTENAME(@PKConstraintName)
exec (@alter_cmd) -- drop primary key (also drops clustered key index)
end
select @alter_cmd = 'alter table dbo.MSpeer_lsns add constraint ' + QUOTENAME(@PKConstraintName) + ' PRIMARY KEY NONCLUSTERED (id)'
exec (@alter_cmd) -- Make PK w/ index unclustered
-- Changing index uci_MSpeer_lsns from (originator, originator_db, originator_publication, originator_publication_id, originator_db_version, originator_lsn)
-- to (originator, originator_db, originator_publication_id, originator_db_version, originator_lsn)
if exists (select * from sys.indexes where name = 'uci_MSpeer_lsns' and object_id = object_id('dbo.MSpeer_lsns'))
begin
-- drop old index
drop index dbo.MSpeer_lsns.uci_MSpeer_lsns
end
if exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_db')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_publication_id')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_db_version')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_lsn')
begin
-- create new one
create clustered index uci_MSpeer_lsns on dbo.MSpeer_lsns(originator, originator_db, originator_publication_id, originator_db_version, originator_lsn)
end
end
-- drop MSrepl_identity_range
if object_id(N'dbo.MSrepl_identity_range') is not NULL
begin
drop table dbo.MSrepl_identity_range
end
-- Create unique index on tables that did not have one previously. The index is not created
-- if duplicates rows exist in the table.
-- MSreplication_subscriptions (SQL7.0 SP1)
SELECT @table_name = N'MSreplication_subscriptions'
IF EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSreplication_subscriptions' ) or
EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSsubscription_agents' )
BEGIN
IF EXISTS ( SELECT publication, publisher_db, publisher, subscription_type
FROM MSreplication_subscriptions
GROUP BY publication, publisher_db, publisher, subscription_type
HAVING COUNT(*) > 1 )
RAISERROR (21203, 10, 1, @table_name)
ELSE
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'uc1MSReplication_subscriptions' AND
id = OBJECT_ID('MSreplication_subscriptions') )
CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON
MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type)
-- Create MSsubscription_agents table
exec @retcode = sys.sp_MScreate_sub_tables_internal
@tran_sub_table = 1,
@property_table = 0,
@sqlqueue_table = 0
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
-- Add new columns to MSsubscription_agents, for 8.0 pre beta upgrade.
declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'last_sync_status', col_type = 'int NULL' -- status of the last sync (Shiloh)
union all
select col_name = 'last_sync_summary', col_type = 'sysname NULL'
union all
select col_name = 'last_sync_time', col_type = 'datetime NULL'
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.MSsubscription_agents', 'U'))
where t2.name is null) --This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.MSsubscription_agents add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 goto UNDO
end
close colcurs
deallocate colcurs
END
-- MSreplication_objects (SQL7.0 SP1)
SELECT @table_name = N'MSreplication_objects'
IF EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSreplication_objects' )
BEGIN
if exists (select * from sys.indexes where name = N'ucMSreplication_objects'
and object_id = OBJECT_ID(N'dbo.MSreplication_objects')
and is_unique = 1)
begin --remove uniqueness constraint
drop index ucMSreplication_objects on dbo.MSreplication_objects
CREATE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
end
else
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'ucMSreplication_objects' AND
id = OBJECT_ID('MSreplication_objects') )
CREATE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
if not exists (select * from sys.columns where
object_id = object_id('MSreplication_objects') and
name = 'article')
begin
alter table dbo.MSreplication_objects add article sysname NULL
if @@error != 0
goto UNDO
end
END
-- MSreplication_queue (SQL2000 SP3)
IF object_id(N'dbo.MSreplication_queue') is not null
BEGIN
-- upgrade index
if exists (select * from sys.indexes where object_id = object_id(N'dbo.MSreplication_queue')
and name = N'nc1MSreplication_queue')
begin
drop index dbo.MSreplication_queue.nc1MSreplication_queue
CREATE NONCLUSTERED INDEX nc1MSreplication_queue ON
MSreplication_queue(publisher, publisher_db, publication, tranid)
IF @@ERROR <> 0
goto UNDO
end
exec @retcode = sp_MScreate_sub_tables_internal
@tran_sub_table = 0,
@property_table = 0,
@sqlqueue_table = 1
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
END
-- MSrepl_queuedtraninfo (SQL2000 SP3)
IF object_id(N'dbo.MSrepl_queuedtraninfo') is not null
BEGIN
exec @retcode = sp_MScreate_sub_tables_internal
@tran_sub_table = 0,
@property_table = 0,
@sqlqueue_table = 1
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
END
-- delete 'columns' column if it exists
if object_id(N'dbo.MSsubscription_articles') is not null
begin
if exists (select * from sys.columns where name = N'columns' and object_id=object_id(N'dbo.MSsubscription_articles'))
begin
alter table MSsubscription_articles drop column columns
end
end
if @@trancount > 0
commit tran
return 0
UNDO:
if @@trancount > 0
begin
rollback tran sp_vupgrade
commit tran
end
return 1
end
