how can i make the database unusable to detach it from my application

Question

Post reply

how can i make the database unusable to detach it from my application

MsCode_Pro

Say Hey Kid

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

#381047

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 14 posts - 1 through 13 (of 13 total)

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

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

Please don't cross post. It just wastes people's time and fragments replies.

No replies to this thread please. Direct replies to:

http://www.sqlservercentral.com/Forums/Topic514284-145-1.aspx

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

Sorry man i just said that if i didn't get answer to this question in development forum, i can get it from backup forum , sorry again,,,,,,,,,, BUT if you don`t make me duplicate my question, why you didn`t answer me there, you just said "Why do you want to detach the database when you close the app? " and no one answer me So what shall i do except put my question in another forum :):):):)

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

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

Be patient. We're not paid to answer questions here.

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

John.Sansom SSCertifiable Points: 7335 More actions · Answer 4

Hi

Detaching a database requires exclusive access to the database. If the database that you want to detach is in use, before you can detach it, set the database to SINGLE_USER mode to obtain exclusive access.

For example, the following ALTER DATABASE statement obtains exclusive access to the AdventureWorks database after all current users disconnect from the database.

USE master;

ALTER DATABASE AdventureWorks

SET SINGLE_USER;

GO

Also, may I ask why you are detaching the database when activity has finished. It may be more appropriate to simply take the database offline?

Hope this helps.

John

John Sansom (@sqlBrit) | www.johnsansom.com

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

for details follow this link :

http://www.sqlservercentral.com/Forums/Topic514284-145-1.aspx

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

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

i try that but an exception message said :

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

the code is :

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

SqlConnection Mconn = new SqlConnection(Mconnection);

Mconn.Open();

SqlCommand Mcom = new SqlCommand("Alter database DigArch set single_user", Mconn);

Mcom.ExecuteNonQuery(); // here the exception

Mcom = new SqlCommand("Exec sp_detach_db 'DigArch', true", Mconn);

Mcom.ExecuteNonQuery();

Mconn.Close();

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

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

I GET IT :):):):)

thank you BigJohn your answer is apart of the solution just you have to make it like this :

ALTER DATABASE DigArch SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE DigArch SET MULTI_USER;

thank you again:)

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

John.Sansom SSCertifiable Points: 7335 More actions · Answer 8

You are most welcome.

John Sansom (@sqlBrit) | www.johnsansom.com

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

I'm thinking that your connection string had better be connecting you to some database other than the one you're detaching.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

yes, sure it connected to master database to be allowed to detach the database, but why if the connection has another job prevent another database to detached????

YoU CaN't LoSe WhAt YoU NeVeR HaD;)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

Good question... I've never seen that behavior before... Of course, it's been several revisions of SQL Server since I've actually had to write any GUI code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bledu SSChampion Points: 12335 More actions · Answer 12

I know you have found your solution, but as been asked before why do you want to detach your database?

Everything you can imagine is real.

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

Security Issue my friend;)

YoU CaN't LoSe WhAt YoU NeVeR HaD;)