Shilpa (sh*************@unisys.com) writes:
I am using a .Net application
Here are the sequence of actions
1) Drop all connections to a database by using the ALTER DATABASE
command
2) Drop the database
3) Recreate the database and populate it with some data
4) Select a value from a table using a where condition (no joins).
Command Object is used to perform this operation and fill a datset
through a data adapter.
All these happen through the .Net application. Before step 4, a
connection is successfully established and the status says 'Open'.
Can you get some clues from the above and suggest me a solution?
And after step 4, you get the transport-level error?
I guess the problem is the connection pool. Recall that when you close
a connection from your .Net app, the connection to SQL Server is not
really closed. Instead ADO .Net lingers to it, for another 60 seconds,
and if the applicaiton opens a new connection during this time, it will
reuse the connection.
When you issue ALTER DATABASE WITH ROLLBACK IMMEDIATE, SQL Server will
terminate these connections. However, this is not something that ADO .Net
can detect. So it gives you a connection which it thinks is good, but
which isn't.
There are a couple of ways to approach this. One is to configure the
connection pool, so that all connections are dropped when you drop the
database. Another is to change the connection string, each time you
drop the database, as different connection strings gives different pools.
I would suggest that the best way, though, is simply to set up an exception
handler, so that you simply trap this error, and then try to reconnect.
For the fine details on how to implement any of the above, I would suggest
that microsoft.public.dotnet.framework.adonet is a better venue, as the
issue involved here is more one with SqlClient rather than SQL Server.
--
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 
