
/*
** 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
-- -

