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.
| Author |
Topic |
|
under2811
Constraint Violating Yak Guru
366 Posts |
 Posted - 2006-08-27 : 08:12:35
|
| hello friendswith reference to forum disscission in subject : Creating Database at run time??????? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71099i created run time database and transfer my basic tables in that now i need to drop the database whose Domain user's licence expired that i decided on expiration periods...ok..but i am facing problem i wrote stored procedure Create Proc SP_DropDataBase@DBID varchar(100)asset @queryDB = 'use masterEXEC sp_detach_db 'DB_UserProfileIDTest'drop database DB_UserProfileID' + @DBID+ 'exec (@queryDB)but i am getting this error....Msg 3703, Level 16, State 2, Line 1Cannot detach the database 'DB_UserProfileIDTest' because it is currently in use.Msg 3702, Level 16, State 4, Line 3Cannot drop database "DB_UserProfileIDTest" because it is currently in use.I know that some one is using this database but how to detach this by force fully and drop the databaseT.I.A |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-27 : 10:13:16
|
| Why are you trying to detach and drop?You won't be able to run this sp in the database you are trying to drop as you will be a user in the database.Look at master..sysprocesses to get a list of all spids in the database and execute a kill command for them.When all have gone drop the database - it may take some time for the spids to drop.You would be able to do this for system spids.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Drop the database forcefully that uses by user