469,329 Members | 1,495 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,329 developers. It's quick & easy.

Cannot drop database

Hi All,

I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by disabling
connection pooling and the database can be dropped. Is there any way to
clear out the connection pool? I can't disable connection pooling as my app
uses it. Some sample code is below

Thanks
Michael
private void DoIt()
{
Execute("CREATE DATABASE ABC");
//remove this next line and it works
Execute("SELECT * FROM sysobjects", "ABC", false);
Execute("SELECT * FROM sysobjects", "ABC", true);
Execute("DROP DATABASE ABC");
}

private void Execute(string SQL)
{
Execute(SQL, "", false);
}

private void Execute(string SQL, string Database, bool NoPooling)
{
string connect = "Data Source=(local);Integrated
Security=SSPI;";
if (Database.Length 0) connect += "Initial Catalog=" +
Database + ";";
if (NoPooling) connect += "Pooling=False;";
SqlConnection connection = new SqlConnection(connect);
connection.Open();
SqlCommand command = new SqlCommand(SQL, connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
Sep 5 '07 #1
4 2700
Michael C wrote:
I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by disabling
connection pooling and the database can be dropped. Is there any way to
clear out the connection pool? I can't disable connection pooling as my app
uses it.
A dirty workaround is SP_WHO and KILL !

Arne
Sep 5 '07 #2
Have you tried SqlConnection.ClearPool(connection) ?

------------------------
Regards,
Steve
www.foxville.ch

On Sep 4, 10:12 pm, "Michael C" <m...@nospam.comwrote:
Hi All,

I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by disabling
connection pooling and the database can be dropped. Is there any way to
clear out the connection pool? I can't disable connection pooling as my app
uses it. Some sample code is below

Thanks
Michael

private void DoIt()
{
Execute("CREATE DATABASE ABC");
//remove this next line and it works
Execute("SELECT * FROM sysobjects", "ABC", false);
Execute("SELECT * FROM sysobjects", "ABC", true);
Execute("DROP DATABASE ABC");
}

private void Execute(string SQL)
{
Execute(SQL, "", false);
}

private void Execute(string SQL, string Database, bool NoPooling)
{
string connect = "Data Source=(local);Integrated
Security=SSPI;";
if (Database.Length 0) connect += "Initial Catalog=" +
Database + ";";
if (NoPooling) connect += "Pooling=False;";
SqlConnection connection = new SqlConnection(connect);
connection.Open();
SqlCommand command = new SqlCommand(SQL, connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}

Sep 5 '07 #3
Michael C (mi**@nospam.com) writes:
I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by
disabling connection pooling and the database can be dropped. Is there
any way to clear out the connection pool? I can't disable connection
pooling as my app uses it. Some sample code is below
Before you drop the database, run

USE db
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 5 '07 #4
<wi*****@googlemail.comwrote in message
news:11*********************@57g2000hsv.googlegrou ps.com...
Have you tried SqlConnection.ClearPool(connection) ?
Yikes! Sorry for the dumb question, I was assuming it wasn't going to be so
straight forward! I went straight to google but couldn't find the right
terms. I probably wouldn't have thought to look for static methods tho :-)

Thanks Arne and Erland also useful information.

Michael
Sep 5 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by BashiraInTrouble | last post: by
1 post views Thread by tgru | last post: by
7 posts views Thread by Wayne Brantley | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.