create procedure sys.sp_vupgrade_publisherdb @ver_old int, @ver_retention int
as
begin
set nocount on
declare @default_name sysname,
@retcode int,
@column_name nvarchar(128),
@column_type nvarchar(128),
@alter_cmd nvarchar(max)
set @retcode = 0
-- raiserror('sp_vupgrade_publisherdb', 0,1) with nowait
-- Safety check - with Yukon, there are now objects in the dist db that can make
-- a distribution db look like a publisher db. In case the previous checks for
-- distribution db mode fail, this is a last ditch check to make sure we don't
-- add stuff to the distribution db
IF sys.fn_MSrepl_isdistdb(DB_NAME()) = 1 OR
object_id(N'dbo.IHpublishers', 'U') IS NOT NULL OR
object_id(N'dbo.syspublications', 'V') IS NOT NULL OR
object_id(N'dbo.MSdistribution_history', 'U') IS NOT NULL
BEGIN
-- The above evidence makes it extremely likely this is a distribution db
-- Don't do anything further
RETURN (0)
END
if object_id('systranschemas') is not null
begin
if not exists (select * from sys.columns where object_id = object_id('systranschemas') and
name = 'typeid')
begin
alter table systranschemas add typeid int default 52
end
end
-- syssubscriptions
if object_id('dbo.syssubscriptions', 'U') is not null
begin
if not exists (select * from sys.columns where object_id = object_id('syssubscriptions') and
name = 'queued_reinit')
begin
alter table syssubscriptions add queued_reinit bit default 0 not null
end
if not exists (select * from sys.columns where object_id = object_id('dbo.syssubscriptions', 'U') and
name = 'nosync_type')
begin
alter table dbo.syssubscriptions add nosync_type tinyint default 0 not null
end
if not exists (select * from sys.columns where object_id = object_id('dbo.syssubscriptions', 'U') and
name = 'srvname')
begin
alter table dbo.syssubscriptions add srvname sysname not null default N'' with values
if exists (select * from master.dbo.sysdatabases where dbid = db_id() and cmptlevel > 70)
exec('update dbo.syssubscriptions set srvname = upper(ss.srvname collate database_default) from dbo.syssubscriptions sub
join master.dbo.sysservers ss on ss.srvid = sub.srvid')
else
exec('update dbo.syssubscriptions set srvname = upper(ss.srvname) from dbo.syssubscriptions sub
join master.dbo.sysservers ss on ss.srvid = sub.srvid')
if exists(select * from sys.indexes where name = N'unc1syssubscriptions' and object_id = object_id(N'syssubscriptions'))
drop index syssubscriptions.unc1syssubscriptions
create unique nonclustered index unc1syssubscriptions on syssubscriptions (artid, srvid, dest_db, srvname)
end
end -- end of syssubscriptions upgrade
-- sysarticles
if object_id('dbo.sysarticles', 'U') is not null
begin
declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'ins_scripting_proc', col_type = 'int NULL'
union all
select col_name = 'del_scripting_proc', col_type = 'int NULL'
union all
select col_name = 'upd_scripting_proc', col_type = 'int NULL'
union all
select col_name = 'custom_script', col_type = 'nvarchar(2048) NULL'
union all
select col_name = 'fire_triggers_on_snapshot', col_type = 'bit NOT NULL default 0') as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysarticles', 'U'))
where t2.name is null) --This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.sysarticles add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 return 1
end
close colcurs
deallocate colcurs
-- sysarticlecolumns
--create sysarticlecolumns before reading columns info, do it within this block so it's NOT always
--created regardless of transactional replication
if object_id('sysarticlecolumns') is NULL
begin
create table dbo.sysarticlecolumns
(
artid int NOT NULL,
colid int NOT NULL,
is_udt bit default 0,
is_xml bit default 0,
is_max bit default 0
)
create unique clustered index idx_sysarticlecolumns on sysarticlecolumns (artid, colid)
exec dbo.sp_MS_marksystemobject 'sysarticlecolumns'
end
if exists (select * from sys.columns where object_id = object_id('dbo.sysarticles', 'U') and
name = 'columns')
begin
exec @retcode = sys.sp_vupgrade_sysarticlecolumns
if @@error <> 0 or @retcode <> 0 return 1
alter table dbo.sysarticles drop column columns
if @@error <> 0 return 1
end
if exists (select * from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id
where object_id = object_id('dbo.sysarticlecolumns', 'U') and sc.name = N'colid' and st.name = N'smallint')
begin
if exists (select * from sysindexes where id = object_id('dbo.sysarticlecolumns') and name ='idx_sysarticlecolumns')
begin
drop index sysarticlecolumns.idx_sysarticlecolumns
if @@error <> 0 return 1
end
alter table dbo.sysarticlecolumns alter column colid int NOT null
if @@error <> 0 return 1
create unique clustered index idx_sysarticlecolumns on sysarticlecolumns (artid, colid)
if @@error <> 0 return 1
end
if exists (select * from sysindexes where id = object_id('dbo.sysarticles') and name = 'unc1sysarticles' )
begin
if not exists (select * from sysindexes where id = object_id('dbo.sysarticles') and name = 'c1sysarticles' )
begin
drop index sysarticles.unc1sysarticles
create unique clustered index c1sysarticles on sysarticles (artid, pubid)
end
end
end
-- dbo.MSpeer_lsns
if object_id('dbo.MSpeer_lsns') is not null
begin
-- add columns originator_version and originator_id
if not exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_version')
begin
select @alter_cmd = 'alter table dbo.MSpeer_lsns add originator_version int NULL'
exec (@alter_cmd)
end
if not exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_id')
begin
select @alter_cmd = 'alter table dbo.MSpeer_lsns add originator_id int NULL'
exec (@alter_cmd)
end
-- Changing index PK from clustered to non-clustered
-- drop clustered PK so we can drop index
declare @PKConstraintName sysname
select top 1 @PKConstraintName = sysdc.name from sys.key_constraints sysdc
where sysdc.parent_object_id = object_id('dbo.MSpeer_lsns')
and sysdc.type = 'PK'
if @PKConstraintName is not null
begin
select @alter_cmd = 'alter table dbo.MSpeer_lsns drop constraint ' + QUOTENAME(@PKConstraintName)
exec (@alter_cmd) -- drop primary key (also drops clustered key index)
end
select @alter_cmd = 'alter table dbo.MSpeer_lsns add constraint ' + QUOTENAME(@PKConstraintName) + ' PRIMARY KEY NONCLUSTERED (id)'
exec (@alter_cmd) -- Make PK w/ index unclustered
-- Changing index uci_MSpeer_lsns from (originator, originator_db, originator_publication, originator_publication_id, originator_db_version, originator_lsn)
-- to (originator, originator_db, originator_publication_id, originator_db_version, originator_lsn)
if exists (select * from sys.indexes where name = 'uci_MSpeer_lsns' and object_id = object_id('dbo.MSpeer_lsns'))
begin
-- drop old index
drop index dbo.MSpeer_lsns.uci_MSpeer_lsns
end
if exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_db')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_publication_id')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_db_version')
and exists(select * from sys.columns where object_id = object_id('dbo.MSpeer_lsns') and name = 'originator_lsn')
begin
-- create new one
create unique clustered index uci_MSpeer_lsns on dbo.MSpeer_lsns(originator, originator_db, originator_publication_id, originator_db_version, originator_lsn)
end
end
-- syspublications
if object_id('dbo.syspublications', 'U') is not null
begin
-- default_access column no longer used
if exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
name = 'default_access')
begin
alter table dbo.syspublications drop column default_access
end
-- insure index nc3syspublications is there
if exists (select * from sys.indexes where name = 'nc3syspublications' and
object_id = object_id('dbo.syspublications') )
begin
drop index dbo.syspublications.nc3syspublications
end
if exists(select * from sys.columns where object_id = object_id('dbo.syspublications') and name = 'status')
begin
create index nc3syspublications on syspublications(status)
end
/*
* Since the ftp_address is now required to enable a publication for internet, publications
* that were enabled for internet can not be upgraded automatically. So the enabled for
* internet option is reset in the upgrade process. New FTP columns added later in script. (Sphinx -> Shiloh)
*/
if exists (select * from sys.columns where object_id = object_id('syspublications') and
name = 'enabled_for_internet')
begin
if not exists (select * from sys.columns where object_id = object_id('syspublications') and name = 'ftp_address')
begin
update dbo.syspublications
set enabled_for_internet = 0
end
else
begin
exec('update dbo.syspublications set enabled_for_internet = 0 where ftp_address is null')
end
end
declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'allow_queued_tran', col_type = 'bit default 0 not null' --Queued updating subscriptions option; conflict management settings added later in this proc
union all
select col_name = 'snapshot_in_defaultfolder', col_type = 'bit default 1 not null' --Portable snapshot
union all
select col_name = 'alt_snapshot_folder', col_type = 'nvarchar(255) null'
union all
select col_name = 'pre_snapshot_script', col_type = 'nvarchar(255) null' -- Pre-snapshot commands
union all
select col_name = 'post_snapshot_script', col_type = 'nvarchar(255) null' -- Post-snapshot commands
union all
select col_name = 'compress_snapshot', col_type = 'bit default 0 not null' -- Snapshot compression
union all
select col_name = 'ftp_address', col_type = 'sysname null' -- Post SQL7.0 FTP configuration stored at publisher
union all
select col_name = 'ftp_port', col_type = 'int not null default 21'
union all
select col_name = 'ftp_subdirectory', col_type = 'nvarchar(255) null'
union all
select col_name = 'ftp_login', col_type = 'sysname null default N''anonymous'''
union all
select col_name = 'ftp_password', col_type = 'nvarchar(524) null'
union all
select col_name = 'allow_dts', col_type = 'bit default 0 not null' --Transformable subscriptions
union all
select col_name = 'allow_subscription_copy', col_type = 'bit default 0 not null' -- Attach & Go
union all
select col_name = 'queue_type', col_type = 'int NULL'
union all
select col_name = 'centralized_conflicts', col_type = 'bit NULL' -- Transactional conflict management
union all
select col_name = 'conflict_retention', col_type = 'int NULL'
union all
select col_name = 'conflict_policy', col_type = 'int NULL'
union all
select col_name = 'ad_guidname', col_type = 'sysname null' -- Active Directory
union all
select col_name = 'min_autonosync_lsn', col_type = 'binary(10) NULL'
union all
select col_name = 'replicate_ddl', col_type = 'int default 1' -- DDL
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.syspublications', 'U'))
where t2.name is null) --This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.syspublications add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 return 1
end
close colcurs
deallocate colcurs
-- The following code for upgrading schema options is applicable
-- to 8.0->9.0 upgrade only, and it is needed because of subtle
-- differences between how index-related schema options are interpreted
-- between these two releases. 7.0's interpretation of index-related
-- schema options is actually quite close to the 9.0 interpretation so
-- there is no need to modify existing 7.0 schema options. The
-- means for detecting an 8.0->9.0 upgrade is by checking for the
-- presence of the backward_comp_level column and the absence of
-- the allow_initialize_from_backup colum in syspublications
-- The differences between how index-related schema options are
-- interpreted in 8.0 and 9.0 are as follow:
-- When the 0x10 (clustered index) and 0x40 (non-clustered indexes)
-- options are specified in 8.0, only "pure" clustered and non
-- clustered indexes not created for backing primary key or unique
-- constraints are replicated, indexes for primary key and unique
-- constraints are included only if the 0x80 (primary key) and 0x4000
-- (unique constraint) options are specified respectively. There is
-- also this special consideration of always implicitly including the
-- 0x80 (primary key) option for transactional (not snapshot)
-- publications even if the option is unspecified. Once either 0x80
-- and/or 0x4000 are included, the 0x8000 (replicate primary key and
-- unique constraints as contraints rather than just indexes) can be
-- used to decide whether the included primary key or unique
-- contraints are replicated as pure indexes or constraints.
-- For 9.0 publications, specifying the 0x10 and 0x40 options will
-- cause all the clustered and non-clustered indexes including those
-- for backing primary key or unique constraints to be replicated
-- as "create index" statements at least. If the 0x80 and/or 0x4000
-- options are specified, the primary key and/or unique constraints
-- will be replicated as contraints respectively regardless of whether
-- the underlying index type (clustered or non-clustered) are covered
-- by the 0x10 and 0x40 options. In a way, the 0x80 and 0x4000 options
-- can also be seen as modifiers of key constraint indexes covered by
-- 0x10 and 0x40 that "upgrade" those "pure indexes" to "full-fledged
-- constraints" thereby providing a more flexible mechanism than
-- the 0x8000 option in 8.0. Thus, the 0x8000 option will be
-- deprecated in 9.0. In 9.0, it is also possible to completely
-- disable the primary key for read-only (i.e. non-synctran, non
-- queued) publications by not specifying the 0x40 option as well as
-- the option (0x10 or 0x40) specifying the underlying index type
-- (clustered or non-clustered) of the primary key.
-- Based on the above description of how interpretation of
-- index-related schema options differs from 8.0 to 9.0, here
-- is the plan for upgrading the schema options from 8.0 to 9.0 which
-- tries preserve the old behavior as much as possible. There are
-- two main cases to consider:
-- i) The 0x8000 option was not specified - This leads to the
-- following two sub-cases:
-- a) At least one of 0x80 or 0x4000 was specified - Under the 9.0
-- interpretation, these will cause the primary key and\or the
-- unique constraints to be replicated as constraints rather than
-- indexes which is inapproprate because the 0x8000 option is
-- explicitly disabled. The best way to upgrade this class of
-- schema options is to force enable both 0x10 and 0x40 to
-- ensure that the underlying index(es) for the primary key and
-- unique constraints are replicated. This is actually what
-- happened in the 8.0 snapshot agent so there is essentially
-- no change in behavior.
-- b) Neither 0x80 nor 0x4000 was specified - The only
-- issue of concern with this class of schema options is that
-- the 0x10 and 0x40 options now covers a broader range of
-- indexes. As such, it not necessary to change these schema
-- options during upgrade.
-- ii) The 0x8000 option was specified - In this case, at least
-- one of 0x80 or 0x4000 is probably specified. Under the 9.0
-- interpretation, mere inclusion of 0x80 and 0x4000 will cause
-- the corresponding primary key and unique constraints to be
-- replicated as constraints anyway so the only thing that needs
-- to be done is to disable the 0x8000 option during upgrade.
-- Note that broader interpretation of 0x10 and 0x40 in 9.0 will
-- also cause a broader range of indexes to be included with
-- this class of schema options.
-- Use transaction encompassing the addition of the
-- backward_comp_level and the allow_initialize_from_backup columns to
-- syspublications to ensure that schema_option upgrade is done at
-- most once even in failure scenarios.
begin transaction
save transaction schema_options_upgrade
if object_id('dbo.sysarticles', 'U') is not null and
exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and name = 'backward_comp_level') and
not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and name = 'allow_initialize_from_backup')
begin
update dbo.sysarticles
set schema_option = case
when (sys.fn_replgetbinary8lodword(schema_option) & 0x8000) = 0 and (sys.fn_replgetbinary8lodword(schema_option) & 0x4080) <> 0 then sys.fn_replcombinehilodwordintobinary8(sys.fn_replgetbinary8hidword(schema_option),sys.fn_replgetbinar
y8lodword(schema_option) | 0x50 & ~convert(int, 0x4080)) -- i)a)
when (sys.fn_replgetbinary8lodword(schema_option) & 0x8000) <> 0 then sys.fn_replcombinehilodwordintobinary8(sys.fn_replgetbinary8hidword(schema_option),sys.fn_replgetbinary8lodword(schema_option) & ~convert(int, 0x8000)) -- ii)
else schema_option
end
if @@error <> 0
begin
if @@trancount > 0
begin
rollback transaction schema_options_upgrade
commit transaction
end
return 1
end
end
if not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
name = 'backward_comp_level')
begin
alter table dbo.syspublications add backward_comp_level int default 10 not null
if @@error <> 0
begin
if @@trancount > 0
begin
rollback transaction schema_options_upgrade
commit transaction
end
return 1
end
end
/*
* Transactional replication automated nosync subscription setup
* support
*/
if not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
name = 'allow_initialize_from_backup')
begin
alter table dbo.syspublications add allow_initialize_from_backup bit default 0 not null
if @@error <> 0
begin
if @@trancount > 0
begin
rollback transaction schema_options_upgrade
commit transaction
end
return 1
end
end
/*
* Revoke public select permission from synchronization views
*/
declare @synchronization_object_name nvarchar(517)
declare #syncobject_name cursor local fast_forward for
select quotename(schema_name(so.schema_id)) + N'.' + quotename(so.name)
from sysarticles sa
inner join sys.objects so
on sa.sync_objid = so.object_id
where (sa.type & 0x05) = 0x01 -- Must be logbased article with an automatically generated view
and rtrim(so.type) = 'V'
open #syncobject_name
fetch #syncobject_name into @synchronization_object_name
while (@@fetch_status <> -1)
begin
exec (N'revoke select on ' + @synchronization_object_name + N' from public')
fetch #syncobject_name into @synchronization_object_name
end
close #syncobject_name
deallocate #syncobject_name
commit transaction
/*
* Publication options
*/
if not exists (select * from sys.columns where object_id = object_id('dbo.syspublications') and
name = 'options')
begin
exec ('alter table dbo.syspublications add options int default 0 not null')
/*
* Set enabled for het sub bit in options column if sync_method = 1
*/
exec ('update dbo.syspublications set options = 0x4 where sync_method = 1')
end
/*
* Schema only articles (views, procs, udfs)
*/
if object_id(N'dbo.sysschemaarticles') is null
begin
-- table does not exist
create table dbo.sysschemaarticles
(
artid int NOT NULL,
creation_script nvarchar(255) NULL,
description nvarchar(255) NULL,
dest_object sysname NOT NULL,
name sysname NOT NULL,
objid int NOT NULL,
pubid int NOT NULL,
pre_creation_cmd tinyint NOT NULL,
status int NOT NULL,
type tinyint NOT NULL,
schema_option binary(8) NULL,
dest_owner sysname NULL
)
exec dbo.sp_MS_marksystemobject N'sysschemaarticles'
if not exists (select * from sys.indexes where object_id = object_id(N'dbo.sysschemaarticles') and name = N'c1sysschemaarticles')
begin
create unique clustered index c1sysschemaarticles
on dbo.sysschemaarticles(artid, pubid)
end
end
else
begin
-- table exists - upgrade indices
if exists (select * from sys.indexes where object_id = object_id(N'dbo.sysschemaarticles') and name = N'unc1sysschemaarticles' )
begin
drop index dbo.sysschemaarticles.unc1sysschemaarticles
end
if not exists (select * from sys.indexes where object_id = object_id(N'dbo.sysschemaarticles') and name = N'c1sysschemaarticles' )
begin
create unique clustered index c1sysschemaarticles on dbo.sysschemaarticles (artid, pubid)
end
end
if exists (select * from sys.objects where name = 'sysextendedarticlesview')
begin
drop view dbo.sysextendedarticlesview
end
-- cannot create view directly in stored procedure
exec ( 'create view dbo.sysextendedarticlesview
as
select artid, creation_script, del_cmd, description,
dest_table, filter, filter_clause, ins_cmd, name, objid, pubid,
pre_creation_cmd, status, sync_objid, type, upd_cmd,
schema_option, dest_owner, ins_scripting_proc, del_scripting_proc,
upd_scripting_proc, custom_script, fire_triggers_on_snapshot
from sysarticles
union all
select artid, creation_script, NULL, description,
dest_object, NULL, NULL, NULL, name, objid, pubid,
pre_creation_cmd, status, NULL, type, NULL,
schema_option, dest_owner, NULL, NULL, NULL, NULL, 0
from sysschemaarticles')
exec dbo.sp_MS_marksystemobject 'sysextendedarticlesview'
exec sys.sp_vupgrade_syscol_status
end
-- sysarticleupdates
if object_id('sysarticleupdates') is not null
begin
declare #colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'sync_upd_trig', col_type = 'int default 0 NOT NULL'
union all
select col_name = 'conflict_tableid', col_type = 'int NULL'
union all
select col_name = 'ins_conflict_proc', col_type = 'int NULL'
union all
select col_name = 'identity_support', col_type = 'bit default 0 NOT NULL'
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysarticleupdates', 'U'))
where t2.name is null) --This query gives all the columns in t1 that are not in syscolums
for read only
open #colcurs
fetch #colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.sysarticleupdates add ' + @column_name + ' ' + @column_type
fetch #colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch #colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 return 1
end
close #colcurs
deallocate #colcurs
-- remove the default for column sync_upd_trig
select @column_name = NULL
select @column_name = dc.name
from sys.default_constraints as dc
join sys.columns as c
on dc.parent_object_id = c.object_id
and dc.parent_column_id = c.column_id
where c.object_id = object_id('dbo.sysarticleupdates')
and c.name = N'sync_upd_trig'
if (@column_name is not null)
begin
select @alter_cmd = N'alter table dbo.sysarticleupdates drop constraint ' + sys.fn_replreplacesinglequote(quotename(@column_name))
exec(@alter_cmd)
if @@error <> 0
return 1
end
end
-- Upgrade dbt->distbackuplsn
-- Make sure the upgrade is done for 8.0 Beta 2 customers.
-- no need to do this if called during restoration of sql70 backup
-- so to avoid sp_repldone error out with 18757,
-- master..sysdatbases.category is bound to be 0 when 70 db is restored into 80
-- since 70 dbtable did not have this field
if exists (select * from sys.objects where name = 'sysarticles') and
not exists (select * from sys.objects where name = 'systranschemas') and
exists( select * from master..sysdatabases
where dbid = db_id() and category & 1 = 1)
--if cdc is still enabled, don't call sp_repldone
AND not exists(select * from sys.databases where db_id() = database_id and is_cdc_enabled = 1)
begin
-- Force to get in even if the logreader is running.
exec sys.sp_replflush
exec sys.sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,
@time = 0, @reset = 0, @code = 1
-- Unmark the connection as the logreader.
exec sys.sp_replflush
end
-- Create new tran tables if the db is enabled for tran publishing.
if exists (select name from sys.objects where name='syspublications')
begin
exec sys.sp_MScreate_pub_tables
end
/*
* syspublications
*/
if object_id('syspublications', 'U') is not null
begin
if not exists (select * from sys.columns where object_id = Object_Id('syspublications') and name = 'ftp_password' and max_length = '1048')
begin
/*
* syspublications ftp_password
* no need to upgrade passwords since this column is new in 8.0.
*/
declare @cmptlevel tinyint
select @cmptlevel = cmptlevel from master.dbo.sysdatabases where dbid = db_id()
if @cmptlevel < 70
begin
raiserror (15048, -1, -1, 70, 70, 70, 80)
end
else
begin
exec( 'alter table syspublications alter column ftp_password nvarchar(524)' )
end
end
--create originator_id column if it does not exist
if not exists (select * from sys.columns where object_id = object_id('syspublications') and name = 'originator_id')
begin
alter table syspublications add originator_id int NULL
end
-- change non-clustered index on syspublications.pubid to clustered
-- index
if exists( select * from sys.indexes where name = 'unc1syspublications' and object_id = object_id('dbo.syspublications') )
begin
drop index dbo.syspublications.unc1syspublications
end
if not exists ( select * from sys.indexes where name = 'uc1syspublications' and object_id = object_id('dbo.syspublications') )
begin
create unique clustered index uc1syspublications
on syspublications (pubid)
end
-- Upgrade updating publications
exec @retcode = sys.sp_vupgrade_updatingpublicationarticle
if @@error != 0 or @retcode != 0
return 1
--if pk of base table is disabled or lo-based iv becomes regular view,
--reinit article per SQLBU#335193
if object_id('dbo.sysarticles', 'U') is not null
and object_id('dbo.syssubscriptions', 'U') is not null
and DatabasePropertyEx(DB_NAME(), 'IsPublished') = 1
begin
exec sp_checkinvalidivarticle @mode = 0, @publication = NULL
end
end
-- all done
end
