how to attache and detache db files with t-sql ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to attache and detache db files with t-sql ?

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-09-28 : 13:30:25

Hello all !

I would like to attache / detache db files with t-sql - could someone help me ?

Thanks a lot for any help !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-28 : 13:52:50

Look for sp_attach_db and sp_detach_db

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-09-28 : 15:45:25

Hi sodeep !

Thanks for your kind response.

Now I have another problem :-)

When I execute this through the Microsoft SQL Server Managmenet Studio Express the db gets detached without problems :

ALTER DATABASE AttendanceRecorder SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; exec sp_detach_db @dbname='AttendanceRecorder', @keepfulltextindexfile='false';

But when I do the same thing (with the same login) in my vb.net Windows application, it says : "Cannot detach the database 'AttendanceRecorder' because it is currently in use. "

Do you have any idea where this could come from ?

Thanks a lot for your feedback !

Regards,
fabianus

my favorit hoster is ASPnix : www.aspnix.com !

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-28 : 16:58:38

Someone's got a connection to the database. There must be noone using the DB for it to be detached. That includes the query window that you're using to run the detach. Run it from the master db rather.

--
Gail Shaw
SQL Server MVP

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-09-29 : 14:11:56

Hi GilaMonster,

I am not sure to understand what you mean. But as said, the exactly same t-sql works when I run it in "Microsoft SQL Server Managmenet Studio Express" but doesn't when I execute it from my .NET application with exactly the same login.

Do you have an idea where this could come from ?

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 15:20:36

Change you default database in your connection string for the .NET application to master for this operation.

E 12°55'05.63"
N 56°04'39.26"

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-10-07 : 11:57:26

Hello Peso,

thank you for your feedback !
Unfortunatly this doesn't solve my problem eather :-(

Could you tell me the diffrence between this :
ALTER DATABASE AttendanceRecorder SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; exec sp_detach_db @dbname='AttendanceRecorder', @keepfulltextindexfile='false';

and detaching with the Microsoft SQL Server Managmenet Studio Express by clicking "Drop connections" ?

how could I "Drop connections during the t-sql stated above ?

Thanks a lot for any additional feedback !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-07 : 12:04:24

see this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-10-08 : 17:11:41

Dear Peso, africa and others,

thanks a lot for your kind support ! Your solution solved my problem ... and I am HAPPY :-)

See you soon,
Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources