[RESOLVED] Drop Database

[PlayKid]

Dear all,

I am trying to make a code in VB.Net to drop database programatically, but when I try to drop it, it said the database is currently in use.

I use the following code:

Dim SQLString As String = _
            "IF EXISTS (" & _
            "SELECT * " & _
            "FROM master..sysdatabases " & _
            "WHERE Name = '" & newDatabaseName.Text & "')" & vbCrLf & _
            "DROP DATABASE " & newDatabaseName.Text & ""
 
        Try
            Dim SQLConnection As New SqlClient.SqlConnection
            SQLConnection.ConnectionString = SQLConnectionString
            Dim Command As New SqlClient.SqlCommand(SQLString, SQLConnection)
   
            SQLConnection.Open()
            Command.ExecuteNonQuery()
            SQLConnection.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

How can I overcome this?

Thank you

PlayKid

[Asgorath]

Transact-SQL Reference


DROP DATABASE
Removes one or more databases from Microsoft® SQL Server™. Removing a database deletes the database and the disk files used by the database.

Syntax
DROP DATABASE database_name [ ,...n ]

Arguments
database_name

Specifies the name of the database to be removed. Execute sp_helpdb from the master database to see a list of databases.

Remarks
To use DROP DATABASE, the database context of the connection must be in the master database.

DROP DATABASE removes damaged databases marked as suspect and removes the specified database. Before dropping a database used in replication, first remove replication. Any database published for transactional replication, or published or subscribed to merge replication cannot be dropped. For more information, see Administering and Monitoring Replication. If a database is damaged and replication cannot first be removed, in most cases you still can drop the database by marking it as an offline database.

A dropped database can be re-created only by restoring a backup. You cannot drop a database currently in use (open for reading or writing by any user). When a database is dropped, the master database should be backed up.

System databases (msdb, master, model, tempdb) cannot be dropped.

Permissions
DROP DATABASE permissions default to the database owner, members of the sysadmin and dbcreator fixed server roles, and are not transferable.

Examples
A. Drop a single database
This example removes all references for the publishing database from the system tables.

DROP DATABASE publishing

B. Drop multiple databases
This example removes all references for each of the listed databases from the system tables.

DROP DATABASE pubs, newpubs


See Also

ALTER DATABASE

CREATE DATABASE

sp_dropdevice

sp_helpdb

sp_renamedb

USE

©1988-2000 Microsoft Corporation. All Rights Reserved.

[PlayKid]

I have resolve it already....I have use master and alter table, set single_user with rollback immediate to drop the database....

[HellWALKER]

PlayKid (or anyone reading this)

I have the same problem you had, but I don't really understand your solution... Here is my code for my drop function:

'This function deletes the database with it's table
    ' - strDatabase:        Name of the database
    Public Sub s_DropDatabase(ByVal strDatabase As String)
        Try
            s_ConnectDB("master")
            'Settings for the command
            m_objSqlCommand.Connection = m_objSqlConnection
            m_objSqlCommand.CommandType = CommandType.Text
            'Command to delete the database
            m_objSqlCommand.CommandText = "DROP DATABASE " & strDatabase
            m_strCurrentDB = m_objSqlConnection.Database 'Update the class variable
            m_objSqlCommand.ExecuteNonQuery() 'Execute the DROP DATABASE command
        Catch ex As Exception
            'Error management. When an error occurs, the error number and its description is
            'displayed in a message box
            MessageBox.Show(ex.Message)
        Finally
            s_CloseConnection()
        End Try
    End Sub

I'm always having this problem... I'm getting many headaches over this one... If you have a solution, I even tried to unplug my network cable just to be sure I'm the only one connected to my MSDE server and it still says the database is in use.

Can you explain that thing about alter table? How does that help to delete a database? And what about that single_user thing?

Tanx in advance,
Phil

[PlayKid]

m_objSqlCommand.CommandText = "ALTER DATABASE " & strDatabase
 & "" & vbcrlf _
"SET SINGLE_USER WITH ROLLBACK IMMEDIATE" & vbcrlf & _
"DROP DATABASE " & strDatabase & ""

Try understand some T-SQL statement, it helps.......
this command is telling the database to remove all the connection that the database currently is having, so then the database is not using currently, then drop it.

[HellWALKER]

tanx PlayKid, I'm gonna take a look a that

Phil

[Manny Somarriba]

Hi Asgorath, I would like to ask if you can show "playkid" code modified, please? Thank you so much.

[.paul.]

@Manny Somarriba This is a 19 year old thread. You can't expect help from the people who answered back then in 2005. Try starting your own new thread in the VB.Net general forum

[Manny Somarriba]

Thank you .Paul. I really appreciate your help and yours links

[Manny Somarriba]

Ok .Paul I will do, thank you.

[Manny Somarriba]

If that is impossible, you can create a batch file in DOS mode using the command DOS to delete completely. At least your database administrator set up a lock on it.

[Peter Porter]

@Manny Somarriba This is a 19 year old thread. You can't expect help from the people who answered back then in 2005. Try starting your own new thread in the VB.Net general forum

I believe Manny Somarriba is testing a bot with his account. He's been a member since 2001, barely posts, but replies to someone's comment from 2005. He then thanked you twice when there was no reason to, then continues like you never pointed out this post is old:

Thank you .Paul. I really appreciate your help and yours links

Ok .Paul I will do, thank you.

If that is impossible, you can create a batch file in DOS mode using the command DOS to delete completely. At least your database administrator set up a lock on it.

Or is this just the way he is?

[.paul.]

I believe Manny Somarriba is testing a bot with his account. He's been a member since 2001, barely posts, but replies to someone's comment from 2005. He then thanked you twice when there was no reason to, then continues like you never pointed out this post is old:

…Or is this just the way he is?