Solved

sp_detach_db

Posted on 2004-09-21
7
2,549 Views
Last Modified: 2012-08-13
Hi,
i need to detach database using osql, even if it's in use.
when detaching database from EM, window appears with option to clear current connection.
how can this be done using T-sql?

0
Comment
Question by:ill
7 Comments
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
Use the following SQL syntax.

sp_detach_db

For example,

EXEC sp_detach_db 'pubs', 'true'

This statement detatches the pubs database with the option to not 'update statistics' after detaching the database

0
 
LVL 12

Author Comment

by:ill
Comment Utility
i need to close acive connections first. that's the problem.
--------------
Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'rbweb2' because it is currently in use.
0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 50 total points
Comment Utility
for killing use

DECLARE @Proc varchar(30)

DECLARE Processes CURSOR FOR
SELECT prc.spid
FROM master.dbo.sysprocesses prc
INNER JOIN master.dbo.sysdatabases db
      ON prc.dbid = db.dbid
WHERE
      db.name = ' HERE DB NAME'

OPEN Processes
FETCH NEXT FROM Processes INTO @Proc

WHILE @@FETCH_STATUS = 0
BEGIN
      SET @Proc = 'KILL ' + @Proc
      EXEC(@Proc)
      FETCH NEXT FROM Processes INTO @Proc
END

CLOSE Processes
DEALLOCATE Processes
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
Comment Utility
You could also do it as follows

alter database <dbname> set single_user with rollback immediate

<....... do your stuff here ..........>
-- also make sure to issue a "use master" before calling sp_detach

alter database <dbname> set multi_user with rollback immediate
0
 
LVL 11

Expert Comment

by:ram2098
Comment Utility
This is what the SQL Server also internally do when you execute "clear" command..so, to script it..it is like this... (Example for PUBS database)
But, unless it is very much required. I do not recommend to query the system tables directly.


use master
GO

declare @spid int

declare curprocesses cursor
for select spid from master.dbo.sysprocesses WHERE (dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE (name = 'pubs')))

open curprocesses
fetch next from curprocesses into @spid

while (@@fetch_status = 0)
  begin
   exec ('kill ' + @spid)
   fetch next from curprocesses into @spid
  end

close curprocesses
deallocate curprocesses

EXEC sp_detach_db 'pubs'

0
 
LVL 12

Author Comment

by:ill
Comment Utility
@oleqP thanks, it works
@Hillaire ellegant :)
0
 

Expert Comment

by:intermediagroup
Comment Utility
if using management studio take the database offiline first.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now