HELP! I keep getting the same error message!

Hey Experts!

I have this SQL Database that has about 2,700 tables. (The completed version could have about 3,500 tables.) Each of these tables changes VERY fast and MUST be kept up to date! That being said, I've been led to believe the fastest way to remove all tables was to "DROP DATABASE database-name" and to use "CREATE DATABASE database-name" followed by "CREATE TABLE table-name".

This sounds good. Yet, I keep getting, "Cannot drop the database 'TEST', because it does not exist or you do not have permission." I've checked it and it does exist. So I guess that's not the problem.

Could the problem be within the SQL connection string that I've named, "ConString"? Or could the problem stem from the way I ".Close()" and then ".Dispose()"?

Any help you can give me that would solve this problem would be greatly appreciated! Thanks for listening to me vent my frustrations!

May you have a blessed day as the LORD wills.

JEP_Dude

PS: The code is listed below.

---

Const ConString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\TEST.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Public G_ErrorCount as integer = 0

Friend Sub Main()
.
.
If SQL_DropDataBase("TEST") = True Then
.
.
END IF
.
.
END SUB


Friend Function SQL_DropDataBase(ByVal L_DBName As String) As Boolean
Dim L_SQLString As String = "DROP DATABASE " & L_DBName
Dim L_Result As Boolean

Using L_SQL_Connection As New SqlClient.SqlConnection(ConString)
Dim L_SQLCommand As New SqlClient.SqlCommand(L_SQLString, L_SQL_Connection)

L_SQLCommand.Connection.Open()
Try
L_Result = True

L_SQLCommand.ExecuteNonQuery()
Catch ex As Exception
L_Result = False

Beep()
G_ErrorCount += 1
MessageBox.Show(ex.Message)
End Try

L_SQL_Connection.Close()
L_SQL_Connection.Dispose()
End Using

Return L_Result
End Function

 

check your connection string is same details which you use in your sql server?

 

so are you using vb.net with asp.net ?

 

you Can see that in the code that is VB.net

Read My Articles on Asp.Net

 

It seems like there is problem in your connection string .Remove it and again put the new string instead of it.

 

Two coding errors:

If SQL_DropDataBase("TEST") = True Then

Code (markup):

This is a pleonasm. Change it to

If SQL_DropDataBase("TEST") Then

Code (markup):

SQL_DropDataBase is returning a Boolean, which the If is testing. There's no need to compare the Boolean with True - that's the default.

Friend Function SQL_DropDataBase(ByVal L_DBName As String) As Boolean
  Dim L_SQLString As String = "DROP DATABASE " & L_DBName
  Dim L_Result As Boolean

  [B][COLOR=#ff0000]Using[/COLOR][/B] L_SQL_Connection As New SqlClient.SqlConnection(ConString)
    Dim L_SQLCommand As New SqlClient.SqlCommand(L_SQLString, L_SQL_Connection)

    L_SQLCommand.Connection.Open()
    Try
      L_Result = True

      L_SQLCommand.ExecuteNonQuery()
    Catch ex As Exception
      L_Result = False

      Beep()
      G_ErrorCount += 1
      MessageBox.Show(ex.Message)
    End Try

    L_SQL_Connection.Close()
    L_SQL_Connection.Dispose()
  [B][COLOR=#ff0000]End Using[/COLOR][/B]

  Return L_Result
End Function

Code (markup):

Drop the Using. Using a variable that's being Used (you do it 3 times) is not only wasteful and semantically incorrect, it can cause your code to fail.

 

does the account that is performing the drop have the necessary permissions ?

 

You are trying to drop the entire database? I would expect that you can't drop a database from code like this if there are active connections. And if you are actively connecting to the database in question in order to drop it, then you would have an active connection.

So, my guess is that you need to do DROP TABLE instead of DROP DATABASE

 

Here is a way to do this;

http://www.fragmentedcode.com/2008/04/22/sql-server-drop-tables-database-quickly/

If I were you, I'd create this code as a stored procedure and this call the stored procedure from code. Again, you probably will never be able to drop a database while you have an active connection to it.

SELECT NAME
INTO #tables
FROM sys.objects
WHERE type = 'U'
 
WHILE (
        SELECT count(1)
        FROM #tables
    ) > 0
BEGIN
    DECLARE @sql VARCHAR(max)
    DECLARE @tbl VARCHAR(255)
 
    SELECT TOP 1 @tbl = NAME
    FROM #tables
 
    SET @sql = 'DROP TABLE ' + @tbl
 
    EXEC (@sql)
 
    DELETE
    FROM #tables
    WHERE NAME = @tbl
END
 
DROP TABLE #tables;

Code (markup):

 

integrated security=true means that you're connection to sql with current windows user.
Are you sure that your logged windows user is admin?