how can i fully close all realated things to a database to prevent an Exception says "datebase is used" when i detach it by T-SQL

Question

how can i fully close all realated things to a database to prevent an Exception says "datebase is used" when i detach it by T-SQL

MsCode_Pro

Say Hey Kid

Points: 690
More actions
June 10, 2008 at 4:47 am

#94204

ok , if you confused i'll explain, i want when i close my application, the database which i work with detached from the server OK, So when i write the detach quiery for the database in the dipose method of the main form or after the Application.Run(new Form1()); ,, there was an exception says that the database under usage or still used, and that after closing all connections related to this database and closing all connections to the instance of sql server ,
so how can i fully close or make the database unusable to be allowed to detach it????????????????
YoU CaN't LoSe WhAt YoU NeVeR HaD;)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why do you want to detach the database when you close the app?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MsCode_Pro Say Hey Kid Points: 690 More actions · Answer 2

just for security issue,,, So is there any way to solve this problem?? whay that happened whatever i wanna detach the DB or not, why that happened????

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm still not understanding the reason for the detach. If your security is set up properly, a detached database is no more secure than an attached one.

Still....

You're probably getting the error because the connection that you're using to detach the database is using that database.

Make sure that the connection through which you issue the detach database command is using some other DB, like master.

Check that all other connections using that database are closed, including replication (if applicable), backups and other background jobs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MsCode_Pro Say Hey Kid Points: 690 More actions · Answer 4

first thank you for you help and your patient, second let me explain more and more :

First, i create a database from an existing mdf and lfd files, and that by command within VS not within SQL OK, NOTE:"untill now if i make a detach process for this DB it will success"

Second, after the creation of the database i use it to retrieve the number of its rows in a datatable without using Connection.open() because i use an adaptar which manage the opening and closing of the database, then i retrieve the value from datatable in an integer variable,

"NOTE" after i used the database and exit the scope of code, what i know is that the Adapter opened the connection and will close it, But in this moment i can't detach the database untill from SQL because it is under use !!!!!!!!!!!!!!!!!!!!

SO, i try to open the connection and close it manualy to manage that instead of adapter and clear the datatable after usage and i still can't detach the database,

NOTE, that there is no any connections opened except a connection uses master database to create the database and i open and close it after usage because i use ExecuteNoneQuery(), So my friend did you see that i make a mistake or what shall i do from your experience, finally thank you for your patient 🙂

THE CODE :

// Attaching the Mdf file

string Mconnection = ConfigurationManager.ConnectionStrings["MotherConnection"].ConnectionString;

SqlConnection Mconn = new SqlConnection(Mconnection);

SqlCommand Mcom = new SqlCommand("Create database DigArch on primary (filename = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DigArch.mdf') log on (filename = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\DigArch_log.ldf') for attach", Mconn);

Mconn.Open();

Mcom.ExecuteNonQuery();

Mconn.Close();

// untill now i can make a detach process

THEN :

SqlConnection Conn = New SqlConnection();

conn.ConnectionString = connection;

cmd = new SqlCommand("select Max(FileID) as 'NOFiles' from FileInfo", conn);

da.SelectCommand = cmd;

DataTable TesDt = new DataTable();

da.Fill(TesDt);

try

{

h = int.Parse(TesDt.Rows[0][0].ToString());

}

catch

{

// when the database is empty

h = 0;

}

// here i can`t make any thing about detaching the database

// the code of detaching :

string Mconnection = ConfigurationManager.ConnectionStrings["MotherConnection"].ConnectionString;

SqlConnection Mconn = new SqlConnection(Mconnection);

SqlCommand Mcom = new SqlCommand("Exec sp_detach_db 'DigArch'", Mconn); // i pass true value in the 2 arg but still failed

Mconn.Open();

Mcom.ExecuteNonQuery();

Mconn.Close();

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I'm no C# expert I'm afraid. I would guess that the data table keeps the connection open until it's destroyed.

If you use a SQL querying tool (management studio) what connections do you see?

SELECT er.session_id, blocking_session_id, wait_type,

wait_time, wait_resource, database_id, login_time, login_name, command

FROM sys.dm_exec_requests er

INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MsCode_Pro Say Hey Kid Points: 690 More actions · Answer 6

the connection string like that :

Data Source = ServerName; Initial Catalog = DatabaseName; user ID = ******; password = ******

and i use T-Sql as normal

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Not asking about the connection string.

If you open a SQL Server query tool, like management studio, and query the open connections to the server, what do you see?

Stick a breakpoint in your C# code just before the detach would happen and check the active connections at that point.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MsCode_Pro Say Hey Kid Points: 690 More actions · Answer 8

Thank you man i found the solution :):):):)

the solution at this link :

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Not a solution, a workaround. There's some connection somewhere that's getting forcefully closed by the server when you do that. If it's in your app and you then try to use that connection, you'll get an error.

Still, if that's good enough, cool.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MsCode_Pro Say Hey Kid Points: 690 More actions · Answer 10

that what i wanna do man , i wanna FORCE every connection in my application to detach the database, and when i used that i success detaching my database, and after i used that when i close the application so i will never do some thing after that:)

YoU CaN't LoSe WhAt YoU NeVeR HaD;)