Author |
Topic |
asher
Starting Member
36 Posts |
Posted - 2012-12-06 : 07:08:28
|
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
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-06 : 07:12:46
|
DROP DATABASE YourDatabaseName --------> will drops entire database--Chandu |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 07:14:09
|
You can useDROP DATABASE YourDataBaseNameHere; See more details in this article: http://msdn.microsoft.com/en-us/library/ms178613.aspxIn 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." |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-12-06 : 07:16:39
|
error messages? Is it possible to post them here? Too old to Rock'n'Roll too young to die. |
|
|
Mission123
Starting Member
1 Post |
Posted - 2012-12-06 : 07:24:42
|
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 |
|
|
asher
Starting Member
36 Posts |
Posted - 2012-12-06 : 07:37:44
|
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\...'. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 08:15:23
|
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." |
|
|
asher
Starting Member
36 Posts |
Posted - 2012-12-06 : 08:15:57
|
I have posted the code and the error message as requested. Can someone assist? |
|
|
asher
Starting Member
36 Posts |
Posted - 2012-12-06 : 08:25:31
|
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? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-06 : 09:14:53
|
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. |
|
|
asher
Starting Member
36 Posts |
Posted - 2012-12-06 : 17:45:15
|
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. |
|
|
asher
Starting Member
36 Posts |
Posted - 2012-12-07 : 04:05:42
|
Is simply deleting the fies in the directory OK? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-07 : 07:20:03
|
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. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-07 : 07:20:50
|
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. |
|
|
asher
Starting Member
36 Posts |
Posted - 2012-12-07 : 13:22:27
|
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. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-07 : 13:57:50
|
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. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-07 : 15:50:48
|
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 IMMEDIATEGODROP DATABASE <Database Name>GO |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-07 : 16:15:21
|
quote: Originally posted by asher Dunita Beck Hello, Hello it is SunitaThe 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. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-07 : 16:55:46
|
quote: Originally posted by sodeep [quote]Originally posted by asherThis 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. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-12-07 : 18:19:45
|
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 IMMEDIATEGODROP 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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-12-07 : 18:20:44
|
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 MASTERGOALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATEGOALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATEGODROP DATABASE [MyDatebase] CODO ERGO SUM |
|
|
Next Page
|