Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/ppc/instsupl.sql

Text File | 1995-12-01 | 6.6 KB | 310 lines

/*
** InstSupl.SQL         1995/11/30 14:25
*/

Go
use master
set nocount on
Go

/*
** Allow ad-hoc updates on system tables
*/
sp_configure 'allow updates',1
Go
reconfigure with override
Go
raiserror('reconfigure- was just run.',1,1)

raiserror('Dropping to-be-created procs if they already exist....',1,1)

if exists (select * from sysobjects
    where name = 'sp_marksuspect' and sysstat = 4)
   drop procedure sp_marksuspect

if exists (select * from sysobjects
    where name = 'sp_diskblock' and sysstat = 4)
   drop procedure sp_diskblock

if exists (select * from sysobjects
    where name = 'sp_resetstatus' and sysstat = 4)
   drop procedure sp_resetstatus
Go


raiserror('Creating proc sp_diskblock.',1,1)
Go

CREATE PROC sp_diskblock @disk int, @block int AS
DECLARE @low    int,
        @dname  varchar(30),
        @msg    varchar(90),
        @lpage  int,
        @dbid   int,
        @segmap int

SELECT  @low = low,  @dname = name
    FROM master.dbo.sysdevices WHERE low/16777216 = @disk

IF ( @low IS NULL )
    BEGIN
        SELECT @msg = 'Virtual device ' + CONVERT(varchar, @disk)
         + ' does not exist on this server.'
        PRINT @msg
        RETURN (1)
    END
ELSE
    BEGIN
        SELECT  @lpage = lstart + @block + @low - vstart,
            @dbid = dbid, @segmap = segmap
            FROM master.dbo.sysusages WHERE (@block + @low) >= vstart
            AND (@block + @low) <= (vstart + size)
        IF ( @dbid IS NULL )
            BEGIN
                SELECT @msg = 'Block ' + CONVERT(varchar, @block)
                    +' on disk "' + @dname
                    + '" is currently not in use for any database.'
                PRINT @msg
                RETURN (1)
            END
        ELSE
            BEGIN
                SELECT @msg = "Virtual disk " + convert(varchar,@disk)
                    + ", block " + convert(varchar,@block)
                    + " corresponds to:"
                PRINT @msg
                SELECT @msg ='Logical page ' + convert(varchar,@lpage)
                    + ' in the "'  + DB_NAME(@dbid)
                    + '" database (dbid='  + convert(varchar(3),@dbid)
                    + ') on device "' + @dname + '".'
                PRINT @msg
            END
    END
RETURN (0)

Go


raiserror('Creating proc sp_resetstatus.',1,1)
Go

CREATE PROCEDURE sp_resetstatus  -- 1995/11/30 14:12 #12092
       @DBName          varchar(30)
as

Set nocount on

Declare
       @msg             varchar(80)
      ,@RetCode         integer
      ,@_error          integer
      ,@_rowcount       integer
      ,@int1            integer
      ,@bitSuspect      integer
      ,@mode            integer
      ,@status          integer

Select
       @RetCode         = 0  -- 0=no_problem, 1=some_problem

---------------------  Restrict to SA  -------------------------

IF suser_id() <> 1
   begin
   RaisError(15003,-1,-1)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


------------------  Get SuspectBit id value  ------------------

SELECT       @bitSuspect = min(number)
      from
             master..spt_values
      where  type = 'D  '
      and    name = 'not recovered'  -- 256, Suspect

IF (@bitSuspect IS Null)
   begin

   RaisError('
Surprisingly a Suspect bit value could not be Selected from spt_values,
thus using hardcoded value of 256.
',13,1)

   Select
       @bitSuspect      = 256
   end


----------------------  Forbid active txn  ---------------------

--- (Prior spt_values Sel trips SET implicit_transactions!)


IF @@trancount > 0
   begin
   RaisError(15002,-1,-1,'sp_resetstatus')
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


---------------  Obtain/Report pre-Update values  --------------------

SELECT
             @mode   = min(mode)
            ,@status = min(status)
      from
             master..sysdatabases
      where  name = @DBName

IF @@error <> 0 OR @status IS Null
   begin
   RaisError('
No row could be Selected from sysdatabases for DBName=''%s''!
',1,1,@DBName)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


Select @int1 = @status & @bitSuspect

Raiserror('
Prior to Update sysdatabases attempt for DBName=''%s'',
the mode=%d and status=%d (status suspect_bit=%d).
',1,1 ,@DBName ,@mode ,@status ,@int1)


---------------------  Update sysdatabases row  ---------------------


BEGIN TRANSACTION


UPDATE
             master..sysdatabases
      set
             mode    = 0
            ,status  = status & (~ @bitSuspect)
      where  name    = @DBName
      and
            (mode   <> 0      OR
             status  & @bitSuspect > 0
            )

Select @_error = @@error ,@_rowcount = @@rowcount


IF @_error <> 0
   begin

   ROLLBACK TRANSACTION

   RaisError('
ERROR!  @@error<>0 from Update sysdatabases.
',1,1)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


COMMIT TRANSACTION

-------- Report the results

IF @_rowcount = 0
   begin
   Raiserror('
No sysdatabases row was Updated,
because mode and status are already reset to fine.  No error, no changes made.
',1,1)
   end

ELSE
   begin

   Raiserror('
For DBName=''%s'' in sysdatabases,
status bit %d was forced Off and mode was forced to 0.'
,1,1, @DBName,@bitSuspect)

   Raiserror('
WARNING:  You MUST stop/restart SQL Server prior to accessing this database!
',1,1)

   end


LABEL_86BYEBYE:

RETURN @RetCode

Go


raiserror('Creating proc sp_marksuspect.',1,1)
Go

CREATE PROC sp_marksuspect @dbname varchar(30) AS
    DECLARE @msg     varchar(80)
    IF @@trancount > 0
        BEGIN
            PRINT "Can't run sp_marksuspect from within a transaction."
            RETURN (1)
        END
    IF suser_id() != 1
        BEGIN
            SELECT @msg = "You must be the System Administrator (SA) "
            SELECT @msg = @msg + "to execute this procedure."
            PRINT @msg
             RETURN (1)
        END
    IF (SELECT COUNT(*) FROM master..sysdatabases
             WHERE name = @dbname) != 1
         BEGIN
            SELECT @msg = "Database '" + @dbname + "' does not exist!"
            PRINT @msg
            RETURN (1)
        END
IF (SELECT COUNT(*) FROM master..sysdatabases
                WHERE name = @dbname and status & 256 = 256) = 1
        BEGIN
                SELECT @msg = "Database '" + @dbname + "' "
                SELECT @msg = @msg + "is already marked suspect."
                PRINT @msg
                RETURN (1)
        END
    BEGIN TRAN
        update master..sysdatabases set status = status|256
            WHERE name = @dbname
    IF @@error != 0 or @@rowcount != 1         ROLLBACK TRAN
    ELSE
        BEGIN
            COMMIT TRAN
            SELECT @msg = "Database '" + @dbname + "' has been marked "
    SELECT @msg = @msg + "suspect!"
            PRINT @msg
            PRINT " "
            SELECT @msg = 'WARNING: Next you should drop this database via DBCC REPAIR!'
            PRINT @msg
            PRINT " "
        END

Go

/*
** Disallow ad-hoc updates to system tables.
*/
sp_configure 'allow updates',0
Go
reconfigure with override
Go
raiserror('reconfigure- was just run.',1,1)
Go
-- -