Deleting active sqlserver DB programmatically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting active sqlserver DB programmatically

Author

Topic

pskumaran84
Starting Member

5 Posts

Posted - 2006-05-08 : 04:08:29

I want to delete the DB that I'm currently connected to.(I am using VB.net as Front end).
What I did was the following:
Closed the connection(I used close and also dispose) and then connected to Master DB using a new connection and tried to drop the previous DB from this new connection.But it gives an error that the DB(to be dropped) is currently in use. So, What should I do to delete the DB? Is there anyother way to do it?

Thanks in advance,

Senthil

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-08 : 04:15:22

Do you access to the Enterprise Manager??

if so then, just go there.. and right click on the database and click on Deattach database.

Over there you will be able to see how many connectioins are open to that database.

Click on the clear button to clear the connections.

and then you can either drop the database or can deattach the database and then can delete the mdf file.

or run this query connecting to the database to know who all are connected to that databse and close all the connections and then drop the database.

SELECT Hostname 'Connected User', db_name(dbid) 'Database', Program_Name, Count(Program_Name) 'Connections'
FROM master.dbo.sysprocesses Where HostName <> '' And db_name(dbid) <> 'master'
Group By Hostname, db_name(dbid), Program_Name

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-08 : 05:50:35

quote:
Originally posted by pskumaran84

So, What should I do to delete the DB? Is there anyother way to do it?

You're doing the right thing connecting to a different database and then trying the drop, but it sounds as though other connections are still present.

-------
Moo. :)

pskumaran84
Starting Member

5 Posts

Posted - 2006-05-08 : 11:19:42

Hi guys,

Thanks a lot for that. Actually, I want to do it thru coding. I dont want to go to Enterp Mgr and drop the DB. The DB is local to my system and I'm the only one accessing it. Here is the part of the code:

//I am connected to DB tt1
Dim olecon As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=tt1;Data Source=localhost;")
olecon.Close()// closing the existing connection.
Dim olecon1 As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=localhost;") // new connection
olecon1.Open()
Dim cmd As New OleDb.OleDbCommand("DROP DATABASE tt1", olecon1)//example
cmd.ExecuteNonQuery()
olecon1.Close()

//Both of them are under the same SQL server instance

(I am using SQL server 2000 Enterp Edition . I read an article where it is easily done for SQL server 2005 with Dot net Framework v.2.0. But I dont want to use SQLserver 2005.

Thanks,

Senthil

pskumaran84
Starting Member

5 Posts

Posted - 2006-05-08 : 11:22:18

I have also tried the disposed property after the closing the connection.

olecon.close()
Olecon.dispose()

But it didnt workout.

Thanks,
Senthil

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-08 : 13:33:23

Just run the SQL script which i gave throug ur Query Analyser and u will came to know, what connection is active to that database

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-08 : 13:43:02

Deleting a databse from code doesn't sound like a very good idea.

Why do you want to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pskumaran84
Starting Member

5 Posts

Posted - 2006-05-08 : 13:44:37

Sorry dude..I am not able to understand that query:

SELECT Hostname 'Connected User', db_name(dbid) 'Database', Program_Name, Count(Program_Name) 'Connections'
FROM master.dbo.sysprocesses Where HostName <> '' And db_name(dbid) <> 'master'
Group By Hostname, db_name(dbid), Program_Name

Just give me an example plzz..

Hostname is localhost,Connected User ?, db_name:t1(example),Database ?, Program name?, Count(program name) ?, Connections ?.

My DB is in Single User mode.

Thanks,
Senthil

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-08 : 13:46:39

You could run a script from your VB code that would find and KILL all processes for the other database first, to ensure that nothing is actively connected to it. Like other I'm not sure why you would want to do this.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

pskumaran84
Starting Member

5 Posts

Posted - 2006-05-08 : 13:54:09

Hi Bret,

I want to do evrythin thru front end.So, (thru the appln) the DB is deleted and the person can create a new one with necessary columns and tables(thru front end) according to his wish.

-Senthil

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-09 : 09:45:57

Give the user the ability to delete the entire database.......
Sounds good to me ....... Not
I have seen more problems at just giving record delete capability than all other user problems combined.
Instead I just add an inactive column and let them check that.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources