Piper's Pit BBS/FTP: ibm 0430 - 0439/ibm0430-0439/ibm0438.tar/ibm0438/SQL42109.ZIP/SQL421A.009/SQL/I386/DNNT42.SQL

Text File | 1994-07-14 | 11.6 KB | 503 lines

/********************************************************************
** This script file will downgrade SQL Server for Windows NT to
** SQL Server 4.2
********************************************************************/

use master
go

/*
** Make sure we have enough space in master db to
** complete this script
*/
dump tran master with truncate_only
go

execute sp_configure 'update', 1
go

reconfigure with override
go

/*
** Drop NT specific error messages
*/
delete from sysmessages where error between 4027 and 4034
    or error in (114,227,423,1708,3609,5016,5017,7964,7965,7966)
go

/*
** Delete rows where tape messages existed in Beta II version.
*/
if charindex('Mar 22',@@version) <> 0
  begin
    delete from sysmessages where
      error=4390 and description like "Mount first%"
    delete from sysmessages where
      error=4391 and description like "End of tape has%"
    delete from sysmessages where
      error=4392 and description like "Database '%"
    delete from sysmessages where
      error=4393 and description like "Tape '%"
    delete from sysmessages where
      error=4394 and description like "Creation date %"
    delete from sysmessages where
      error=4395 and description like "Cannot find file %"
    delete from sysmessages where
      error=4396 and description like "File <%"
    delete from sysmessages where
      error=4397 and description like "Warning, file <%"
  end
go

/*
** Remove sp_configure parameters for SQL Server/NT and spt_values
**   and to sysconfigures
*/
delete from sysconfigures where config between 501 and 503
go
delete from spt_values where name = 'tempdb in ram' and number = 501
go
delete from spt_values where name = 'max async IO' and number = 502
go
delete from spt_values where name = 'max worker threads' and number = 503
go

dump tran master with truncate_only
go

/* */
/* Drop the NT specific procedures */
/* */
if exists ( select * from sysobjects
        where sysstat & 7 = 4
            and name = 'MS_sqlctrs_users' )
    drop proc MS_sqlctrs_users
go

if exists ( select * from sysobjects
        where sysstat & 7 = 4
            and name = 'MS_sqlctrs_locks' )
    drop proc MS_sqlctrs_locks
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_logininfo')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_logininfo'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_logininfo'
         and sysobjects.sysstat & 7 = 4

    end
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_loginconfig')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_loginconfig'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_loginconfig'
         and sysobjects.sysstat & 7 = 4

    end
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_grantlogin')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_grantlogin'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_grantlogin'
         and sysobjects.sysstat & 7 = 4

    end
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_revokelogin')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_revokelogin'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_revokelogin'
         and sysobjects.sysstat & 7 = 4

    end
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_cmdshell')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_cmdshell'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_cmdshell'
         and sysobjects.sysstat & 7 = 4

    end
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_logevent')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_logevent'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_logevent'
         and sysobjects.sysstat & 7 = 4

    end
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_enumgroups')
    begin

    delete syscomments
       from syscomments,sysobjects
       where syscomments.id = sysobjects.id
         and sysobjects.name = 'xp_enumgroups'
         and sysobjects.sysstat & 7 = 4

    delete from sysobjects
       where sysobjects.name = 'xp_enumgroups'
         and sysobjects.sysstat & 7 = 4

    end
go

dump tran master with truncate_only
go

if exists (select *
    from sysobjects
        where sysstat & 7 = 4
            and name = 'sp_addextendedproc')
begin
    drop procedure sp_addextendedproc
end
go

if exists (select *
    from sysobjects
        where sysstat & 7 = 4
            and name = 'sp_dropextendedproc')
begin
    drop procedure sp_dropextendedproc
end
go

if exists (select *
    from sysobjects
        where sysstat & 7 = 4
            and name = 'sp_helpextendedproc')
begin
    drop procedure sp_helpextendedproc
end
go

/*
**  Drop NT specific version of sp_configure.
*/
if exists (select *
    from sysobjects
        where sysstat & 7 = 4
            and name = 'sp_configure')
begin
    drop procedure sp_configure
end
go

/* */
/* Change the operating system version back to OS/2*/
/* */

update spt_values set name = "OS/2"
where name = "WINDOWS/NT"
go
update spt_values set high = 32000
where name = "memory"
go

/*
** Recreate OS/2 specific version of sp_configure.
*/
create procedure sp_configure
@configname varchar(20) = NULL,        /* option name to configure */
@configvalue int = NULL            /* configure value */
as

declare @confignum int            /* number of option to be configured */
declare @configcount int        /* number of options like @configname */

/*
**  If no option name is given, the procedure will just print out all the
**  options and their values.
*/
if @configname is NULL
begin
    select name, minimum = low, maximum = high,
        config_value = sysconfigures.value,
        run_value = syscurconfigs.value
    from spt_values, sysconfigures, syscurconfigs
        where type = "C"
            and number *= sysconfigures.config
            and number *= syscurconfigs.config
            and number >= 0

    return (0)
end

set nocount on

/*
**  Use @configname and try to find the right option.
**  If there isn't just one, print appropriate diagnostics and return.
*/
select @configcount = count(*)
        from spt_values
        where name like "%" + @configname + "%" and type = "C"
            and number >= 0

/*
**  If no option, show the user what the options are.
*/
if @configcount = 0
begin
    print "Configuration option doesn't exist."

    /*
    ** Show the user what the options are.
    */
    select name, minimum = low, maximum = high,
        config_value = sysconfigures.value,
        run_value = syscurconfigs.value
    from spt_values, sysconfigures, syscurconfigs
        where type = "C"
            and number *= sysconfigures.config
            and number *= syscurconfigs.config
            and number >= 0
    return (0)
end

/*
**  If more than one option like @configname, show the duplicates and return.
*/
if @configcount > 1
begin
    print "Configuration option is not unique."

    select duplicate_options = name
        from spt_values
            where name like "%" + @configname + "%"
                and type = "C"
                and number >= 0

    return (1)
end

/*
** If @configvalue is NULL, just show the current state of the option.
*/
if @configvalue = NULL
begin
    select name, minimum = low, maximum = high,
        config_value = sysconfigures.value,
        run_value = syscurconfigs.value
    from spt_values, sysconfigures, syscurconfigs
        where type = "C"
            and number *= sysconfigures.config
            and number *= syscurconfigs.config
            and name like "%" + @configname + "%"
            and number >= 0
    return (0)
end

/*
**  Only the SA can execute the update part of this procedure so check.
*/
if suser_id() != 1
begin
    print "Only the System Administrator (SA) may change configuration parameters."
    return (1)
end

/*
**  Now get the configuration number.
*/
select @confignum = number
    from spt_values
        where type = "C" and (@configvalue between low and high
            or @configvalue = 0)
            and name like "%" + @configname + "%"
            and number >= 0


/*
**  If this is the number of devices configuration parameter,
**  we want to make sure that it's not being set to lower than the
**  number of devices in sysdevices.
*/
if @confignum = 116
begin
    /*
    **  Get the max vdevno.
    */
    declare @maxvdevno    int
    select @maxvdevno = max(convert(tinyint, substring(convert(binary(4),
        d.low), v.low, 1)))
    from master.dbo.sysdevices d, master.dbo.spt_values v
        where v.type = "E"
        and v.number = 3

    /*
    **  If @configvalue == 0, then set it to the current number of devices.
    */
    if @configvalue = 0
    select @configvalue = @maxvdevno + 1
    if (select low
        from master.dbo.spt_values
            where type = "C"
                and number = 116) > @configvalue
    begin
        select @configvalue = low
            from master.dbo.spt_values
                where type = "C"
                    and number = 116
    end

    if ((@maxvdevno + 1) > @configvalue)
    begin
        print "You can't set the number of devices to be less than the number of devices already defined in sysdevices."
        return (1)
    end
end

/*
**  If this is the number of default language, we want to make sure
**  that the new value is a valid language id in Syslanguages.
*/
if @confignum = 124
begin
    if not exists (select *
        from syslanguages
            where langid = @configvalue)
    begin
        /* 0 is Sybase default language, us_english */
        if @configvalue != 0
        begin
            print "You can't set the default language to a language ID that is not defined in Syslanguages."
            return (1)
        end
    end
end

/*
**  If this is the number of kernel language, we want to make sure
**  that the new value is a valid language id in Syslanguages.
*/
if @confignum = 132
begin
    if not exists (select *
        from syslanguages
            where langid = @configvalue)
    begin
        /* 0 is Sybase default language, us_english */
        if @configvalue != 0
        begin
            print "You can't set the kernel language to a language ID that is not defined in Syslanguages."
            return (1)
        end
    end
end
/*
**  Although the @configname is good, @configvalue wasn't in range.
*/
if @confignum is NULL
begin
    print "Configuration option value is not legal."
    return (1)
end

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
if @@trancount > 0
begin
    print "Can't run sp_configure from within a transaction."
    return (1)
end

/*
**  Now update sysconfigure.
*/
update sysconfigures
    set value = @configvalue
        where config = @confignum

print "Configuration option changed. Run the RECONFIGURE command to install."

return (0)
go
grant execute on sp_configure to public
go
dump transaction master with no_log
go

/*
** Set max connections back to OS/2 value if necessary
*/
if (select high from spt_values where name = 'user connections') > 256
   update spt_values set high = 256 where name = 'user connections'
go

execute sp_configure 'update', 0
go
reconfigure with override
go

dump tran master with truncate_only
go