Disconnecting from SqlServer Database from VB.net

edmonson

The connection to the app database is:

"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=work;Data Source=pcjordi2009\sqlexpress"

And when I connect to detach/attach I use:

"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data Source=pcjordi2009\sqlexpress"

Lost User

Looks good. Just tried from SQL Management Studio, one can simulate an open connection by simply opening a table in edit-mode.

ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
sp_detach_db 'AdventureWorks';

The first command sets the database to single-user mode, effectively disconnecting everyone else :)

I are Troll :suss:

edmonson

I've tested from Management Studio and it works properly. From my application, the problem continues. When I execute ALTER DATABASE... the DB enters in Single User Mode (An icon appears on Db List in Mabagement Studio, but when execute detach it fails reporting that Database is in use :(

Lost User

edmonson wrote:

When I execute ALTER DATABASE... the DB enters in Single User Mode (An icon appears on Db List in Mabagement Studio, but when execute detach it fails reporting that Database is in use :(

Strange.. Can you post the offending code here? I've tried it here this way, just to verify that it's actually possible;

string cs = "Server=.\\SQLEXPRESS;Database=master;Trusted_Connection=True;";
using (var con = new SqlConnection(cs))
using (var cmd = new SqlCommand())
{
con.Open();
cmd.Connection = con;
cmd.CommandText = @"ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
cmd.ExecuteNonQuery();
cmd.CommandText = @"sp_detach_db 'AdventureWorks';";
cmd.ExecuteNonQuery();
}

I are Troll :suss:

edmonson

Sure, this code works and the database is deatached succesful! but when I execute an attach again: - Database is attached successful (I can see on Management Studio) - I open a connection an it opens succesful. - But when I call an cmd.ExecuteReader to get data, then next error message appears: "Error at level transport when send the query to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe)" I've reviewed SQL configuration, and all seems good. If I restart the App all work succesful. Any idea, why the server process stopped ??

Lost User

edmonson wrote:

Any idea, why the server process stopped ??

Alas, no. Is it replicatable?

I are Troll :suss:

edmonson

Sorry, the message was: Sure, your code works and the database is deatached succesful! but when I execute an attach again(the same database): - Database is attached successful (I can see on Management Studio) - I open a connection an it opens succesful. - But when I call an cmd.ExecuteReader to get data, then next error message appears: "Error at level transport when send the query to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe)" I've reviewed SQL configuration, and all seems good. If I restart the App all work succesful. Any idea about it ?

Lost User

I don't know; does it happen every time after a reattach?

I are Troll :suss:

edmonson

Yes, every time I attached again (without exiting from application) and try to get data, the problem appears. But if I restart application and reattach then works fine ! (Strange behavior:-() If I find the solution I'll report it.

Lost User

edmonson wrote:

Yes, every time I attached again (without exiting from application) and try to get data, the problem appears.

Without exiting from the application; so something gets left behind, in either the application or SQL Server. When the application starts to fetch data, then the application will create a new Connection object, right? Thinking out loud; the database is re-attached, from within the application. Could it be that we disconnected an active DataReader during the detaching?

I are Troll :suss: