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

Whing Dela CruzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.