Piper's Pit BBS/FTP: ibm 0430 - 0439/ibm0430-0439/ibm0438.tar/ibm0438/SQL42110.ZIP/SQL421A.010/SQL/I386/INSTNT.SQ@/INSTNT.SQ@

Text File | 1994-07-14 | 12.0 KB | 479 lines

/*******************************************************************
**
**  This script file adds the WindowsNT specific settings and
**  procedures.  It should be run after instmstr.sql has been run.
**
********************************************************************/

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 procedures that will be created by this script
*/

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

dump tran master with truncate_only
go

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

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

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

dump tran master with truncate_only
go

/*
** Delete messages that will be added by this script
*/
delete from sysmessages
    where error in (114,227,423,1708,3609,5016,5017,7964,7965,7966,8113)
go
delete from sysmessages where error between 4027 and 4034
go

/*
** Add NT specific error messages.
*/
insert into sysmessages
   values (114, 15, 2, "Browse mode is invalid for a statement that assigns values to a variable", NULL)
go
insert into sysmessages
   values (227, 16, 2, "Maximum number of vector aggregates exceeded (%d max, %d found).", NULL)
go
insert into sysmessages
   values (423, 16, 2, "Too many substitution nodes in worktable. (MAX: %d, ACTUAL: %d). Try splitting query or limiting SELECT list.", NULL)
go
insert into sysmessages
   values (1708, 15, 2, "The total row size, %d, for table '%.*s' exceeds the maximum number of bytes per row, %d.", NULL)
go
insert into sysmessages
   values (3609, 10, 2, "Attempt to update a column in the fabricated row of an inner table in an outer join.", NULL)
go
insert into sysmessages
   values (5016, 16, 2, "Database TEMPDB cannot be altered when in RAM.  Remove from RAM with 'sp_configure', stop\restart SQL Server, perform 'ALTER DATABASE', place in RAM with 'sp_configure', stop\restart SQL Server.", NULL)
go
insert into sysmessages
   values (5017, 16, 2, "ALTER DATABASE failed.  Database %.*s not created with 'for load' option.", NULL)
go
insert into sysmessages
   values (7964, 16, 2, "Allocation page SDES not open.", NULL)
go
insert into sysmessages
   values (7965, 16, 2, "Page is not an allocation page.", NULL)
go
insert into sysmessages
   values (7966, 10, 2, "WARNING: NO_INDEX option of '%.*s' being used, checks on non-system indexes will be skipped", NULL)
go
insert into sysmessages
   values (8113,16,1,"Unable to recompile '%.*s', '%s %.*s' is illegal in an open transaction.", NULL)
go

/*
** These are new messages for tape devices
** Messages 4027 - 4034
*/
insert into sysmessages
   values (4027, 16, 2, "Mount first tape for %s of database '%s'.", NULL)
insert into sysmessages
   values (4028, 10, 2, "End of tape has been reached, remove tape '%s' and mount next tape for %s of database '%s'.", NULL)
insert into sysmessages
   values (4029, 10, 2, "Database '%s'%s(%d pages) dumped to file <%d> on tape '%s'.", NULL)
insert into sysmessages
   values (4030, 16, 2, "Tape '%s' expires on day '%s' year '%s' and cannot be overwritten.", NULL)
insert into sysmessages
   values (4031, 16, 2, "Creation date on tape '%s'(%s) does not match that of first volume(%s).", NULL)
insert into sysmessages
   values (4032, 16, 2, "Cannot find file %d on tape '%s'.", NULL)
insert into sysmessages
   values (4033, 16, 2, "File <%d> on tape '%s' is not a SQL Server %s dump.", NULL)
insert into sysmessages
   values (4034, 16, 2, "Warning, file <%d> on tape '%s' was dumped from database '%s'.", NULL)
go

/*
** Change the operating system version, user connections and memory
** parameter to the NT specific values.
*/
update spt_values set name = "WINDOWS/NT"
where name = "OS/2"
update spt_values set high = 1048576
where name = "memory"
update spt_values set high = @@max_connections
where name = "user connections"
go

/*
** Add new sp_configure parameters for SQL Server/NT to spt_values
** and sysconfigures if they don't already exist.
*/

if not exists (select * from sysconfigures where config = 501)
    insert sysconfigures values(501,  0, "TempDB in RAM option",0)
if not exists (select * from spt_values where
            number = 501 and type = 'C' and name = 'tempdb in ram')
    insert spt_values(name, number, type, low, high)
        values ("tempdb in ram", 501, 'C', 0, 1)
go

if not exists (select * from sysconfigures where config = 502)
    insert sysconfigures values(502,  8, "Maximum outstanding async IOs",0)
if not exists (select * from spt_values where
            number = 502 and type = 'C' and name = 'max async IO')
    insert spt_values(name, number, type, low, high)
        values ("max async IO", 502, 'C', 1, 50)
go

if not exists (select * from sysconfigures where config = 503)
    insert sysconfigures values(503,255, "Maximum worker threads",0)
if not exists (select * from spt_values where
            number = 503 and type = 'C' and name = 'max worker threads')
    insert spt_values(name, number, type, low, high)
        values ("max worker threads", 503, 'C', 10, 1024)
go

dump tran master with truncate_only
go

/*
** addextendedproc
*/
create procedure sp_addextendedproc
@functname varchar(30),        /* name of function to call */
@dllname varchar(255)        /* name of DLL containing function */
as
/*
**  If we're in a transaction, disallow the addition of the
**  extended stored procedure.
*/
if @@trancount > 0
begin
    print "Can't run sp_addextendedproc from within a transaction."
    return (1)
end

/*
**  You must be SA to execute this sproc.
*/
if suser_id() != 1
begin
    print "You must be the System Administrator (SA) to execute this procedure."
    return (1)
end

/*
** Create the extended procedure mapping.
*/
dbcc addextendedproc( @functname, @dllname)
return (0)
go

dump tran master with truncate_only
go

/*
** dropextendedproc
*/
create procedure sp_dropextendedproc
@functname varchar(30)        /* name of function */
as
/*
**  If we're in a transaction, disallow the dropping of the
**  extended stored procedure.
*/
if @@trancount > 0
begin
    print "Can't run sp_dropextendedproc from within a transaction."
    return (1)
end

/*
**  You must be SA to execute this sproc.
*/
if suser_id() != 1
begin
    print "You must be the System Administrator (SA) to execute this procedure."
    return (1)
end

/*
** Drop the extended procedure mapping.
*/
dbcc dropextendedproc( @functname )
return (0)
go

dump tran master with truncate_only
go

/*
** Drop extended procs. if they already exist now that sp_dropextendedproc
** has be created.
*/
if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_logininfo')
    exec sp_dropextendedproc 'xp_logininfo'
go

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

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

dump tran master with truncate_only
go

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

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

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

dump tran master with truncate_only
go

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

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_startmail')
    exec sp_dropextendedproc 'xp_startmail'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_stopmail')
    exec sp_dropextendedproc 'xp_stopmail'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_sendmail')
    exec sp_dropextendedproc 'xp_sendmail'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_mailproclist')
    exec sp_dropextendedproc 'xp_mailproclist'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_loginmail')
    exec sp_dropextendedproc 'xp_loginmail'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_prepmsg')
    exec sp_dropextendedproc 'xp_prepmsg'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_addmsgline')
    exec sp_dropextendedproc 'xp_addmsgline'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_addattach')
    exec sp_dropextendedproc 'xp_addattach'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_sendmsg')
    exec sp_dropextendedproc 'xp_sendmsg'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_logoffmail')
    exec sp_dropextendedproc 'xp_logoffmail'
go

if exists (select * from sysobjects
        where sysstat & 7 = 4
            and name = 'xp_msver')
    exec sp_dropextendedproc 'xp_msver'
go

/*
** helpextendedproc
*/

create procedure sp_helpextendedproc
@funcname varchar(30) = NULL
as

set nocount on

/*
**  If no function name given, get 'em all.
*/
if @funcname = null
    select @funcname = "%"

/*
**  Make sure the function name exists
*/
if not exists (select *
    from master.dbo.sysobjects
        where type = "X "
          and name like @funcname)
begin
    print "No such extended procedure name exists."
    return (1)
end

/*
**  Now print out the function name info
*/
select distinct name = master.dbo.sysobjects.name,
        dll = substring(master.dbo.syscomments.text,1,30)
            from master.dbo.sysobjects, master.dbo.syscomments
        where master.dbo.sysobjects.id = master.dbo.syscomments.id
          and master.dbo.sysobjects.name like @funcname
          and master.dbo.sysobjects.type = "X "
        order by master.dbo.sysobjects.name

return (0)
go

grant execute on sp_helpextendedproc to public
go

/*
** Add xp_cmdshell extended procedure
*/
sp_addextendedproc 'xp_cmdshell','xpsql.dll'
go

/*
** Add xp_logevent extended procedure
*/
sp_addextendedproc 'xp_logevent','xpsql.dll'
go

dump tran master with truncate_only
go

/*
** Add xp_logevent extended procedure
*/
sp_addextendedproc 'xp_msver','xpsql.dll'
go

/*
** Add xp_enumgroups extended procedure
*/
sp_addextendedproc 'xp_enumgroups','xplogin.dll'
go

/*
** Add mail enabling extended procedures
*/
sp_addextendedproc 'xp_startmail', 'sqlmapi.dll'
go
sp_addextendedproc 'xp_stopmail', 'sqlmapi.dll'
go
sp_addextendedproc 'xp_sendmail', 'sqlmapi.dll'
go

dump tran master with truncate_only
go

/* Script file to add extended stored procedures for NT integrated login
** security.
*/

sp_addextendedproc 'xp_loginconfig', 'xplogin.dll'
go
sp_addextendedproc 'xp_logininfo', 'xplogin.dll'
go
sp_addextendedproc 'xp_revokelogin', 'xplogin.dll'
go
sp_addextendedproc 'xp_grantlogin', 'xplogin.dll'
go

create proc MS_sqlctrs_users
as
begin
select distinct sysprocesses.spid, memusage 'Memory (2K Pages)', cpu 'CPU time',
       physical_io,count(syslocks.spid) 'Locks held'
from sysprocesses, syslocks where sysprocesses.spid *= syslocks.spid
group by sysprocesses.spid
end
go
grant execute on MS_sqlctrs_users to public
go

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

dump tran master with truncate_only
go