• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 74
  • Last Modified:

How to delete Database?

Hi, Experts, I need to delete or drop my Database using .asp or vbscript command, how? My code below is not working, I'm having an error says, "-2147217865|[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the database 'Jyle761234', because it does not exist or you do not have permission."

<%
On Error Resume Next

response.expires=-1

set cn=Server.CreateObject("ADODB.Connection")
cn.ConnectionString = "driver={SQL Server};Server=" & "winzServer" & "\SQLEXPRESS;uid=sa;pwd=3970;"
cn.Open

    cn.Execute "DROP DATABASE Jyle761234;"
    cn.Close
   
If Err.Number <> 0 Then
   response.Write(  Err.Number & "|" & Err.Description )
   response.End()
else
   response.write("Database has been deleted!")
   response.end()
end if
%>

Open in new window

0
Whing Dela Cruz
Asked:
Whing Dela Cruz
1 Solution
 
Nitin SontakkeDeveloperCommented:
I believe that the error message is quite clear enough. Create a dummy / test database on the server and try dropping it using the IDENTICAL credentials used in above code.

Check if database really already exists. If permissions are okay and database doesn't exist, consider adding if exists part to your drop statement so as to suppress the error.

See if you can drop it from SQL Server Management Studio. If it succeeds, however, you will land up dropped after all.
0
 
Ryan ChongCommented:
people usually don't drop database, but if that's your business requirement, i would suggest you create a stored procedure at your database end instead of directly write that command in your ASP script for security proposes.>> you hide your code of what you're going to do.

in stored procedure, you can check if the database is exist before you dropping it.>> better error handling

permission is another issue that already mentioned, obviously your logon account need to have priviledge to drop database.

try search for "DROP DATABASE (Transact-SQL) | Microsoft Docs" in google (weird, I no longer can paste link directly in EE editor?!)
1
 
David Johnson, CD, MVPOwnerCommented:
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can see that you're using sa account so I'll put on the side the permission issue and focus on the first part of the error message, that's the database doesn't exist. Can you confirm the existence of the database? You might have run the code before and got the database deleted already so a second time won't work anymore.
0
 
Whing Dela CruzAuthor Commented:
Hi experts, the database to be deleted is exists . I also set the permission of the sql server 10  to alter any database but I'm still having the error. I tried to use this code to delete a table in the database and I was successful. Is there any alternatives or techniques to achieve the goal?

<%
On Error Resume Next

response.expires=-1

set cn=Server.CreateObject("ADODB.Connection")
cn.ConnectionString = "driver={SQL Server};Server=" & "winzServer" & "\SQLEXPRESS;uid=sa;pwd=3970;"
cn.Open

    cn.Execute "DROP DATABASE Jyle761234;"
    cn.Close
   
If Err.Number <> 0 Then
   response.Write(  Err.Number & "|" & Err.Description )
   response.End()
else
   response.write("Database has been deleted!")
   response.end()
end if
%>

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
DROP DATABASE only works if nobody is connected to the database. Are you sure nobody were?
Also try to connect to a master database in case of the default database is set to the one you're trying to delete:
cn.ConnectionString = "driver={SQL Server};Server=" & "winzServer" & "\SQLEXPRESS;database=master;uid=sa;pwd=3970;"
cn.Open

Open in new window

0
 
Whing Dela CruzAuthor Commented:
Hi experts, it was my big mistakes not to make sure the existing of the database to delete. It happened that I have a two server working on, and I thought  I was connected to winzServer. As I examine thoroughly the cause of the problem was the server where I wrongly connected. Thank you all,  I'm now able to delete the database base on my posted code above.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now