
/*
** Upgrade1.CQL		1996/04/01 21:00
**
** (From old/6.0 Upgrade.SQL)
**
** Copyright Microsoft, Inc. 1994, 1995, 1996
** All Rights Reserved.
** Use, duplication, or disclosure by the United States Government
** is subject to restrictions as set forth in subdivision (c) (1) (ii)
** of the Rights in Technical Data and Computer Software clause
** at CFR 252.227-7013. Microsoft, Inc. One Microsoft Way, Redmond WA
** 98052.
*/

/*********************************
	Normally -m and -T3643 will be coded on sqlservr.exe
when this file is run.
	This file works closely with sysdatabases.version (spt_values 'DBV').
408 means fully upgraded to MS SQL 6.5.  This file will upgrade databases
where version<=408 (repeated runs harmless), but will refuse to upgrade
if version between 409 and 999.
	As this file runs it adds a constant of 1000 three times
to sysdatabases.version to track progress; before sysdatabases.version
is finally forced to 408 (by sp_db_upgrade3).

	sp_db_upgrade1 is created, and then run against master db only.
	Next, sp_db_upgrade2,3 are created then run against master db only.
	Next, sp_db_upgrade1,2,3 are all run against model; then all
are run against tempdb.
	At this point sp_db_upgrade3 should have updated the version of
these special system databases to 408.
	Next, sp_db_upgrade1,2 are run in a loop thru all other databases.
	Next, after SETUP.EXE has finished this file, SETUP.EXE executes
sp_db_upgrade3 '%',' ' (if not blank, then 'on' or 'off').  This will
finish all databases (bypassing the already done special system databaes).
	Finally, SETUP.EXE will proceed on to Messages.SQL which
starts by "DBCC TraceOff(3643)".

	sp_db_upgrade calls sp_db_upgrade1,2,3.  Its main purpose is
to be called by LOAD DB when loading a 6.0 dump onto 6.5.  It can
also be run by hand on any database (regardless of whether not fully upgraded,
partially upgraded, or fully upgraded).
*********************************/

go
dbcc traceon(3643) -- Ensure that we are allowed access to databases with old schema version.
go

use master
go
dump tran master with no_log
go
set nocount on
raiserror('This file Upgrade1.SQL can be run against either 4.2x/6.0/6.5 databases, to get up to 6.5',0,1)
go

if suser_id() <> 1
   raiserror('Aborting, because only the SA can run this script! (1)',16,127) with log
go
if suser_id() <> 1
   raiserror('Aborting, because only the SA can run this script! (2)',22,127) with log
go

declare @sysdabversion_constant integer
select  @sysdabversion_constant = 1000

declare @sqlservr_dbver int
dbcc getvalue('current_version')
select @sqlservr_dbver = @@error -- Get expected db schema version level from server.
if @sqlservr_dbver <> 408 raiserror('Error, the value %d for @sqlservr_dbver <> 408 for 6.5!',22,127,@sqlservr_dbver) with log

if exists (select * from master..sysdatabases where version > @sqlservr_dbver and version < @sysdabversion_constant)
	begin
	raiserror('Error, at least one sysdatabases.version value is greater than 408 yet less than 1000, thus is post 6.5 database.',22,127) with log
	end

if exists (select * from sysobjects where name='sp_configure' and type='P ')
	begin

	if (select version from master..sysdatabases where dbid = db_id('master')) = @sqlservr_dbver
		begin
		exec sp_configure 'allow updates',1
		reconfigure with override
		end
	else
		raiserror('(master db lower version prevents usage of sp_configure re turning on ''allow updates''. Thus -m expected.)',0,1)
	end
else
	raiserror('(sp_configure does not now exist, so it cannot be used to turn on ''allow updates''. Thus -m expected.)',0,1)
go

print ''
print 'Making sure that updates to system tables are allowed.'
go

/*
** Make sure server was started in single user mode or that sp_configure was used
** to enable updates to system tables.
*/
if (select value from syscurconfigs where config = 102) <> 1
	raiserror('Cannot run upgrade.sql unless updates to system tables are enabled.  Shutdown server and restart with the ''-m'' option.'
            ,22,127) with log
go

declare @vc1 varchar(55)
select  @vc1 = convert(varchar,getdate(),113)
raiserror('DateTime near the top of this file Upgrade1.SQL is %s' ,0,1 ,@vc1)
go

raiserror('Capture baseline info on current environment...',0,1)

raiserror(' ',0,1)
dbcc tracestatus(-1)

raiserror(' ',0,1)
dbcc useroptions

raiserror(' ',0,1)
select cfg.config,cfg.value as 'configures',cur.value as 'curconfigs'
	from master..sysconfigures cfg ,master..syscurconfigs cur
	where cfg.config = cur.config
	and cfg.status & 2 = 0
	order by cfg.config

raiserror(' ',0,1)
select 'First' as 'First',version,name,dbid from master..sysdatabases
	order by 2,3
go
print ' - - - - - - - - - - - - - - - - - - - - - - - - -'
go
print ''
print 'Checking size of master device.'
go
dbcc updateusage('master') with no_infomsgs
go

/*
** Determine amount of free space in master. We need a 4 MB buffer
** to upgrade versions >= 6 and 15 MB for versions < 6.
*/
declare @free_db_space int
declare @free_dev_space int
declare @space_needed int
declare @cmd varchar(255)

select @free_db_space =
	(select sum(size) from sysusages where dbid = 1)
	- (select sum(reserved) from sysindexes
		where indid in (0, 1, 255))

if (@free_db_space < 7680		-- This will be a 4.2x server
		and
		(select version from master..sysdatabases where dbid = 1) < 400) --4.2
	or @free_db_space < 2048	-- post 4.2x upgrade.

begin
	raiserror('(version &  sum(size) (%d)  branch taken.)',0,1,@free_db_space)

	if (select version from master..sysdatabases where dbid = 1) < 400
		select @space_needed = 7680 - @free_db_space
	else
		select @space_needed = 2048


	/*
	** See if the master device has enough free space to alter the
	** master database.
	*/
	select @free_dev_space =
	        (select sum(high-low+1) from sysdevices where name = 'master')
		- (select sum(size) from sysusages where vstart between
			(select low from sysdevices where name = 'master')
			and
			(select high from sysdevices where name = 'master'))

	if @free_dev_space < @space_needed
		begin
			print 'Expanding master device.'
			select @cmd =
				'disk resize name = master,size = ' +
				convert(varchar(15), @space_needed +
				(select sum(high-low+1) from sysdevices where name = 'master') - @free_dev_space)
			execute(@cmd)
			if @@error <> 0
				raiserror('Could not expand master device.  See event viewer or errorlog for details.'
                     ,16,127) with log
		end
	else
		raiserror('(NOT  @free_dev_space < @space_needed  (%d>=%d))',0,1,@free_dev_space,@space_needed)

	print ''
	print 'Altering master database.'

	select @cmd='alter database master on master = ' +
		convert(varchar(10),ceiling(@space_needed/512.))

	execute(@cmd)
	if @@error <> 0
		raiserror('Could not expand master database.  Please free some space on the master device or expand it.'
               ,16,127) with log
end
go


/*
** Add xp_regread if necessary.
*/
if not exists (select * from sysobjects where name = 'xp_regread')
	begin
	raiserror('(addextendedproc a1)',0,1)
	dbcc addextendedproc( 'xp_regread', 'xpstar.dll')
	end
go


----print 'Shrink size of model db to 1 Mb (512 2_Kb pages) IF this is a NewInstall run.'
go

----Allow a full database to fit on a 1.44Mb floppy (removable media, fallback).
declare @i varchar(255)

exec master..xp_regread 'HKEY_LOCAL_MACHINE',
	 'SOFTWARE\Microsoft\MSSQLServer\Setup', 'SetupStatus',
	  @i out

print ''

if isnull(lower(@i),'null') not in ('upgrade','installed')
	begin
		print 'Performing new installation - shrinking model database.'

		----A dynam exec handles case where 'allow updates' is set relatively late.

		execute('update master..sysusages set size = 512
			where dbid = (select db_id(''model''))')

		dbcc dbrepair('model',remap)
	end
else
		print 'Performing upgrade - size of model database not modified.'

go

print ''
print 'Checking to see if sysdatabases needs to be upgraded.'
go
if not exists (select * from syscolumns
		where name = 'category'
		and id=object_id('sysdatabases'))
	begin
		print ''
		print 'Via Alter, changing sysdatabases, adding column  ''category''.'
		alter table sysdatabases add category int null --master db only
	end
else
		raiserror('sysdatabases already has ''category'' column.',0,1)
go

print ''
print 'Initializing sydatabases.category.'
update master..sysdatabases set category = 0 where category is null
go

print ''
print 'Make sure the maxlen of sysdatabases clustered index was updated'
print 'to include the new category column.'
update sysindexes set maxlen = 78 where id = object_id('sysdatabases') and
   indid = 1 and maxlen <> 78
go

print ''
print 'Checking to see if sysdevices needs to be upgraded.'
go
if not exists (select * from syscolumns
        where name = 'stripeset'
        and id=object_id('sysdevices'))
    begin
        print ''
        print 'Updating sysdevices.'
        alter table sysdevices add stripeset varchar(30) null
    end
else
        raiserror('sysdevices already has column ''stripeset''.',0,1)
go


print ''
print 'Checking to see if sysservers needs to be upgraded.'
go
if not exists (select * from syscolumns
	where name = 'topologyx'
	and id=object_id('sysservers'))
    begin
        print ''
	print 'Updating sysservers.'
	alter table sysservers add topologyx int null, topologyy int null
    end
else
        raiserror('sysservers already has column ''topologyx''.',0,1)
go


/*****************************
sysprocesses colums update
******************************/

print ''
print 'Checking to see if sysprocesses needs to be upgraded.'
go

delete from syscolumns where id = 32 and colid > 4
go
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,5,0,47,30,18,1,0,0,'hostname')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,6,0,47,30,48,1,0,0,'program_name')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,7,0,47,8,78,1,0,0,'hostprocess')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,8,0,47,16,86,1,0,0,'cmd')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,9,0,56,4,104,7,0,0,'cpu')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,10,0,56,4,108,7,0,0,'physical_io')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,11,0,56,4,112,7,0,0,'memusage')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,12,0,52,2,116,6,0,0,'blocked')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,13,0,45,2,118,3,0,0,'waittype')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,14,0,52,2,120,6,0,0,'dbid')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,15,0,52,2,122,6,0,0,'uid')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,16,0,52,2,124,6,0,0,'gid')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,17,0,61,8,128,1,0,0,'login_time')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,18,0,61,8,136,1,0,0,'last_batch')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,19,0,47,30,144,1,0,0,'nt_domain')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,20,0,47,30,174,1,0,0,'nt_username')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,21,0,47,12,204,1,0,0,'net_address')
insert syscolumns (id,number,colid,status,type,length,offset,usertype,cdefault,domain,name)
values (32,0,22,0,47,12,216,1,0,0,'net_library')
go

/* Update sysindexes to reflect the new mixlen,maxlen and rowpage for sysprocesses */
update sysindexes set minlen=228,maxlen=228,rowpage=8 where id =32 and indid in (0,1)

/************************************
end of sysprocesses columns update
*************************************/
go


/* Set creation date for master and model databases. */
print ''
print 'Setting creation date for master and model databases.'
go
update master..sysdatabases set crdate = getdate()
	where name in ('master','model') and crdate like '%1900%'
go

/* Set creation date for system tables. */
print ''
print 'Setting creation date for system tables.'
go
update sysobjects set crdate = getdate()
	where id < 100 and crdate like '%1900%'
go

print ''
print 'Fixing syscolumns.usertype for syscurconfigs entry.'
go
/* Fix usertype value for syscurconfigs before upgrade. */
update syscolumns set usertype = 6
	where name='status' and id=object_id('syscurconfigs')
go

print ''
print 'Checking page counts in ''model'' database.'
go
dbcc checkdb('model') with no_infomsgs
go

--print ''
--print 'Initializing sysconfigures.status.'
--go
--update sysconfigures set status = status | 2 where config >= 1000
go

---- Create procedures to perform upgrade functions.

if exists (select * from sysobjects
			where name = 'sp_db_upgrade1'
			and type = 'P ')
	begin
		raiserror('Dropping    sp_db_upgrade1...',0,1)
			drop procedure sp_db_upgrade1
	end
else
		raiserror('proc sp_db_upgrade1 does not pre-exist.',0,1)

if exists (select * from sysobjects
			where name = 'sp_db_upgrade2'
			and type = 'P ')
	begin
		raiserror('Dropping    sp_db_upgrade2...',0,1)
			drop procedure sp_db_upgrade2
	end
else
		raiserror('proc sp_db_upgrade2 does not pre-exist.',0,1)
go


print ''
print 'Creating procedure sp_db_upgrade1.'
go
create procedure sp_db_upgrade1  --1996/03/19 15:44
as
--Makes SQL 6.5 specific schema changes to the current database.
--Safe to rerun.
/***********************
SYSDATABASES.VERSION
--------------------
   Typically, 406 means not upgraded to 6.5, still plain 6.0 (1=4.21A).
1406 means at least one aspect has been upgraded (by sp_db_upgrade1
since it goes first).  2406 means sp_db_upgrade1 has finished.
3406 means sp_db_upgrade2 has finished.  408 means sp_db_upgrade3
has finished, and thus the database is now fully 6.5.
***********************/


   declare @curdbversion integer
          ,@sysdabversion_constant integer
          ,@_error integer ,@_rowcount integer
	,@sysdab_status_balk integer

	declare @curdbname varchar(30)
	declare @pmsg varchar(255)
	declare @dumptran varchar(255)

	select @sysdab_status_balk = (256|512|1024) --(32|64|128|256|512|1024) --14453 LOAD DB 6.0-->6.5 sets 32.

	select @curdbname = db_name()
         ,@sysdabversion_constant = 1000 --NORMAL sysdatabases.version values must never reach this high!

	select @dumptran = 'dump tran '+@curdbname+' with no_log'

	raiserror('Top of sp_db_upgrade1.  Upgrading and creating new system tables, db=%s.',0,1,@curdbname)


	/* See if database is 'offline' or 'Read only' and, if so, exit. */
	if (select status & @sysdab_status_balk
		from master..sysdatabases where name=db_name())
		<> 0
		begin
			select @pmsg = 'Database '''+@curdbname+''' cannot be upgraded because it is ''offline'' or ''Read only''.'
			print ''
			print @pmsg
			print 'Perhaps utilize sp_dboption to change status.'
			return(0)
		end

	execute(@dumptran)


---- Handle sysdatabases.version flag/setting.

   select @curdbversion = version from master..sysdatabases where name=@curdbname

   --How many times has @sysdabversion_constant been added so far?
   if (@curdbversion -
           (@curdbversion % @sysdabversion_constant))
                    / @sysdabversion_constant >= 2
      begin
      raiserror('Since curdbversion=%d is at least twice the constant value %d, it appears the work of this procedure has already been completed for this database.  Jumping to end of this proc.'
               ,0,1 ,@curdbversion ,@sysdabversion_constant)
      goto LABEL_75DONE
      end

   if @curdbversion >= @sysdabversion_constant
      begin
      raiserror('Near top of sp_db_upgrade1, sysdatabases.version=%d ( >=%d ), and thus has already been modified at least once (perhaps incompletely) by an earlier run.'
               ,0,1,@curdbversion,@sysdabversion_constant)
      end
   else  -- NORMAL exec path.
      begin
   	update master..sysdatabases set version = version + @sysdabversion_constant
         where name=@curdbname and version < @sysdabversion_constant
      raiserror('Near top of sp_db_upgrade1, sysdatabases.version=%d has now been increased by %d.  This means the system objects should be considered to be in a state between old v. new versions of MS SQL.'
               ,0,1,@curdbversion,@sysdabversion_constant)
      end


	print 'Section for updating sysobjects (schema_ver).'

	if exists (select * from syscolumns
		where id=object_id('sysobjects')
		and name='schema')
		begin
		raiserror('syscolumns entry for sysobjects.schema will now be upd to schema_ver, db=%s.',0,1,@curdbname)
		execute('update syscolumns set name=''schema_ver''
			where id=object_id(''sysobjects'') and name = ''schema''')

   	if @@error<>0
	   	begin
		   raiserror('Could not successfully upgrade table ''sysobjects'' (%s).'
			   	,11,1,@curdbname)
   		return(1)
	   	end
   	else
	   	begin
		   raiserror('Upgraded col name sysobjects.schema_ver will now be incremented, db=%s.',0,1,@curdbname)
   		execute('update sysobjects set schema_ver = schema_ver+1
	   			where id = object_id(''sysobjects'')')
		   end
		end
   else
      raiserror('sysobjects.schema col of 6.0 is already renamed schema_ver for 6.5',0,1)


	print 'Section for updating sysindexes (OrigFillFactor).'

	if exists (select * from syscolumns
			where id=object_id('sysindexes')
				and name='usagecnt')
		begin
		raiserror('syscolumns row for sysindexes OrigFillFactor will be updated.',0,1)
		select 'sysindexes_before'='sysindexes_before',colid,name
		   from syscolumns
		   where id=object_id('sysindexes') and name in ('usagecnt','OrigFillFactor')
		   order by colid

		update syscolumns set
			name='OrigFillFactor', type=48, usertype=5,length=1
	        	where id=object_id('sysindexes') and name = 'usagecnt'

   	if @@error<>0
	   	begin
		   	print 'Could not successfully upgrade table ''sysindexes'' (OrigFillFactor).'
			   return(1)
   		end
	   else
		   begin
	      print '''sysindexes'' successfully upgraded (sysobjects.schema_ver).'
   		select 'sysindexes_after '='sysindexes_after ',colid,name
	   	   from syscolumns
		      where id=object_id('sysindexes') and name in ('usagecnt','OrigFillFactor')
		      order by colid

   			execute('update sysobjects set schema_ver = schema_ver + 1
	   			where id=object_id(''sysindexes'')')
		   end
		end
	else
		print 'New 6.0 syscolumns row for sysindexes OrigFillFactor already Updated from old usagecnt.'



	print 'Converting 4.2 sysobjects.expdate to sysobjects.version, for 6.0+ (if not already done).'
	update syscolumns
		set name='version', type=56, usertype=7, length=4
		where id=object_id('sysobjects')
			and name = 'expdate'

   select @_error=@@error ,@_rowcount=@@rowcount

	if @_error<>0
		begin
			print 'Could not successfully upgrade table ''sysobjects'' (syscolumns.name=expdate-->version).'
			return(1)
		end
	else
		begin
         if @_rowcount>0
            begin
   			print '''Sysobjects.expdate'' successfully converted (increment schema_ver).'
	   		execute('update sysobjects set schema_ver = schema_ver + 1
   		   		where id=object_id(''sysobjects'')')
            end
         else
            raiserror('Column sysobjects.expdate was already renamed to .version, fine.',0,1)
		end


	print 'Checking to see if ''sysreferences'' exists (new in 6.0).'
	if not exists (select * from sysobjects
					where name = 'sysreferences')
		begin
			print 'Creating sysreferences.'

			create table sysreferences
			(
			constid		int		not null,
			fkeyid		int		not null,
			fkeydbid	smallint	not null,
			rkeyid		int		not null,
			rkeydbid	smallint	not null,
			rkeyindid	smallint	not null,
			keycnt		smallint	not null,
			fkey1		tinyint		not null,
			fkey2		tinyint		not null,
			fkey3		tinyint		not null,
			fkey4		tinyint		not null,
			fkey5		tinyint		not null,
			fkey6		tinyint		not null,
			fkey7		tinyint		not null,
			fkey8		tinyint		not null,
			fkey9		tinyint		not null,
			fkey10		tinyint		not null,
			fkey11		tinyint		not null,
			fkey12		tinyint		not null,
			fkey13		tinyint		not null,
			fkey14		tinyint		not null,
			fkey15		tinyint		not null,
			fkey16		tinyint		not null,
			rkey1		tinyint		not null,
			rkey2		tinyint		not null,
			rkey3		tinyint		not null,
			rkey4		tinyint		not null,
			rkey5		tinyint		not null,
			rkey6		tinyint		not null,
			rkey7		tinyint		not null,
			rkey8		tinyint		not null,
			rkey9		tinyint		not null,
			rkey10		tinyint		not null,
			rkey11		tinyint		not null,
			rkey12		tinyint		not null,
			rkey13		tinyint		not null,
			rkey14		tinyint		not null,
			rkey15		tinyint		not null,
			rkey16		tinyint		not null
			)
			on system

			if @@error<>0
				begin
				print 'Could not successfully create table ''sysreferences''.'
				return(1)
				end
			else
				print '''Sysreferences'' successfully created (now IXs).'

			create unique clustered index ucsysreferences
				on sysreferences(constid) on system
			create nonclustered index nc1sysreferences
				on sysreferences(fkeyid) on system
			create nonclustered index nc2sysreferences
				on sysreferences(rkeyid) on system
		end
	else
		raiserror('''sysreferences'' already exists.',0,1)


	print 'Checking to see if ''sysconstraints'' exists (new in 6.0).'
	if not exists (select * from sysobjects
					where name = 'sysconstraints')
		begin
			print 'Creating sysconstraints.'

			create table sysconstraints
			(
			constid		int	not null,
			id		int	not null,
			colid		tinyint not null,
			spare1		tinyint	not null,
			status		int	not null,
			actions		int	not null,
			error		int	not null
			)
			on system

			if @@error<>0
				begin
					print 'Could not successfully create table ''sysconstraints''.'
					return(1)
				end
			else
				print '''Sysconstraints'' successfully created (now IXs).'

		        create clustered index csysconstraints
				on sysconstraints(id, colid) on system
			create unique nonclustered index unc1sysconstraints
				on sysconstraints(constid) on system
 		end
	else
		raiserror('''sysconstraints already exists.',0,1)


	if not exists (select * from sysobjects
					where name = 'syssubscriptions')
		begin
			print 'Creating syssubscriptions.'

			CREATE TABLE syssubscriptions
			(
			artid     int         NOT NULL,
			srvid     smallint    NOT NULL,
			dest_db   varchar(30) NOT NULL,
			status    tinyint     NOT NULL,
			sync_type tinyint     NOT NULL,
			timestamp
			)
			on system

			if @@error<>0
				begin
					print 'Could not successfully create table ''syssubscriptionss''.'
					return(1)
				end
			else
				print '''Syssubscriptions'' successfully created (now IXs).'

			create unique nonclustered index unc1syssubscriptions
				on syssubscriptions (artid, srvid) on system
		end
	else
		raiserror('''syssubscriptions already exists (new in 6.0).',0,1)

	-- Add precision and scale columns to systypes and syscolumns if necessary.

	print 'Checking ''systypes'' for prec and scale columns (new in 6.0).'
	if not exists (select * from syscolumns
		where id = object_id('systypes')
		and name = 'prec')
		begin
			print 'Adding columns for support of exact numeric datatypes.'
			alter table systypes add prec tinyint null, scale tinyint null
			if @@error<>0
				begin
					print 'Could not successfully upgrade table ''systypes''.'
					return(1)
				end
			else print '''Systypes'' successfully upgraded'
		end
	else
		raiserror('''prec'' and ''scale'' are already cols in ''systypes''.',0,1)

	execute(@dumptran)

	print 'Checking ''syscolumns'' for prec and scale columns (new in 6.0).'
	if not exists (select * from syscolumns
		where id = object_id('syscolumns')
		and name = 'prec')
		begin
			print 'Adding columns for support of exact numeric datatypes.'
			alter table syscolumns add prec tinyint null, scale tinyint null
			if @@error<>0
				begin
					print 'Could not successfully upgrade table ''syscolumns''.'
					return(1)
				end
			else print '''Syscolumns'' successfully upgraded'
		end
	else
		raiserror('''prec'' and ''scale'' are already cols in ''syscolumns''.',0,1)


	---------------------  add sysprotects.grantor col  -----------------


	print 'Checking ''sysprotects'' for grantor column (new in 6.5).'

	if not exists (select * from syscolumns
		where id = object_id('sysprotects')
		and name = 'grantor')
		begin
			ALTER table sysprotects add grantor smallint null

			if @@error<>0
				begin
				print 'Could not successfully upgrade table ''sysprotects''.'
				return(1)
				end
			else
				print '''sysprotects'' successfully upgraded.'
		end
	else
		raiserror('''grantor'' col already exists in ''sysprotects''.',0,1)


	execute(@dumptran)

/*** See sp_db_upgrade3
	------------------------  dbcc, extracting syscomments.text  ---------

	--execute(@dumptran)

	-- Upgrade procs., triggers, etc.
	print 'Upgrading procedures, triggers, views and rules.'

	dbcc upgradedb(null)
	if @@error<>0
		begin
		print ''
		print 'WARNING - Encountered error(s) upgrading some procedures, views, triggers, rules or defaults.'
		print 'These objects will NOT be accessible after completion of upgrade.'
		print 'Please see event viewer or errorlog for more information and then use sp_db_upgrade <dbname> to complete the upgrade process for this database.'
		end
	else
		print '''dbcc upgradedb'' successfully completed without errors.'

	execute(@dumptran)
***/

--	select @pmsg='Running dbcc updateusage on '''+db_name()+'''.'
--	print @pmsg
--	dbcc updateusage (0) with count_rows

   ---- Another increase of sysdab.ver, to help precise restartability,
   ----   telling sp_db_upgrade2 that it can run.

	update master..sysdatabases
          set version = version + @sysdabversion_constant
          where name=@curdbname
          and
               (version -
                     (version % @sysdabversion_constant))
                           / @sysdabversion_constant < 2

        select @_error=@@error ,@_rowcount=@@rowcount

   if (@_rowcount<>1 or @@error<>0)
      raiserror('ERROR, could not add constant to sysdatabases.version (2)!',18,1) with log
   else
      raiserror('Near bottom of sp_db_upgrade1, sysdatabases.version incremented by constant.  Next should be sp_db_upgrade2.',0,1)

LABEL_75DONE:

	raiserror('Ending sp_db_upgrade1.  Database ''%s'' is successfully upgraded so far, now might need data initialization by sp_db_upgrade2.'
			,0,1,@curdbname)
	print ' '  --resets @@error=0, from 50000 (tho no longer needed if isql.exe in 6.50.167+ used)
	return(0)
--end of sp_db_upgrade1
go

dump tran master with no_log
go


---- We should get master db fully upgraded before starting any other dbs.
execute('use master
		declare @rc int
		exec @rc = sp_db_upgrade1
		if @rc <> 0 RaisERROR(''Bad rc from sp_db_upgrade1!'',18,74) with log
	   ')
-- If no error, then can create proc sp_db_upgrade2, in master.
go


/***
Had to add new columns before referencing them in declaration
of procedure that initializes existing rows and adds new ones.
Thus sp_db_upgrade1 & sp_db_upgrade2 (& sp_db_upgrade3) are
separate sprocs run/created in certain order.
***/
print ''
print 'Creating procedure sp_db_upgrade2.'
go
create procedure sp_db_upgrade2  --1996/03/22 15:22
   as
   set nocount on
   declare @sysdabversion_constant integer
          ,@_error integer ,@_rowcount integer
	declare @pmsg varchar(255)
	declare @dumptran varchar(255)
	declare @id int
	declare @indid int
	declare @dbstat int
	declare @repl_uid int
	declare @curdbname varchar(30)
          ,@curdbversion integer
	declare @counter int
		,@sysdab_status_balk integer

	select @sysdab_status_balk = (256|512|1024)
	select  @curdbname = db_name()  ,@counter=0
          ,@sysdabversion_constant = 1000
	select @dumptran = 'dump tran '+@curdbname+' with no_log'

	raiserror('At top inside sp_db_upgrade2, db=%s.',0,1,@curdbname)

	select @curdbversion = version from master..sysdatabases where name=@curdbname

	if (@curdbversion -
                     (@curdbversion % @sysdabversion_constant))
                           / @sysdabversion_constant < 2
		begin
		raiserror('sysdatabases.version=%d, which is so low that sp_db_upgrade1 could not have prepared this database for this procedure.'
			,0,1,@curdbversion)
		return(0)
		end

	-- Exit if db is unavailable.

	if (select status & @sysdab_status_balk
		from master..sysdatabases where name=@curdbname)
		<> 0
		begin
			select @pmsg = 'Database '''+db_name()+''' cannot be initialized because it is unavailable (offline,read only...).'
			raiserror('%s',0,1,@pmsg)
			print 'Use sp_dboption to change status.'
			return(0)
		end

	select @pmsg = 'Initializing database '''+db_name()+'''.'
	print ''
	print @pmsg

	execute(@dumptran)

	update sysobjects set crdate = getdate()
		where id < 100 and crdate like '%1900%'
	select @_error=@@error ,@_rowcount=@@rowcount
	raiserror('Number of sysobjects.crdate cells Upd where crdate was LIKE '' 1900 '' = %d.  If starting from 6.0 (rather than 4.2), then answer should be =0.'
            ,0,1, @_rowcount)


	print 'Converting timestamp datatype to fixed length.'
   -- Unconditional, but harmlessly so.
	update systypes set type=45,allownulls=0
			where name = 'timestamp'

	print 'Initializing new columns in systypes.'
	update systypes set prec = NULL, scale = NULL
		where type in (34,35)			-- Text/image
	update systypes set prec = length, scale = NULL
		where type in (37,38,39,45,47)		-- char/varchar,
							-- binary/varbinary
							
	update systypes set prec = 10, scale = 0
		where type in (38,56)			-- int/intn
	update systypes set prec = 5, scale = 0
		where type = 52				-- smallint
	update systypes set prec = 3, scale = 0
		where type = 48				-- tinyint
	update systypes set prec = 1, scale = 0
		where type = 50				-- bit
	update systypes set prec = 19, scale = 4
		where type in (60,110)			-- money/moneyn
	update systypes set prec = 10, scale = 4
		where type = 122			-- smallmoney


	update systypes set prec = 24, scale = NULL	--6.0=7, 6.5=24
		where type = 59				-- real
		and (  (prec<>24 or prec is null)
                    OR  scale is not null
		    )
	update systypes set prec = 53, scale = NULL	--6.0=15, 6.5=53
		where type in (62,109)			-- float/floatn
		and (  (prec<>53 or prec is null)
		    OR  scale is not null
		    )
	select @_error=@@error ,@_rowcount=@@rowcount
	raiserror('Number of systypes.prec cells Upd Set=53 where .type in 62,109 (float/n) = %d.  Answer should be at least >=2 for upgrades of either 4.2 or 6.0 (to 6.5).'
            ,0,1, @_rowcount)


	update systypes set prec = 23, scale = 3
		where type in (61,111)			-- datetime/datetimn
	update systypes set prec = 16, scale = 0
		where type = 58				-- smalldatetime

	print 'Inserting rows for exact numerics into systypes.'
	if not exists (select * from systypes where name = 'numeric')
		begin
		insert systypes
		(uid,usertype,variable,allownulls,type,length,tdefault,domain,name,printfmt,prec,scale)
		values
		(1,10,0,1,63,17,0,0,'numeric',NULL,38,38)

		raiserror('systypes rows for exact numerics were just inserted.',0,1)
		end
	else
		raiserror('systypes already has rows for exact numerics.',0,1)

	if not exists (select * from systypes where name = 'numericn')
		insert systypes
		(uid,usertype,variable,allownulls,type,length,tdefault,domain,name,printfmt,prec,scale)
		values
		(1,25,0,1,108,17,0,0,'numericn',NULL,38,38)

	if not exists (select * from systypes where name = 'decimal')
		insert systypes
		(uid,usertype,variable,allownulls,type,length,tdefault,domain,name,printfmt,prec,scale)
 		values
 		(1,24,0,1,55,17,0,0,'decimal',NULL,38,38)

	if not exists (select * from systypes where name = 'decimaln')
	insert systypes
		(uid,usertype,variable,allownulls,type,length,tdefault,domain,name,printfmt,prec,scale)
		values
		(1,26,0,1,106,17,0,0,'decimaln',NULL,38,38)

	execute(@dumptran)

	print 'Initializing new columns in syscolumns.'
	update syscolumns
		set prec = t.prec, scale = t.scale
			from systypes t, syscolumns c
			where c.usertype = t.usertype
			and c.usertype not in (10,24,25,26) /* exact numerics. */

	update syscolumns set prec = length, scale = NULL
		where type in (37,38,39,45,47)	/* char/varchar,binary/varbinary */

    /*
    ** Can't update more than one row at a time in sysindexes
    ** due to an internal restriction.
    */
	print 'Initializing sysindexes.OrigFillFactor.'
	select @counter=0
	execute('declare c2 cursor for select id from sysindexes (holdlock)')
	open c2
	fetch c2 into @id
	while @@fetch_status <> -1
		begin
			select @counter=@counter+1
			update sysindexes
				set OrigFillFactor = 0  -- Intentionally leaving this invasion to reduce risk of SETUP failure due to 'out of disk space'.
					where current of c2
			fetch c2 into @id
		end

	deallocate c2

	raiserror('Number of sysindexes.OrigFillFactor cells Upd to 0 was %d',0,1,@counter)

	print 'Converting syscomments.texttype to bitmap.'

	update syscomments set texttype = 2 where texttype = 0


	---------------  initialize new sysprotects.grantor col  ----------

	if 0 = (select count(*) from sysprotects where grantor is NOT null)
		begin

		raiserror('Some sysprotects.grantor cells are Null (expected for upgrade to 6.5), so fix.',0,1)

		UPDATE		 sysprotects
			set	 grantor =
					(select	 sysobjects.uid
					 from	 sysobjects
					 where	 sysobjects.id = sysprotects.id
					)
			where	 id <> 0


		if (@@error <> 0)
			begin
			print 'sysprotects.grantor init errored!'
			return(1)
			end


		-----  modify index  -----

		DROP index sysprotects.sysprotects

		CREATE Clustered
			index	sysprotects
			on	sysprotects
				(id, action, grantor, uid, protecttype)


		if (@@error <> 0)
			begin
			print 'index sysprotects.sysprotects creation errored!'
			return(1)
			end

		print 'sysprotects.grantor & new index initialized.'
		end
	else
		raiserror('All sysprotects.grantor cells are already nonNull.',0,1)



	-----------------  create sys-tables which have identity col  -------

	/*
	** Need to create these here since identity cols. depend on
	** on values in prec. and scale. cols.
	*/
	print 'Checking to see if ''sysarticles''already exists (new in 6.0).'

	if not exists (select * from sysobjects where name = 'sysarticles')
		begin
			print 'Creating sysarticles.'

			create table sysarticles
			(
			artid		int		identity,
			columns		varbinary(32)	NULL,
			creation_script	varchar(127)	NULL,
			del_cmd		varchar(255)	NULL,
			description	varchar(255)	NULL,
			dest_table	varchar(30)	NOT NULL,
			filter		int		NOT NULL,
			filter_clause	text 		NULL,
			ins_cmd		varchar(255)	NULL,
			name		varchar(30)	NOT NULL,
			objid		int		NOT NULL,
			pubid		int		NOT NULL,
			pre_creation_cmd tinyint	NOT NULL,
			status		tinyint		NOT NULL,
			sync_objid	int		NOT NULL,
			type		tinyint		NOT NULL,
			upd_cmd		varchar(255)	NULL
			)
			on system

			if @@error<>0
				begin
				print 'Could not successfully create table ''sysarticles''.'
				return(1)
				end
			else
				print '''Sysarticles'' successfully created, with identity (now IXs).'

			create unique nonclustered index unc1sysarticles
				on sysarticles(artid, pubid) on system
		 end
	else
		raiserror('sysarticles already exists.',0,1)

	execute(@dumptran)

	print 'Checking to see if ''syspublications'' exists (new in 6.0).'

	if not exists (select * from sysobjects
				where name = 'syspublications')
		begin
			print 'Creating syspublications.'

			CREATE TABLE syspublications (
			description	varchar(255) 	NULL,
			name 		varchar(30)	NOT NULL,
			pubid 		int		identity,
			repl_freq 	tinyint		NOT NULL,
			restricted 	bit		NOT NULL,
			status 		tinyint		NOT NULL,
			sync_method	tinyint		NOT NULL,
			taskid		int		NOT NULL
			)
			on system

			if @@error<>0
				begin
				print 'Could not successfully create table ''syspublications''.'
				return(1)
				end
			else
			print '''Syspublications'' successfully created, with identity (now IXs).'

			create unique nonclustered index unc1syspublications
				on syspublications (pubid) on system
			create unique nonclustered index unc2syspublications
				on syspublications (name) on system
		end
	else
		raiserror('syspublications already exists.',0,1)

---- Ensure 0 for some sysreferences columns
	print ''
	print 'Updating sysrefences, since in 6.* DRI cannot cross db boundary.'
	update sysreferences set
				fkeydbid = 0,
				rkeydbid = 0
			where  fkeydbid > 0
				or rkeydbid > 0

	execute(@dumptran)

	---- Set sysdatabases.version to indicate upgraded progress.

	--declare @sqlservr_dbver int
	--dbcc getvalue('current_version')
	--select @sqlservr_dbver = @@error -- Get expected db schema version level from server.

	update master..sysdatabases set version = version + @sysdabversion_constant
		where name=@curdbname
		and (@curdbversion -
			(@curdbversion % @sysdabversion_constant))
				/ @sysdabversion_constant = 2

	raiserror('Ending sp_db_upgrade2.  Database %s upgrade progressing fine so far.  Next should be sp_db_upgrade3.'
			,0,1,@curdbname)

	return(0)
-- End of sp_db_upgrade2
go


dump tran master with no_log
go

--
go
if exists (select * from master..sysobjects where type='P ' and
	name='sp_db_upgrade3')
	begin
	print 'drop procedure sp_db_upgrade3...'
	       drop procedure sp_db_upgrade3
	end
go
print       'create procedure sp_db_upgrade3...'
go
CREATE PROCEDURE sp_db_upgrade3  --1996/04/01 21:00
    @DBNamePattern      varchar(44)    = null  -- '%'
   ,@Set_QI             varchar( 4)    = null  -- SETUP.EXE switch (sqlservr.exe default 'OFF')
as

--DBCC TRACEON(3643)  --Needed in effect, but assumed ON from context.

/***********************************************
   See 6.5 SETUP.EXE switch /t SetQuotedId = ON (or =OFF) option.
   Unlike sp_db_upgrade1,2 this sproc can loop thru databases.
   Avoid using isql.exe /b switch on this file.

PARMS
-----
   @DBNamePattern
      NULL:  Means current db.
      nonNULL:  Means interpret as a LIKE pattern.

   @Set_QI
      NULL or Blank:  Means current setting.
      nonNULL:  Means desired setting (on/off).
***********************************************/

set nocount    on
--set ansi_nulls on   -Was not a 6.0 option.

print ' . . . . . . . .'
raiserror('Executing sp_db_upgrade3....',0,1) with nowait

declare
       @RetCode                  integer
      ,@_error                   integer
      ,@CurrentDBName            varchar(30)
      ,@CurrentDBId              smallint
      ,@sysdabversion_constant   integer
      ,@sqlservr_dbver           integer
      ,@sysdab_status_balk       integer --See spt_values.type='D  '
      ,@String_Set_QI            varchar(35)

select
       @RetCode                  = 0   --  =0 means good;  >0 means bad
      ,@sysdabversion_constant   = 1000
      ,@sysdab_status_balk       = (256|512|1024)

DBCC getvalue('current_version')
select  @sqlservr_dbver = @@error -- Get expected db schema version level from server (408 in 6.5).

if (suser_id() <> 1)
   begin
   raiserror('Only the SA can run this procedure.',11,1)
   select @RetCode = @@error
   goto LABEL_86_RETURN
   end

if (@DBNamePattern is null)
   begin
   select @DBNamePattern = db_name()
   end


if (@Set_QI is null)
   begin
   select @Set_QI = ' '
   end

select @Set_QI = lower(@Set_QI)

if (@Set_QI not in ('on' ,'off' ,' '))
      begin
      raiserror('Value ''%s'' for SET QUOTED_IDENTIFIER is unrecognized.',11,1,@Set_QI)
      select @RetCode = @@error
      goto LABEL_86_RETURN
      end

if (@Set_QI <> ' ')
      begin
      select @String_Set_QI = 'SET QUOTED_IDENTIFIER ' + @Set_QI
      end
else
      begin
      select @String_Set_QI = ' '
      end

select @CurrentDBName = char(0x00)
select @CurrentDBId   = -98

WHILE (11=11)
   begin

   SELECT
                @CurrentDBId     = min(sysdab.dbid)
         from   master..sysdatabases   sysdab
         where  @CurrentDBId     <     sysdab.dbid
         and    sysdab.name    like    @DBNamePattern
         and    @sysdab_status_balk & sysdab.status = 0
         and    sysdab.dbid      > 0
         and   (
                ((sysdab.version -
                     (sysdab.version % @sysdabversion_constant))
                           / @sysdabversion_constant = 3) --such as 3406
--               OR
--                (sysdab.version = @sqlservr_dbver)
               )

   if (@CurrentDBId is null)
      begin
      BREAK
      end

   select @CurrentDBName = db_name(@CurrentDBId)

   print ' '
   raiserror('sp_db_upgrade3, database = ''%s'' ...',0,1,@CurrentDBName) with nowait

   EXECUTE(
   '
   use master
   dump transaction ' +@CurrentDBName+ ' with no_log
   ')

   EXECUTE(
   '
   use ' +@CurrentDBName+ '
   ' +@String_Set_QI+ '
   DBCC UPGRADEDB(null)
   ')

   select @_error = @@error  --Some errors will end control-of-flow.

   print 'Forcing sysdatabases.version=408 for this databases.'

   UPDATE          master..sysdatabases  --db is usable anyway!
         set       version = 408 --6.5
         where     name    = @CurrentDBName
         and
                  (version -
                     (version % @sysdabversion_constant))
                         / @sysdabversion_constant >= 3

   if (@_error <> 0)
      begin
      print 'WARNING - Encountered error(s) upgrading some procedures, views, triggers, rules or defaults.'
      print 'These objects will NOT be accessible after completion of upgrade.'
      print 'Please see event viewer or errorlog for more information and then use sp_db_upgrade <dbname> to complete the upgrade process for this database.'
      raiserror('At dynamic execute time the Set Quoted_Identifier values was  @Set_QI=''%s''',0,1,@Set_QI)
      DBCC USEROPTIONS
      select @RetCode = 1
      end

   EXECUTE(
   '
   use master
   dump transaction ' +@CurrentDBName+ ' with no_log
   ')

   end  --loop 11

LABEL_86_RETURN:

--DBCC TRACEOFF(3643)  --Messages.SQL starts with this command.

raiserror('sp_db_upgrade3, RETURN = %d',0,1,@RetCode)

return @RetCode
-- End of sp_db_upgrade3
go
--

/*
** Now we can create a 'wrapper' proc. for future manual upgrades.
*/
print ''
print 'Creating procedure sp_db_upgrade.'
go
if exists (select * from sysobjects
			where name = 'sp_db_upgrade'
			and type = 'P ')
	begin
		raiserror('Dropping    sp_db_upgrade...',0,1)
			drop procedure sp_db_upgrade
	end
else
	raiserror('sp_db_upgrade does not pre-exist.',0,1)
go

create procedure sp_db_upgrade  --1996/03/27 16:26
	 @dbname varchar(30) = null
	,@set_qi varchar(4) = null
as
/**
This proc should be run in master db.
This sproc execs sp_db_upgrade1,2,3.
**/
	set nocount on
	print ' '
	declare @db_orig_status smallint ,@db_mod_status smallint
	declare @cur_db varchar(30)
	select  @cur_db=db_name()	--Keep track of which db we started in.

	if @dbname is null
		begin
		select @dbname = db_name()
		end

--------  Handle special cases, such as an Offline database.  ---------

	select @db_orig_status = min(d.status)
		from master..sysdatabases d
		where name = @dbname

	if (@db_orig_status is null)
		begin
		raiserror('No database named ''%s'' found in sysdatabases.',11,1,@dbname)
		return (1)
		end

	raiserror('The procedure sp_db_upgrade is now running in database ''%s'', to upgrade database ''%s''.',0,1,@cur_db,@dbname)

	if (@db_orig_status & 256 <> 0)  --"not recovered" = "suspect"
		begin
		raiserror('The database ''%s'' has a sysdatabases.status value of %d, which includes an ON value for the ''not recovered'' (''suspect'') bit.',17,1,@dbname,@db_orig_status)
		return (1)
		end

	dbcc traceon(3643)

	if (@db_orig_status & 512 <> 0)
		begin
		exec sp_dboption @dbname ,'offline' ,'false'
		end

	if (@@error <> 0)
		begin
		dbcc traceoff(3643)
		return(1)
		end

	if (@db_orig_status & 1024 <> 0)
		begin
		exec sp_dboption @dbname ,'read only' ,'false'
		end

	if (@@error <> 0)
		begin
		dbcc traceoff(3643)
		return(1)
		end

	select @db_mod_status = min(d.status)
		from master..sysdatabases d
		where name = @dbname

	if (@db_mod_status <> @db_orig_status)
		begin
		raiserror('The database status has been temporarily modified to %d, but will be returned to %d upon completion.',0,1,@db_mod_status,@db_orig_status)
		end

---------  Handle Quoted_Identifier  ---------------

	if (@set_qi is null)
		select @set_qi = ' '
	select @set_qi = lower(@set_qi)
	if (@set_qi not in ('on','off',' '))
		begin
		raiserror('Error, set quoted_identifier %s ?',11,1,@set_qi)
		return(1)
		end

	execute(
	'
	use ' +@dbname+ '
	declare @rc integer
	exec @rc = sp_db_upgrade1
	if @rc <> 0 raiserror(''sp_db_upgrade1 RETURN %d'',18,10,@rc)
	exec @rc = sp_db_upgrade2
	if @rc <> 0 raiserror(''sp_db_upgrade2 RETURN %d'',18,20,@rc)
	')

	execute(
		'
		use ' +@dbname+ '
		declare @rc integer
		exec @rc = sp_db_upgrade3 ''' +@dbname+ ''' , ''' +@set_qi+ '''
		if @rc <> 0 raiserror(''sp_db_upgrade3 RETURN %d'',16,30,@rc)
		')

------------  Finalization  -------------------

	if (@db_orig_status & 1024 <> 0)
		begin
		exec sp_dboption @dbname ,'read only' ,'true'
		end
	if (@db_orig_status & 512 <> 0)
		begin
		exec sp_dboption @dbname ,'offline' ,'true'
		end

	dbcc traceoff(3643)

	--execute('use '+@cur_db)	--In 6.5 (Hydra 6.01.144+) this is essentially a NOP.
	Return (0)
-- End of sp_db_upgrade
go


dump tran master with no_log
go

print ' '
raiserror('... sp_db_upgrade2 , sp_db_upgrade3: master ...',0,1) with nowait

---- We should get master db fully upgraded before starting any other dbs.
execute('use master
		declare @rc int
		exec @rc = sp_db_upgrade2
		if @rc <> 0 RaisERROR(''sp_db_upgrade2 RETURN code = %d, indicating an error!'',18,77) with log
		exec @rc = sp_db_upgrade3
		if @rc <> 0 RaisERROR(''sp_db_upgrade3 RETURN code = %d, indicating an error!'',18,77) with log
	   ')
-- If no error, then hope to proceed to model & tempdb, before touching other dbs.
go


---- Now upgrade model & tempdb.

dump tran model with no_log
go

print ' '
raiserror('... sp_db_upgrade1 , sp_db_upgrade2 , sp_db_upgrade3: model ...',0,1) with nowait

execute('use model
		declare @rc int
		exec @rc = sp_db_upgrade1
		if @rc <> 0 RaisERROR(''Bad rc from sp_db_upgrade1!'',18,75) with log
		exec @rc = sp_db_upgrade2
		if @rc <> 0 RaisERROR(''sp_db_upgrade2 RETURN code = %d, indicating an error!'',18,75) with log
		exec @rc = sp_db_upgrade3
		if @rc <> 0 RaisERROR(''sp_db_upgrade3 RETURN code = %d, indicating an error!'',17,75) with log
	   ')
-- If no error, then sysdatabases.version now =408.
go


dump tran tempdb with no_log
go

print ' '
raiserror('... sp_db_upgrade1 , sp_db_upgrade2 , sp_db_upgrade3: tempdb ...',0,1) with nowait

execute('use tempdb
		declare @rc int
		exec @rc = sp_db_upgrade1
		if @rc <> 0 RaisERROR(''Bad rc from sp_db_upgrade1!'',18,76) with log
		exec @rc = sp_db_upgrade2
		if @rc <> 0 RaisERROR(''sp_db_upgrade2 RETURN code = %d, indicating an error!'',18,76) with log
		exec @rc = sp_db_upgrade3
		if @rc <> 0 RaisERROR(''sp_db_upgrade3 RETURN code = %d, indicating an error!'',17,76) with log
	   ')
-- If no error, then sysdatabases.version now =408.
go


print ''
print 'Checking for databases with old (or midstate) schemas, preparing loop around sp_db_upgrade1,2.'
go

declare @sysdabversion_constant integer
select  @sysdabversion_constant = 1000

declare @sqlservr_dbver int
dbcc getvalue('current_version')
select @sqlservr_dbver = @@error -- Get expected db schema version level from server.

declare c1 insensitive scroll cursor for
    select name from master..sysdatabases
	where (   version <= @sqlservr_dbver  --This is the NORMAL (406<408), unless restarting after abort.
		 OR
                  version >= @sysdabversion_constant
		)
	and    name not in ('master' ,'model' ,'tempdb')
	order by dbid
go

declare	 @dbname varchar(32)
	,@counter int
select	 @counter=0

open c1
fetch first from c1 into @dbname

print ' '
print ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'
print ' - - - - - - - - -  sp_db_upgrade1,2 (~3)  - - - - - - - - -'
print ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'
print ' '

while @@fetch_status >= 0
  begin
	select @counter = @counter + 1
	print ' ' print ' '
	raiserror('(Inside loop exec sp_db_upgrade2, db=%s)',0,1,@dbname)

	execute(
	'
	use ' +@dbname+ '
	declare @rc integer
	exec @rc = sp_db_upgrade1
	if @rc <> 0 raiserror(''Bad rc from sp_db_upgrade1!'',18,91,@rc)
	exec @rc = sp_db_upgrade2
	if @rc <> 0 raiserror(''sp_db_upgrade2 RETURN code = %d, indicating an error!'',18,92,@rc)
	')

	fetch c1 into @dbname	 -- Get next db name.
  end

raiserror('Number of loops to exec sp_db_upgrade2 was %d.',0,1,@counter)
go
deallocate c1		-- No longer need cursor.
go

print ''
print 'Encrypting any existing nonEncrypted passwords (for 6.0 style).'
go
update syslogins set password = pwdencrypt(password), status = status | 8
	where (status & 8) = 0
go

print ''
print 'Setting all existing servers to be ''rpc'' servers.'
go
update sysservers set srvstatus = srvstatus | 1
	where srvid <> 0	/* Don't want to do this to the 'local' server. */
go

/***
--------  Ensure all sysdatabases.version = current
declare @sqlservr_dbver int
dbcc getvalue('current_version')
select @sqlservr_dbver = @@error		-- Get expected db schema version level from server.

declare @sysdab_status_balk integer

select @sysdab_status_balk = (256|512|1024)

if exists (select * from master..sysdatabases where version <> @sqlservr_dbver
            and status & @sysdab_status_balk = 0)
   raiserror('ERROR, some databases have an incorrect sysdatabases.version (should be %d according to sqlservr.exe), aborting!  Rerun this file through isql.exe.'
            ,18,86,@sqlservr_dbver) with log
else
   raiserror('Good, all databases have the current sysdatabases.version = %d.',0,1,@sqlservr_dbver)
***/
go

Dump Transaction master with no_log
Go
Checkpoint
Go

if exists (select * from sysobjects where type='P ' and
           name='sp_MS_upd_sysobj_category')
  drop procedure sp_MS_upd_sysobj_category

raiserror('create procedure sp_MS_upd_sysobj_category ...',0,1)
Go
create procedure sp_MS_upd_sysobj_category  --1996/02/14 14:22:04
       @pSeqMode        integer
      ,@pInitDtTm_vc    character varying(25)   = null  OUTPUT
as
/********1*********2*********3*********4*********5*********6*********7**
      This sproc merely updates sysobjects.category second bit (2)
to ON, signifying this is a MS provided object.

...In the same one connection through isql.exe!...
@pSeqMode = 1:  Use this after bunched drops but before bunched creates.
@pSeqMode = 2:  Use this after bunched creates.
@pSeqMode = 4:  Unneeded, but offers to report/OUTPUT datetime of mode 1.

      A sequence mode parm value of 2 can be combined with an
optional date_time second parm, passed in character format style of
'yyyy/mm/dd hh:mm:ss' exactly.
      But usually we will let this second parm default to null,
and let this proc fill it in from the value previously stored
during the mode 1 run.

      This undocumented internal MS sproc was written to replace
sp_check_objects.
      This sproc is executed twice within one file of GO-delimited
batches, once before the first object create, and once after the
last object create ('allow updates',1 must be in effect).
      No change of db context should occur between the two
executions of this sproc.  Two execution pairs can occur within the
same connection, if GO separated.
*********1*********2*********3*********4*********5*********6*********7*/

set nocount          on
set dateformat       ymd
set xact_abort       off
set ansi_defaults    off
set ansi_nulls       on

declare
       @RetCode            integer
      ,@_rowcount          integer
      ,@CountUpd           integer
      ,@InitDtTm           datetime
      ,@MSBit              integer
      ,@WType              character(2)
      ,@vc_InitDtTm        character varying(35)
      ,@vc_spid            character varying(12)
      ,@OrigDabName        character varying(30)
      ,@Sel_OrigDabName    character varying(30)

select
       @RetCode            = 0  -- 0 is good , >0 is bad
      ,@InitDtTm           = null
      ,@MSBit              = 2
      ,@vc_spid            = convert(varchar,@@spid)
      ,@OrigDabName        = db_name()

print ' '

------------------------ Only SA can run this --------------------

if (suser_id() <> 1)
   begin
   raiserror('Even the SA should NOT run this undoc internal proc, but you definitely should not!',11,1)
   select @RetCode = 1
   goto LABEL_86_RETURN
   end


-------------------------- Edit parms ------------------------

if (@pSeqMode is null)
   begin
   raiserror('Input parm @pSeqMode cannot be null.',11,1)
   select @RetCode = 1
   goto LABEL_86_RETURN
   end

if (@pSeqMode not in (1,2,4))
   begin
   raiserror('Input parm @pSeqMode value is not in the defined legal set.',11,1)
   select @RetCode = 1
   goto LABEL_86_RETURN
   end

if (@pSeqMode in (1,4))
   begin
   if (@pInitDtTm_vc is not null)
      begin
      raiserror('Input parm @pSeqMode value  %d  cannot be passed in with any nonnull InitDtTm parm value.'
               ,11,1,@pSeqMode)
      select @RetCode = 1
      goto LABEL_86_RETURN
      end
   end

if (@pSeqMode = 2)
   begin
   if (@pInitDtTm_vc is not null)
      begin
      select @InitDtTm = convert(datetime,@pInitDtTm_vc)
      end
   end


-------------------------- Branch to proper sequence mode --------------

if (@pSeqMode = 1)
     goto LABEL_1_START

if (@pSeqMode in (2,4))
     goto LABEL_2_START


-------------------------------------------------------------------
--                      Start mode 1
-------------------------------------------------------------------


LABEL_1_START:


---- Maybe try a ##table instead?
EXECUTE(
'
use tempdb
if object_id(''tab1_UpdCat_' +@vc_spid+ ''') is not null
    drop table tab1_UpdCat_' +@vc_spid+ '
')


EXECUTE(
'
use tempdb
create table tab1_UpdCat_' +@vc_spid+ '
   (OrigDabName character varying(30) not null
   ,vc_InitDtTm character varying(35) not null)
')


waitfor delay '00:00:01'

select @InitDtTm = getdate()

select @vc_InitDtTm  =
                                 convert(varchar,@InitDtTm,111) + ' '
                     + substring(convert(varchar,@InitDtTm,114),1,8)

EXECUTE(
'
use tempdb
insert into tab1_UpdCat_' +@vc_spid+ '
      (OrigDabName ,vc_InitDtTm)
   values (''' +@OrigDabName+ ''' ,''' + @vc_InitDtTm + ''')
')

raiserror('Time established during sequence mode 1 before creates is  ''%s'' (spid=%d, db=%s)'
            ,0,1,@vc_InitDtTm,@@spid,@OrigDabName)

waitfor delay '00:00:01'


goto LABEL_86_RETURN


-------------------------------------------------------------------
--                      Start mode 2  (and 4)
-------------------------------------------------------------------


LABEL_2_START:  -- 4 also


if (@InitDtTm is null)
   begin

   create table #tab2_UpdCat
      (OrigDabName character varying(30) not null
      ,vc_InitDtTm character varying(35) not null)

   EXECUTE(
   '
   use tempdb
   set rowcount 1
   insert into #tab2_UpdCat (OrigDabName ,vc_InitDtTm)
      select OrigDabName ,vc_InitDtTm from tab1_UpdCat_' +@vc_spid+ '
   set rowcount 0
   ')

   Select
                 @Sel_OrigDabName = min(OrigDabName)
                ,@InitDtTm        = convert(datetime,min(vc_InitDtTm))
                ,@vc_InitDtTm     = min(vc_InitDtTm)
         from    #tab2_UpdCat

   if (object_id('tempdb..#tab2_UpdCat') is not null)
               drop table #tab2_UpdCat

   if (@Sel_OrigDabName <> @OrigDabName)
      begin
      raiserror('User error, the database selected from the temp table (%s) differs from current database context (%s).'
               ,11,6,@Sel_OrigDabName,@OrigDabName)
      select @RetCode = 1
      end
   end


if (@InitDtTm is null)
   begin
   raiserror('Could not obtain a proper InitDtTm value.',16,1)
   select @RetCode = 1
   goto LABEL_86_RETURN
   end

raiserror('The datetime established earlier during sequence mode 1 was ''%s'' (spid=%d, db=%s)'
            ,0,1,@vc_InitDtTm,@@spid,@OrigDabName)
select @pInitDtTm_vc = @vc_InitDtTm

if (@pSeqMode = 4)
   begin
   goto LABEL_86_RETURN
   end


EXECUTE(
'
use tempdb
drop table tab1_UpdCat_' +@vc_spid+ '
')


--------  Actual updates to sysobjects.category

set rowcount 20  -- Hold down UOW (implicit txn) size.

--------------------------
--select @WType = 'P '

select @CountUpd  = 0
      ,@_rowcount = 1

while (@_rowcount > 0)
   begin
   update       sysobjects  --Limited to current db.
      set       category = category | @MSBit
      where     category & @MSBit = 0
    --and       type       = @WType
      and       crdate     > @InitDtTm

   select @_rowcount = @@rowcount
   select @CountUpd  = @CountUpd + @_rowcount

   EXECUTE('dump transaction ' + @OrigDabName + ' with no_log')
   end

raiserror('The number of objects updated with category | %d was %d. (spid=%d, db=%s)'
         ,0,1,@MSBit,@CountUpd,@@spid,@OrigDabName)

--------------------------

set rowcount 0

goto LABEL_86_RETURN


---------------------- Finalization -----------------------

LABEL_86_RETURN:

print ' '

Return @RetCode

Go

--------  Special case sysobjects.category | 2

update          sysobjects
      set       category = category | 2
      where     category & 2 = 0
      and       name in
			(
	 'sp_MS_upd_sysobj_category'

	,'sp_db_upgrade'
	,'sp_db_upgrade1'
	,'sp_db_upgrade2'
	,'sp_db_upgrade3'

	,'xp_cmdshell'
	,'xp_regread'

	--,'sysarticles'
	--,'sysconstraints'
	--,'syspublications'
	--,'sysreferences'
	--,'syssubscriptions'
			)

Go

raiserror(' ',0,1)
select 'Last ' as 'Last ',version,name,dbid from master..sysdatabases
	order by 2,3
go

Dump Transaction master with no_log
Go
Checkpoint
Go

if exists (select * from sysobjects where name = 'sp_configure'
			and type = 'P ')
	begin
		exec sp_configure 'allow updates',0
		reconfigure with override
		print 'sp_configure & reconfigure_wo just set allow updates Off.'
	end
else
	raiserror('(sp_configure not present to turn Off ''allow updates''.)',0,1)

Go

declare @vc1 varchar(55)
select  @vc1 = convert(varchar,getdate(),113)
raiserror('This file completed successfully at %s' ,0,1 ,@vc1)
go

--dbcc traceoff(3643) --  sp_db_upgrade3  still needs access.
go
-- - -----
