Cannot drop database

[Michael C]

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();
}

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

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

[wisccal]

Have you tried SqlConnection.ClearPool(connection) ?

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

On Sep 4, 10:12 pm, "Michael C" <[e-mail]:

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();
}

[Erland Sommarskog]

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

[Michael C]

<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