I am having problems deleting a SQL Server connection after I reset and recreate this database, and the next time I try to run a command against a new connection in the same database, I get:
There was a transport layer error while sending a request to the server. (provider: shared memory provider, error: 0 - no process is on the other end of the channel.)
Here is the TCP version (if I try to connect to another server)
There was a transport layer error while sending a request to the server. (provider: TCP provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Following are the steps to reproduce the problem:
- Open a database connection and run the sql command
- Drop database
- Re-create the database
- Open a new connection to the same database and try to run a command against it.
Result: I get an exception
Here is the code:
using (var conn = new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=DBNAME;Integrated Security=True"))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE ...";
cmd.ExecuteNonQuery();
}
string sql = "Alter Database DBNAME set single_user with rollback immediate drop database DBNAME";
var server = new Microsoft.SqlServer.Management.Smo.Server(".");
server.ConnectionContext.ExecuteNonQuery(sql);
server.ConnectionContext.Disconnect();
sql = File.ReadAllText("PathToDotSqlFile..."));
server = new Microsoft.SqlServer.Management.Smo.Server(".");
server.ConnectionContext.ExecuteNonQuery(sql);
server.ConnectionContext.Disconnect();
using (var conn = new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=WER_CONFIG;Integrated Security=True"))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE ...";
cmd.ExecuteNonQuery();
}
The error occurs in the line 'cmd.ExecuteNonQuery ()' at the very end. It seems that even when creating a new connection every time I connect, the sql server keeps track of something (or maybe the ADO.net code), where the next time I ask for connections, it gives me the one that is already in use or was closed on the server side. He does not understand that the server is closed (presumably because the database is related to the fact that it was reset) until you try to execute another command with it.
, , , , . , .
:
ProcessStartInfo info = new ProcessStartInfo("sqlcmd.exe", " -e -E -S . -Q \"Alter Database DBNAME set single_user with rollback immediate drop database DBNAME\"");
var p = Process.Start(info);
p.WaitForExit();
info = new ProcessStartInfo("sqlcmd.exe", " -i " + PathToDotSqlFile);
p = Process.Start(info);
p.WaitForExit();
.
SqlConnection , , ? ?
: SqlConnection.ClearPool() , = false, .