Problem Dropping Database C#

Apr 26, 2006

Hi,

i have a problem dropping a database?

I have a WinForm and i can create DB, create the Tables and drop the DB via buttons.

When i start myProgram it's no problem to drop the Database.
Then i can create the database without problems.
The tables also without problems.

Now if i try to drop my whole DB, i get the error message:

SqlException Handle :System.Data.SqlClient.SqlException: Cannot drop database "XtmsDb" because it is currently in use.


The code for dropping db:

public void deleteProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";

sqlStr = "Drop database XTmsDB";

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");

SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database dropped!!!");
}
catch (SqlException dropEx)
{
System.Console.WriteLine("SqlException Handle :{0}", dropEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}


The code for creating db:

public void createProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
sqlStr = projectDBStrings.getDBCreateString();

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database created!!!");

}
catch (Exception)
{
System.Console.WriteLine("Could not establish Connection!");
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}

The code for creating Tables:

public void createProjectTables()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=XtmsDb;Integrated Security=SSPI;";
ArrayList createList = projectDBStrings.fillProjectSchema();
SqlTransaction tx;
SqlCommand cmd = new SqlCommand("", conn);
IEnumerator createListEnum = createList.GetEnumerator();

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
try
{
while (createListEnum.MoveNext())
{
sqlStr = (String)createListEnum.Current.ToString();
tx = conn.BeginTransaction();

cmd.CommandText = sqlStr;
cmd.Transaction = tx;
cmd.ExecuteNonQuery();

tx.Commit();

}
System.Console.WriteLine("Schema created!!!");

}
catch (SqlException deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
}
catch (SqlException connectionEx)
{
System.Console.WriteLine("SqlConnection Handle : {0}", connectionEx.ToString());
}
finally
{
conn.Close();

System.Console.WriteLine("Connection closed!!!");
}
}



Who can help me?

View 14 Replies


ADVERTISEMENT

Dropping Database

Dec 21, 1998

Hi,

After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level,
It gives me the error:
Attempt to locate entry in the sysdatabases for database 'db1' by name,
failed -- no entry found in that name.

What should I do? Thanks.

View 1 Replies View Related

Dropping Database

Sep 7, 2007

Hi friends,

How I can drop a database insatance from a different server ( data base is SQL Server 2005) from my code

Hoping a early reply ....
Thanks :)

View 5 Replies View Related

Dropping A Database From ASP.NET

Jan 16, 2008

I am trying to run a dymanic DROP DATABASE <dbname> command in a stored procedure called from ASP.NET. I typically connect from the .net using a SQL builtin account. I am getting this error:

System.Data.SqlClient.SqlException: Cannot drop the database 'db_testco', because it does not exist or you do not have permission.

The database is there for sure asI can browse it via the Studio manager. What permission do I need to set for my .net login to allow it to drop ? I tried adding the Delete permission to the database and still no go...

Setup: SQL 2005 SP2 2 Node cluster.

Thanks.

View 3 Replies View Related

Dropping Distribution Database

May 2, 2000

i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .

thanks

View 1 Replies View Related

Dropping Users In Database

Dec 23, 1999

Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?

View 3 Replies View Related

Dropping Connections To Restore Database

Aug 3, 2000

Hello all,

I need to restore my database and I keep getting a database in use error. Can someone tell me the easiest way to drop all connections to a database and open it exclusively?

Also, I'm not sure where this connection is coming from, there is another database on the same machine that has user connected, can these connections be the problem?

Thanks

Kevin Kraus

View 3 Replies View Related

Dropping A Database That Subscribes For Replication

Nov 2, 1999

I am trying to delete a database that currently subscribes for replication. The problem however, is that the publishing server no longer exists and I can't disable the subscription therefore I can't drop the database. When I try to disable the replication the server tries to communicate with the old server. Is there anyway to force the dropping of the database or to force the server to disable replication.

thanks in advance

Michael

View 2 Replies View Related

Dropping Database On Another Server Instance

Oct 2, 2015

I have managed to successfully connect to another SQL Server instance using a linked server.

I can then do my select statement like this: select * from [servernameinstancename].[databasename].[databaseowner].[tablename]

I just wanted to know how I can drop the database on the other instance?I have tried doing: DROP DATABASE [servernameinstancename].[databasename].

View 3 Replies View Related

Dropping All Database Objects Through Script

May 28, 2008

Does anyone have a script that will iterate through a database and drop all objects in order of dependencies? I need a way recreate all objects in database through SQL script in a development environment without having to recreate the actual database. Therefore I need a way to clear out the current database objects first without running into a problem with dependencies. This needs to be dynamic so that if a object is added, it is automatically included in the drop scripts.

View 5 Replies View Related

DB Design :: Dropping And Rebuilding A Database

Jun 26, 2015

With some performance issues faced as I checked on Google came across Paul Randal's article on Logging & Recovery: URL...In this regard, and with some schema changes required, ended up with a decision to drop and re-create a database. What are the proper way to do the same and following queries:

A) What would be the recommended way to shift data from some tables of former database to that of the newer one (The latter ones created with the Script Table transaction)?
B) Would a simple Drop Database completely delete the former database completely and clean up the memory (database pages etc.) or I need to take some additional steps for clearing up the space?
C) Most of the data tables have been updated via loading the Bulk Insert commands time to time from CSV/flat files. Could that be somehow the cause of enlarging the the size of Database and Log?

View 16 Replies View Related

Dropping An Active Connection On An Attached Database

Jan 9, 2008

Why in the hell doesn't SQL Server provide a facility for dropping anactive connection on an attached database in SQL Server Managementconsole? I can't detach an attached database because apparently thereis an active connection.I know you can use SSEUTIL but it seems like kluge for a poorlythought out function.Crazy

View 2 Replies View Related

Reducing Database Size After Dropping Text Column

Jul 20, 2005

Hello,A while back I dropped a text column from a SQL Server 7 databaseroughly 3GB in size. I expected the size of the database to decreaseby around 1GB, but no change occurred. After searching usenet, Idiscovered that SQL Server 7 has no way of reclaiming that space, butthat there is some command that can be run in SQL Server 2000 thatwill reclaim it.I have since migrated this database to SQL Server 2000, and am nowtrying to figure out what that command is, but cannot locate anyusenet posts about it... also tried searching books online, but can'tfind anything that way either.Does anyone know what I should run?Thanks,Tom

View 6 Replies View Related

Transact SQL :: Database Size Does Not Decrease After Dropping All The Tables

Oct 15, 2015

I have a database consisting of two main tables and 12 sub tables.

This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.

So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.

Surprisingly we saw that the database size increased rather than decreasing .

View 9 Replies View Related

Dropping Tables

May 28, 2002

Is there a way we can prevent a object owner from dropping his tables. Example:

There is a user called 'tom' who is given create table permissions, 'tom' creates the table completes his dev and then the table is moved into production where 'tom' is still the owner. However, in production, 'tom' does not have the create table privs. Because 'tom' is the owner of the table, he is still able to drop the table in production. This is what I am trying to avoid. I would like to retain 'tom' as the owner and want to take away his create/drop privs.

View 1 Replies View Related

Dropping The Statistics

Nov 17, 2000

Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!

View 3 Replies View Related

DRopping Primary Key.

Dec 7, 1999

Simple heres the syntax Alter Table XX Drop PRIMARY KEY.

This is not working whats up?

Thanks,
Phil

View 3 Replies View Related

Dropping A Column

Apr 12, 2001

Whats the quickest way to drop a column from a table with 1 million rows.
This is for SQL Server 6.5

Thanks for all the responses,
Chan

View 1 Replies View Related

Dropping Connections - 6.5

May 22, 2001

I need to drop a database but cannot because there are 2 open connections that I cannot drop via EM. Is there a way to do this without starting and stopping??

View 2 Replies View Related

Dropping Columns

Jul 20, 1998

Hi,

I`m new at SQL Server so excuse me if this question sounds dumb.

Does anybody know how to drop columns in a table. I know I can drop and re-build the table,
but I would prefer and easier way.

Thanks

-Z

View 1 Replies View Related

Dropping Defaults

Jul 6, 2001

I have run the following script to create a default on a table:

alter table TableXYY
add default 0 for ColumnX

Now, how do I delete this default (without re-creating the table)??

Thanks

View 1 Replies View Related

Dropping Users

Jul 23, 2001

Anyone know how to drop all users associated with a particular login not matter what database they're contained in?

ie login AdminUser / User AdminUser (in databases master, Dev1)

....
Thus, is there a way to drop the AdminUser login above and associated user
AdminUser in databases master, Dev1 in example above.

Thanks

View 1 Replies View Related

Dropping A Set Of Tables

Feb 22, 2004

Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regds

View 1 Replies View Related

Dropping All Indexes

Jan 3, 2007

Looking for suggestions.

I have a database that is giving me a bad Index error. When I go to drop the necessary Index it is telling me that it either does not exist or cannot be dropped. However when I try to build that index, it tells me one already exists.

Is there any way to drop all of the indexes or at the very least see what the indexes are? This particular database is using 2005 Express.

Any help would be great!
Shawn

View 4 Replies View Related

Dropping Constraints

Oct 15, 2007

How to remove the constraints on a table in sql server 2000? I need to drop the column, so i need to drop the constraint before that.

Alter Table Table_Name NO CHECK constraints ALL.

Once I execute the above one, it says, "Successfully Executed" but when I try to drop it doesnt allow me and gives me the following error:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_Users_Created_by' is dependent on column 'CREATED_BY'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN CREATED_BY failed because one or more objects access this column.

Any ideas?

Thanks,

View 2 Replies View Related

Dropping A Constraint

Sep 20, 2006

I think the answer to this question will be something like 'you'll have to re-initialize the subscribers', but I need to ask anyway...

I created a publication where my foreign key constraint we NOT created with NOT FOR REPLICATION. My publication is configured to replicate DDL changes. Is there any way I can drop and re-create the constraint in the publisher and get replication to push the change to the subscribers?

Thanks for your help

Graham

View 1 Replies View Related

Trouble Dropping A Job

Mar 10, 2007

I have trouble dropping a job that was a maintence plan.

I get the following error. Is there a way around this?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Job 'Maintenne Plan'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

 

View 3 Replies View Related

Best Way Of Dropping The Column

Oct 11, 2007

Hi,

I have a table with multiple column and has millions of rows in it(say about 50 millions ) and its in production.
What is the best way of dropping the a column from the table without impacting the systems performance. Remeber this table will be used some application to get some data out of it.


~Mohan

View 1 Replies View Related

Need Help Dropping Master Key

Apr 11, 2007

I am having following issue recreating my service broker



This could be a trivial fix...



when i execute the following query..



open master key decryption by password = 'pass1234$'

alter master key drop encryption by password = 'pass1234$'

close master key

drop master key

go



I get the following result..



Msg 15558, Level 16, State 1, Line 3

Cannot drop encryption by password '********'.

Msg 15580, Level 16, State 1, Line 5

Cannot drop master key because certificate 'BackofficeServiceAccount' is encrypted by it.



But 'BackofficeServiceAccount' was already dropped.



Can any one help me how to drop master key?



I appreciate any input..



regards...

View 3 Replies View Related

Trigger Dropping

Apr 12, 2006

Hi:

I think I am doing something wrong. I am trying to make the trigger fire when I want to drop the same trigger. For example I have the following trigger defined:

USE AdventureWorks2000

GO

CREATE TRIGGER Incheckstock

ON DATABASE

FOR DROP_TRIGGER

AS

PRINT 'You must disable Trigger "Incheckstock" to drop Trigger!'

ROLLBACK

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER Incheckstock ON DATABASE



DROP TRIGGER Incheckstock

ON DATABASE

I created the trigger Incheckstock and i want to see if the same trigger fires itself if when i am trying to drop the trigger with the same name (kind of recursive one). Is this possible in SQL 2005?. If I asked a dumb question, please correct me.

Thanks

View 1 Replies View Related

Update Is Dropping A Number

Jan 20, 2007

I'm having a problem updating my Client profile table. I intially put
the data in with the "INSERT" SQL command that part works great. but
when I retrive the data to edit the profile it returns the correct
details and phone numbers. The problem is when I use the "UPDATE" SQL
command it saves all the data correctly except for the mobile phone
number which it drops off the last number all the time. Field sizes etc
are okay. Any help appreaciated

View 2 Replies View Related

Dropping Excel Sheet

May 14, 2002

I guess nobodys heard of this? I'm using DTS to transform data to Excel spreadsheet. I have a DROP TABLE `data$` then a CREATE TABLE `data$` the old data is cleared but the new data is appended to the blank rows of the old data. So if I had 5 rows before now I have 10. And the new data has 5 blank rows before it.

I've tried deleting the excel file & replacing it with a new one.
I've used the wizard thinking it was me but no good, it still happens.

HELP!

View 1 Replies View Related

Dropping SQL Server Connections

Jul 17, 2001

I am using Access 2000 to link tables from SQL Server. The tables are linked using ODBC DSN`s. I am running a query to return some rows to Access, but once I have returned the rows, I want to drop the linked table connection to SQL Server, because it is locking a table on the server. I think I need to write a bit of VBA code, but I don`t know where to start. Any ideas would be greatly appreciated. Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved