Solved

sp_detach_db

Posted on 2004-09-21
7
2,634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Expert Comment

by:vidnan123
ID: 12110471
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
ID: 12110488
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
ID: 12110563
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
ID: 12110581
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
ID: 12110584
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
ID: 12110614
@oleqP thanks, it works
@Hillaire ellegant :)
0
 

Expert Comment

by:intermediagroup
ID: 28893144
if using management studio take the database offiline first.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

738 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