SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dropping a database
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

asher
Starting Member

Netherlands
36 Posts

Posted - 12/06/2012 :  07:08:28  Show Profile  Reply with Quote
I have been unable to find code for dropping a database. I am aware that "dropdatabase" can be put into a command string, but doing that has lead to error messages. 1. Or is removing the database entries in the directory simply enough? 2. Does someone have a simple code example for dropping a database?

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/06/2012 :  07:12:46  Show Profile  Reply with Quote
DROP DATABASE YourDatabaseName --------> will drops entire database

--
Chandu
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  07:14:09  Show Profile  Reply with Quote
You can use
DROP DATABASE YourDataBaseNameHere;
See more details in this article: http://msdn.microsoft.com/en-us/library/ms178613.aspx

In particular, "You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER."
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 12/06/2012 :  07:16:39  Show Profile  Visit webfred's Homepage  Reply with Quote
error messages? Is it possible to post them here?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Mission123
Starting Member

1 Posts

Posted - 12/06/2012 :  07:24:42  Show Profile  Reply with Quote
I have been unable to find code for dropping a database. I am aware that "dropdatabase" can be put into a command string, but doing that has lead to error messages. 1. Or is removing the database entries in the directory simply enough? 2. Does someone have a simple code example for dropping a database?

Re: Could you post the screenshot with error message.


--Sri
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/06/2012 :  07:37:44  Show Profile  Reply with Quote
Code:

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using InfraStructure;

namespace FilesAndDatabase
{
public sealed class DropDatabase
{
public DropDatabase()
{
try
{
string file = @"c:\...\... .mdf";
if (File.Exists(file))
{
SqlConnection connection = new SqlConnection
("Server =.\\Sqlexpress;DataBase=[...];Integrated Security=true");
string command_string = "DROP DATABASE[...]";
SqlCommand command = new SqlCommand(command_string, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception x)
{
new Warning(x.Message);
}
}
}
}


Error message:

Cannot open database "[...Database]" requested by the login. The login failed.
Login failed for user 'VOS\...'.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  08:15:23  Show Profile  Reply with Quote
In connection string, specify Database=master. Also, make sure the user has sufficient privileges. "Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role."
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/06/2012 :  08:15:57  Show Profile  Reply with Quote
I have posted the code and the error message as requested. Can someone assist?
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/06/2012 :  08:25:31  Show Profile  Reply with Quote
Changing the name of the database to master does not solve the problem. "Cannot open database "master" requested by the login. The login failed. Login failed for user ""..."". How do I grant CONTROL permission?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  09:14:53  Show Profile  Reply with Quote
quote:
Originally posted by asher

Changing the name of the database to master does not solve the problem. "Cannot open database "master" requested by the login. The login failed. Login failed for user ""..."". How do I grant CONTROL permission?

Connect to the server using a admin account and security -> logins right click on the login name you want to use, properties, Usermapping, select the database and grant the db_owner role.
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/06/2012 :  17:45:15  Show Profile  Reply with Quote
The application is to be sold on the market; if I am not misunderstanding what sunita beck is saying, the procedure described (Connect to the server etc.) would not seem to allow for that. Apart from this, is sunita beck referring to SQL server manager? I have installed sqlserver.exe - and nothing else - which does not seem to permit doing what is being suggested.
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/07/2012 :  04:05:42  Show Profile  Reply with Quote
Is simply deleting the fies in the directory OK?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/07/2012 :  07:20:03  Show Profile  Reply with Quote
quote:
Originally posted by asher

The application is to be sold on the market; if I am not misunderstanding what sunita beck is saying, the procedure described (Connect to the server etc.) would not seem to allow for that. Apart from this, is sunita beck referring to SQL server manager? I have installed sqlserver.exe - and nothing else - which does not seem to permit doing what is being suggested.

Are you DEVELOPING an application to be sold on the market, or are you working with an application that you bought?

If you are developing an application to be sold on the market, you should install SQL Server Management Studio (SSMS) in your development environment. That does not mean that you would need to install SSMS on every client machine where your application will be deployed to. SSMS is THE tool you would use to develop the database side of your application.

If the application is something that you bought from a vendor, the best course of action would be to consult the vendor.

Also, it is unusual for a client application to be dropping and recreating databases. So if you are developing an application, and if that calls for dropping databases, there may be room for design improvements.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/07/2012 :  07:20:50  Show Profile  Reply with Quote
quote:
Originally posted by asher

Is simply deleting the fies in the directory OK?


Do you mean the mdf and ldf files? That would NOT be the right thing to do if you want to drop a database.
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/07/2012 :  13:22:27  Show Profile  Reply with Quote
Dunita Beck Hello,

The situation is simpler than you assume. I want a client to be able to delete the application, get rid of all traces, that is all. I normally develop Windows applications using files, so this is a new ball game for me. If I create a database dynamically from within the application, which I do, I would expect it to be possible to delete the database dynamically as well, without a lot of paraphernalia. If the user credentials are good enough to create a database, why are they not good enough to get rid of it again, without running into all sorts of error messages.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/07/2012 :  13:57:50  Show Profile  Reply with Quote
quote:
Originally posted by asher

Dunita Beck Hello,

The situation is simpler than you assume. I want a client to be able to delete the application, get rid of all traces, that is all. I normally develop Windows applications using files, so this is a new ball game for me. If I create a database dynamically from within the application, which I do, I would expect it to be possible to delete the database dynamically as well, without a lot of paraphernalia. If the user credentials are good enough to create a database, why are they not good enough to get rid of it again, without running into all sorts of error messages.

I wouldn't be able to answer that question without being able to access your code and the database server. I could guess, but several of my guesses have been wrong. The shortest path to victory would be to do what I already suggested.

But, perhaps some of the experts on the forum would be able to offer better suggestions.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/07/2012 :  15:50:48  Show Profile  Reply with Quote
If you want to drop the database the command(s) are pretty simple. However, if you don't have access to drop the database, then that's a different issue.

If you just need to drop the database, I'd also suggest adding an ALTER first to make sure no one has it locked. Make sure you are exucuting in the MASTER db and run:
ALTER DATABASE <Database Name> OFFLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE <Database Name>
GO
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/07/2012 :  16:15:21  Show Profile  Reply with Quote
quote:
Originally posted by asher

Dunita Beck Hello, Hello it is Sunita

The situation is simpler than you assume. I want a client to be able to delete the application, get rid of all traces, that is all. I normally develop Windows applications using files, so this is a new ball game for me. If I create a database dynamically from within the application, which I do, I would expect it to be possible to delete the database dynamically as well, without a lot of paraphernalia. If the user credentials are good enough to create a database, why are they not good enough to get rid of it again, without running into all sorts of error messages.


This is bad design to dynamically drop database.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/07/2012 :  16:55:46  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

[quote]Originally posted by asher
This is bad design to dynamically drop database.

I don't agree (unless I'm misunderstanding "dynamically"). If you install an applicaiton on my computer that creates a database and I unistall that program, I would want the database removed as well. That seems like a pretty common pattern from the database applications I've used.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/07/2012 :  18:19:45  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

If you want to drop the database the command(s) are pretty simple. However, if you don't have access to drop the database, then that's a different issue.

If you just need to drop the database, I'd also suggest adding an ALTER first to make sure no one has it locked. Make sure you are exucuting in the MASTER db and run:
ALTER DATABASE <Database Name> OFFLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE <Database Name>
GO




If you drop the database while it is OFFLINE, it will not delete the database files, so you would have to do that manually later.




CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/07/2012 :  18:20:44  Show Profile  Reply with Quote
The code below should do the job.

Before running it, make sure that the login you are using has sysadmin privileges and does not have the database to be dropped as its default database. It would also be good to check that no other logins have that database as the default database. If you drop a user’s default database, then they will no longer be able to login.

USE MASTER
GO
ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatebase]




CODO ERGO SUM
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000