Database state cannot be changed while other users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database state cannot be changed while other users

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-11-02 : 00:19:08

Hi guys

I've created the stored procedure for restoring db and I put this into Administration db. I got the following error message:

Database state cannot be changed while other users

One of the SQL statements is:

--SET @sSQLAlter1 = 'ALTER DATABASE ' + @sNewDBName + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;'
SET @sSQLAlter1= 'EXEC master.dbo.sp_dboption ''' + @sNewDBName + ''', ''offline'', ''TRUE'''

I have to use master.dbo.sp_dboption due to this need to be run on master, and can't really use ALTER DATABASE statement cause it's running on Administration database and can't use USE statement.

What did I do wrong then?

Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-02 : 04:33:25

You have to make sure that noone is using the database.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources