alter database set single_user

• Indianrock

  SSC-Insane

  Points: 20333

  More actions

  March 2, 2005 at 7:21 pm

  #85554

  I'm wondering if this is the best way to set a database to single_user, do an update or schema change and then set back to multi_user.  Should transactions be incorporated?  Nobody likes "go to"  these day, but it works here. I've confirmed that it kicks out web services users as well as others in the sysadmin group.

  use master

  go

  DECLARE @intErrorCode INT

  alter database inventory set SINGLE_USER with ROLLBACK immediate

  SELECT @intErrorCode = @@ERROR

  IF (@intErrorCode <> 0) GOTO PROBLEM

  -- do database update, drop databae, schema change etc

  print ' doing database work '

  SELECT @intErrorCode = @@ERROR

  IF (@intErrorCode <> 0) GOTO PROBLEM

  alter database inventory set MULTI_USER with ROLLBACK immediate

  SELECT @intErrorCode = @@ERROR

  IF (@intErrorCode <> 0) GOTO PROBLEM

  PROBLEM:

  IF (@intErrorCode <> 0) BEGIN

  PRINT 'Unexpected error occurred!'

     

  print @@error

  END

  -- check to see if was set back to multi_user

   exec master.dbo.sp_dboption 'inventory'

• philcart

  SSC-Forever

  Points: 47794

  More actions

  March 2, 2005 at 8:22 pm

  #545093

  How about this?

  use master
  go
  DECLARE @intErrorCode INT

  alter database inventory set SINGLE_USER with ROLLBACK immediate

  SELECT @intErrorCode = @@ERROR
  IF (@intErrorCode = 0) 
  BEGIN
   -- do database update, drop databae, schema change etc
   print ' doing database work '

   SELECT @intErrorCode = @@ERROR
   IF (@intErrorCode = 0)
   BEGIN
    alter database inventory set MULTI_USER with ROLLBACK immediate
    
    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode = 0) 
     exec master.dbo.sp_dboption 'inventory' 
     
   END
  END

  IF (@intErrorCode <> 0) 
  BEGIN
   PRINT 'Unexpected error occurred!'
      print @@error
  END

  None of those horrible GOTO's that nobody likes

   

  --------------------
  Colt 45 - the original point and click interface