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

Set nocount on

Declare
       @msg             nvarchar(280)
      ,@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,'sysadmin')
   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

----------------------  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(15010,-1,-1,@DBName)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


Select @int1 = @status & @bitSuspect


Raiserror(15052,-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(15055,-1,-1)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


COMMIT TRANSACTION

-------- Report the results

IF @_rowcount = 0
   begin
   Raiserror(15056,-1,-1)
   end

ELSE
   begin

   Raiserror(15073,-1,-1, @DBName,@bitSuspect)

   Raiserror(15074,-1,-1)

   end


LABEL_86BYEBYE:

RETURN @RetCode