Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/i386/upgrade1.sql

Text File | 1995-12-13 | 41.0 KB | 1,482 lines

-- - -----
/*
** Upgrade1.SQL        1995/12/13 10:55
**
** (From old 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.
*/

go
use master
go
dump tran master with no_log
go
set nocount on
go
declare @dbver int
dbcc getvalue('current_version')
select @dbver = @@error        -- Get expected db schema version level from server.

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

    if (select version from sysdatabases where dbid = db_id('master')) = @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.)',1,1)
    end
else
    raiserror('(sp_configure does not now exist, so it cannot be used to turn on ''allow updates''. Thus -m expected.)',1,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.',20,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' ,1,1 ,@vc1)
go

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

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

raiserror(' ',1,1)
dbcc useroptions

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 sysdatabases where dbid = 1) < 400)
    or @free_db_space < 2048    -- post 4.2x upgrade.

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

    if (select version from 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)
            exec(@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))',1,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.))

    exec(@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


if exists (select * from sysdatabases where name='master' and version between 400 and 406)
    begin
    raiserror('Dropping proc sp_dbcoalesce (6.0 name)....',1,1)
    drop procedure sp_dbcoalesce
    end
go

/*
** Add xp_regread if necessary.
*/
if not exists (select * from sysobjects where name = 'xp_regread')
    begin
    raiserror('(addextendedproc a1)',1,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

/******************************
sp_makewebpage must fit in 6.5 model db.  model db is normally 2 Mb.


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.dbo.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 'Updating sysdatabases, adding column  ''category''.'
        alter table sysdatabases add category int null
    end
else
        raiserror('sysdatabases already has ''category'' column.',1,1)
go

print ''
print 'Initializing sydatabases.category.'
update sysdatabases set category = 0 where category is null --better in exec() immed after alter?
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''.',1,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''.',1,1)
go

/* Set creation date for master and model databases. */
print ''
print 'Setting creation date for master and model databases.'
go
update 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 sysstat & 0xf = 4)
    begin
        raiserror('Dropping    sp_db_upgrade1...',1,1)
            drop procedure sp_db_upgrade1
    end
else
        raiserror('proc sp_db_upgrade1 does not pre-exist.',1,1)
go
if exists (select * from sysobjects
            where name = 'sp_db_upgrade2'
            and sysstat & 0xf = 4)
    begin
        raiserror('Dropping    sp_db_upgrade2...',1,1)
            drop procedure sp_db_upgrade2
    end
else
        raiserror('proc sp_db_upgrade2 does not pre-exist.',1,1)
go

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

go
print ''
print 'Creating procedure sp_db_upgrade1.'
go
create procedure sp_db_upgrade1 as
--Makes SQL 6.0 specific schema changes to the current database.

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

    select @curdbname = db_name()
    select @dumptran = 'dump tran '+db_name()+' with no_log'

    raiserror('

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


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

    exec(@dumptran)

    /* Make changes to sysobjects to avoid keyword conflict. */
    print 'Section for updating sysindexes (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.',1,1,@curdbname)
        exec('update syscolumns set name=''schema_ver''
            where id=object_id(''sysobjects'') and name = ''schema''')
        end

    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.',1,1,@curdbname)
        exec('update sysobjects set schema_ver = schema_ver+1
                where id = object_id(''sysobjects'')')
        end


    /* Make changes to sysindexes for enhanced fillfactor support. */
    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.',1,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'
        end
    else
        print 'syscolumns row for sysindexes OrigFillFactor already Updated.'

    if @@error<>0
        begin
            print 'Could not successfully upgrade table ''sysindexes'' (OFFactor).'
            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

            exec('update sysobjects set schema_ver = schema_ver + 1
                where id=object_id(''sysindexes'')')
        end

        /* Convert unused column sysobjects.expdate to sysobjects.version */
    print 'Converting sysobjects.expdate to sysobjects.version.'
    update syscolumns
        set name='version', type=56, usertype=7, length=4
        where id=object_id('sysobjects')
            and name = 'expdate'

    if @@error<>0
        begin
            print 'Could not successfully upgrade table ''sysobjects'' (syscolumns.name=expdate-->version).'
            return(1)
        end
    else
        begin
            print '''Sysobjects.expdate'' successfully converted (increment schema_ver).'
            exec('update sysobjects set schema_ver = schema_ver + 1
                where id=object_id(''sysobjects'')')
        end

    /* Create table to support DRI. */
    print 'Checking to see if ''sysreferences'' exists.'
    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.',1,1)

    /* Create new table to support DRI. */
    print 'Checking to see if ''sysconstraints'' exists.'
    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.',1,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.',1,1)

    /*
    ** Add precision and scale columns to systypes and syscolumns
    ** if necessary.
    */
    print 'Checking ''systypes'' for prec and scale columns.'
    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''.',1,1)

    exec(@dumptran)

    print 'Checking ''syscolumns'' for prec and scale columns.'
    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''.',1,1)


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


    print 'Checking ''sysprotects'' for grantor column.'

    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''.',1,1)


    ------------------------  dbcc, extracting syscomments.text  ---------

    exec(@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.'

    exec(@dumptran)

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

    /*
    ** Set flag to indicate that database schema has been updated
    ** and is ready for data initialization.
    */
    update master..sysdatabases set version = 32767 where name=db_name()

    raiserror('Ending sp_db_upgrade1.  Database %s successfully upgraded, now needs initializing (version set=32767).'
            ,1,1,@curdbname)
    print ' '  --resets @@error=0, from 5000
    return(0)
go
dump tran master with no_log
go

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


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

delete from syscolumns where id = 32 and colid > 5
go

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
go

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


print ''
print 'Checking for databases with old schemas.'
go
/*
** Upgrade system tables in each database created with an old schema.
*/
declare @dbver int
dbcc getvalue('current_version')
select @dbver = @@error        -- Get expected db schema version level from server.

declare c1 scroll cursor for select name from master..sysdatabases
    where version < @dbver
go

declare @dbname varchar(30)
open c1
fetch c1 into @dbname
while @@fetch_status >= 0    -- make sure a valid row was fetched
    begin
    raiserror('(Inside loop exec sp_db_upgrade1, db=%s)',1,1,@dbname)

    exec('use '+@dbname+'
        declare @rc int
        exec @rc = sp_db_upgrade1
        if @rc <> 0 RaisERROR(''Bad rc from sp_db_upgrade1!  Breaking connection.''
                ,22,127)
       ')

    fetch c1 into @dbname    /* Get next db name. */
    end
go

use master
go

declare @curdbname varchar(30) select @curdbname=db_name()
raiserror('
After USE MASTER above, current db of context is:  %s.',1,1,@curdbname)
go

/*
** Had to add new columns before referencing them in declaration
** of procedure that initializes existing rows and adds new ones.
*/
print ''
print '
Creating procedure sp_db_upgrade2.'
go
create procedure sp_db_upgrade2 as
    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)
    declare @counter int

    select  @curdbname = db_name()  ,@counter=0
    select @dumptran = 'dump tran '+db_name()+' with no_log'

    raiserror('

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

    if (select version from master..sysdatabases where name = db_name()) <> 32767
        begin
            select @pmsg=
            'Cannot initialize '''+db_name()+''' database until schema has been modified.'
            print @pmsg
            print 'Please correct upgrade errors and then use sp_db_upgrade <dbname> to complete the upgrade process for this database.'
            return(1)
        end

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

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

    exec(@dumptran)

    update sysobjects set crdate = getdate()
        where id < 100 and crdate like '%1900%'

    print 'Converting timestamp datatype to fixed length.'
        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 = 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
            )
    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 = 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.',1,1)
        end
    else
        raiserror('systypes already has rows for exact numerics.',1,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)

    exec(@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
    exec('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
                    where current of c2
            fetch c2 into @id
        end

    deallocate c2

    raiserror('Number of sysindexes.OrigFillFactor cells Upd to 0 was %d',1,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, so fix.',1,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 nulls & new index initialized.'
        end
    else
        raiserror('All sysprotects.grantor cells are already nonNull.',1,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.'

    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.',1,1)

    exec(@dumptran)

    print 'Checking to see if ''syspublications'' exists.'

    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.',1,1)

    exec(@dumptran)

    /*
    ** Set sysdatabases.version to indicate that db has been upgraded.
    */
        declare @dbver int
    dbcc getvalue('current_version')
    select @dbver = @@error        -- Get expected db schema version level from server.

    update sysdatabases set version = @dbver where name=db_name()

    raiserror('Ending sp_db_upgrade2.  Database %s was successfully initialized, version set=%d.'
            ,1,1,@curdbname,@dbver)
    return(0)
go

if exists (select * from master.dbo.sysobjects where name='sp_db_upgrade2')
    begin
    raiserror('(sp_db_upgrade2 exists in master, as it should.)',1,1) with nowait
    end
else
    begin
    raiserror('(sp_db_upgrade2 does NOT exists in master!)',11,1) with nowait
    end
go

declare     @dbname varchar(32)
    ,@counter int
select     @counter=0
fetch first from c1 into @dbname

while @@fetch_status >= 0
  begin
    select @counter = @counter + 1

    exec('use '+@dbname+' exec sp_db_upgrade2')
    fetch c1 into @dbname        -- Get next db name.
  end

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

use master        -- Safety measure, no longer needed (6.01.144).
go

dump tran master with no_log
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 sysstat & 0xf = 4)
    begin
        raiserror('Dropping    sp_db_upgrade...',1,1)
            drop procedure sp_db_upgrade
    end
else
    raiserror('sp_db_upgrade does not pre-exist.',1,1)
go

dbcc traceoff(3643)    -- Disable access to databases with old schema.
go

create procedure sp_db_upgrade
    @dbname varchar(30) = null
as
    declare @cur_db varchar(30)
    select @cur_db=db_name()    /* Keep track of which db we started in. */

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

    dbcc traceon(3643)    /* Enables us to 'use' a database with an 'outdated' schema. */
    exec('use '+@dbname+' exec sp_db_upgrade1 exec sp_db_upgrade2')
    dbcc traceoff(3643)

    exec('use '+@cur_db)    --In 6.01.144+ this is essentially a NOP.
go

print ''
print 'Updating sysrefences.'
go

declare @counter int select @counter=0
declare @dbname varchar(30)
declare sysrefupdcur cursor for select name from sysdatabases

open sysrefupdcur

fetch sysrefupdcur into @dbname

while @@fetch_status >= 0
    begin
        select @counter = @counter + 1

        exec('use '+@dbname + ' ' +
             'update sysreferences set
                fkeydbid = 0,
                rkeydbid = 0
            where  fkeydbid > 0
                or rkeydbid > 0'
                    )
        fetch sysrefupdcur into @dbname
    end

deallocate sysrefupdcur

raiserror('Number of loops to Upd sysreferences._keydbid to 0 was %d.',1,1,@counter)
go

use master
go

print ''
print 'Decrypting passwords encrypted by prior builds for upgrade.'
go
update syslogins set password = olddecrypt(password), status = status & (~4)
    where (status & 4) = 4
go

print ''
print 'Encrypting existing passwords.'
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

--exec sp_coalesce_fragments '%'  --easy chance to simplify legacy sysusages
go


use master
go


-- - ---------------------------------------------------------------------------------
go
dump transaction model with no_log
go

---- 6.5 Beta (only) needs db=model at least 2 Mb large, for sp_makewebpage.
----    (Thus no 1.44Mb floppy RemovableMedia DB's.)

---- IF db=model is already at least 2 Mb big,
----    THEN most of this section's logic will be skipped.

declare
     @Int1            integer
    ,@Int2            integer
    ,@Num1            numeric(21,1)
    ,@vc1            varchar(33)
declare
     @2KbPagesModel        numeric(21,1)
    ,@2KbPer1Mb        numeric(21,1)
    ,@NumMbFinalForModel    numeric(21,1)
    ,@dbid_model        integer
    ,@AddToModel2Kb        numeric(21,1)
    ,@RawAddToMasterDV2Kb    numeric(21,1)
declare
     @Reserved        numeric(21,1)
declare
     @DVSize2Kb        numeric(21,1)
    ,@DVConsumed2Kb        numeric(21,1)

select
     @2KbPer1Mb        = 512.0 -- 512 2Kb pages per 1 Mb
    ,@NumMbFinalForModel    = 2.0

select @dbid_model = db_id('model') ,@Int1 = @NumMbFinalForModel
select @Int2 = @Int1 * @2KbPer1Mb

raiserror('

db=model (dbid=%d) might need to be increased to a new total size of %d Mb (=%d 2Kb pages)....
',1,1 ,@dbid_model,@Int1,@Int2)

--raiserror('
--(This logic ASSUMES there is enough unused space inside db=model for NewInstall/Upgrade
--activities, if db=model is already at least 2 Mb big.)
--
--',1,1)


select 'Before',ug.* from sysusages ug ,sysdevices dv
        where dv.name='master' and ug.vstart between dv.low and dv.high order by ug.vstart
select 'Before',dv.* from sysdevices dv where dv.name='master'


---- Get the current size of db=model.


select
     @2KbPagesModel        = sum(ug.size)
  from
     master.dbo.sysusages        ug
    ,master.dbo.sysdevices        dv
    ,master.dbo.sysdatabases    db
  where
     ug.vstart between dv.low and dv.high
  and     db.dbid    = ug.dbid
  and     db.name    = 'model'
  and     dv.cntrltype    = 0

select @Int1 = @2KbPagesModel
raiserror('
The ''before'' size of db=model, in 2Kb pages, is %d.

',1,1 ,@Int1)


---- Get the number of Reserved pages inside db=model.


select @Reserved = sum(reserved) from model.dbo.sysindexes where indid in (0,1,255)

select @Int1 = convert(integer,@Reserved)
raiserror('In db=model there are %d pages Reserved.',1,1 ,@Int1)


---- Skip the rest of this section?


if (@2KbPagesModel / @2KbPer1Mb >= @NumMbFinalForModel)  -- So far no action needed.
    begin

    select @Int1 = convert(integer,@NumMbFinalForModel)
    raiserror('db=model is already at least %d Mb large.',1,1 ,@Int1)

    if (@2KbPagesModel - @Reserved  >= @2KbPer1Mb / 2.0)  -- So far no action needed.
        begin
        raiserror('Sufficient UnReserved space available in db=model.',1,1)

        raiserror('Skipping over most of this db=model upsizing section, no action needed.',1,1)

        goto LABEL_153MODELBIGENOUGH

        end

    raiserror('Due to extra user objects or data, db=model will be increased in size by 1 Mb.',1,1)

    select @NumMbFinalForModel = @NumMbFinalForModel + 1
    end
else
    begin
    raiserror('db=model does need to be increased to a new total size.  Upgrade will now do this....',1,1)
    raiserror('(If a Disk Resize is performed, it will try to bring the device size to end on a 1Mb boundary.)',1,1)
    raiserror('(Consider manually running ''sp_coalesce_fragments'' any time after this SETUP completes.)',1,1)
    end


---- Still here, so calculate additional space needed for db=model.


select @AddToModel2Kb = (@NumMbFinalForModel * @2KbPer1Mb) - @2KbPagesModel


---- Get the current amount of unclaimed space on dv=master.


select
     @DVSize2Kb        = (dv.high - dv.low + 1)
    ,@DVConsumed2Kb        = sum(ug.size)
  from
     master.dbo.sysusages        ug
    ,master.dbo.sysdevices        dv
  where
     ug.vstart between dv.low and dv.high
  and     dv.name        = 'master'
  group by
     (dv.high - dv.low + 1)


raiserror('
(Notice-  The first server fragment starts at vstart=4 not =0.)',1,1)
-- Thus 4+252 pages are unreachable (essentially "claimed"), because Alter behaves in 0.5 Mb units.
select @DVConsumed2Kb = @DVConsumed2Kb + 4 + 252


select @Int1 = @DVSize2Kb ,@Int2 = @DVConsumed2Kb
raiserror('
The ''before'' size of dv=master, in 2Kb pages, is %d.  Of that, the amount already claimed is %d.

',1,1 ,@Int1,@Int2)


---- If necessary, increase the size of dv=master.


select @RawAddToMasterDV2Kb = @AddToModel2Kb - (@DVSize2Kb-@DVConsumed2Kb)


if @RawAddToMasterDV2Kb > 0
    begin

    -- Num of whole 1Mb chunks needed for dv=master, before accounting for fraction.
    select @Int1 =      convert(integer,@RawAddToMasterDV2Kb)
            / convert(integer,@2KbPer1Mb) -- truncates fraction


    -- If any fraction, then round up by just saying 1 more 1Mb chunk.
    select @Int2 =      convert(integer,@RawAddToMasterDV2Kb)
            % convert(integer,@2KbPer1Mb) -- gets only fraction
    if @Int2 > 0
        select @Int1 = @Int1 + 1  -- 1 more 1Mb chunk


    -- Get character format of new total size for dv=master.
    select @Int2 = (@Int1 * convert(integer,@2KbPer1Mb))
            +
            convert(integer,@DVSize2Kb)

    select @vc1 = convert(varchar,@Int2)


    raiserror('Will now attempt Disk Resize = %s 2Kb pages total' ,1,1 ,@vc1)

    execute('disk resize name=''master'',size=' + @vc1)

    if @@error <> 0
        begin
        raiserror('Warning bsdf3117u, dv=master could not be increased.',2,2)

        goto LABEL_153MODELBIGENOUGH

--        raiserror('Error bsdf3117n, dv=master could not be increased to a new total size!'
--            ,22,127) with log -- kills spid
        end

    end


---- Finally, increase size of db=model.


-- Num of whole 1Mb chunks needed for db=model, before accounting for fraction.
select @Int1 =      convert(integer,@AddToModel2Kb)
        / convert(integer,@2KbPer1Mb) -- truncates fraction


-- If any fraction, then round up by just saying 1 more 1Mb chunk.
select @Int2 =      convert(integer,@AddToModel2Kb)
        % convert(integer,@2KbPer1Mb) -- gets only fraction
if @Int2 > 0
    select @Int1 = @Int1 + 1  -- 1 more 1Mb chunk


-- Get character format of additional Mb for db=model.
select @vc1 = convert(varchar,@Int1)


raiserror('
Will now attempt alter = %s 1Mb units additional.

',1,1 ,@vc1)

execute('alter database model on master = ' + @vc1)

if @@error <> 0
    begin
    raiserror('Warning bsdf4285y, db=model could not be increased.',2,2)

    goto LABEL_153MODELBIGENOUGH

--    raiserror('Error bsdf4285p db=model could not be increased!'
--            ,22,127) with log -- kills spid
    end


select 'After_',ug.* from sysusages ug ,sysdevices dv
        where dv.name='master' and ug.vstart between dv.low and dv.high order by ug.vstart
select 'After_',dv.* from sysdevices dv where dv.name='master'



LABEL_153MODELBIGENOUGH:


raiserror('
Now done with section which considers increasing size of db=model.

',1,1)

go
-- - - - -

use master
go

if exists (select * from sysobjects where name = 'sp_configure'
            and sysstat & 0xf = 4)
    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''.)',1,1)
go

dump tran master with no_log
go
checkpoint
go


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

go
-- - -----