October 24, 2005 at 11:52 pm
Hi,
Iam Sridhar Babu. i have one issue regarding SQLServer2000.
iam installing .net application with backend MS SQL Server2000.
it is working fine.
when ever i acccess database through application, for each connecation one process will create. and when i close the application the process are closed automatically.
but the process are going to sleeping mode.
my problem is when i am going to uninstall the application
it is showing the error message.
Error 27506.
Cannot frop the database 'database name ' because it is currently in use[3702]
this is done with the help of Install Shield package.
Please help me how to do this problem through programmatically.
BABU
B.Sridhar Babu SE
Canarys automations P ltd
#135 7th Main, 4th Block
Jayanagar
Bangalore-560011
sridhar.bandalamudi@ecanarys.com Tel: +91-26539915-24
Mobile: +91-9449631468
October 25, 2005 at 5:51 am
The application may be using connection pooling of some sorts. Is this a fat client, windows service, or web application running on IIS?
October 25, 2005 at 5:44 pm
You said the process is going into sleeping mode - you checked that from sysprocesses from the SQL Server side ? If so, then the connections are not really being closed.
Since you are interested in dropping the database anyways, at the time of the un-install of the application, you can kill any open connections to the database that you are interested in dropping. Basically, write up a stored procedure that takes in a database name and then does the logic of getting the spids and killing them (I believe Brian Knight had one on this site...or you can write one easily on your own as well). At the time of the un-install, fire off that procedure (will reside in the master database) and then drop the database.
October 26, 2005 at 7:33 am
Understanding the application is a more practical approach than brute force, especially if a connection pool keeps openeing connections as you kill them.
Exmaple: If it is an web application using ODBC with connection pooling (it is built in) shutting down IIS would close all the connections establisahed by the connection pool and then the uninstall couls remove the database and web site. (assuming that is what it is trying to do)
October 26, 2005 at 11:05 pm
.NET applications when closing the connection in the application itself, will still retain the connection to the SQL Server. This is not a bug by Microsoft. It is part of the internal mechanics of the .NET Framework. This helps the .NET application reuse the existing (sleep mode) connection and execute the query. After a few minutes of inactivity with the connection, the .NET framework will physically close the connection to the SQL Server.
If you want to prevent this pooling from happening you must disable it in the Connection String.
But if you need to have those processes killed right away. Then 'kill ' will do the trick.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply