Drop Database Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Environment = .NET 1.1, & ADO.NET, MS Sql

I am trying to create a database and populate it with data. If there are no
errors, then it works well. How ever, if there are any errors, I want to drop
the database.

To drop the database I use the sql command 'Drop Database MyDatabaseName'.
I run this command on a connection to the master Database on the Sql Server
I get an error returned which is ' Can not drop database as it is still in
use'. If I use the SQL Server Management tools, I need to 'Force all active
connections to close' before I can delete the database.

How can I do this in TSql code?

Thanks

Peter Tewkesbury
 
Hi Peter,

Perhaps using something like:
USE MASTER
ALTER DATABASE <yourdatabase> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Note that this will kill all sessions and rollback active transactions.
-- do something here

USE MASTER
ALTER DATABASE <yourdatabasename> SET MULTI_USER
 
If you want to stay completely in T-SQL, write a procedure that does the work
and put it in SQL Server. The easiest might be to run command line with a
..NET Process object.

Otherwise, you need to separate out the different functions into different
routines and destroy the connection object each time.

1. Database Creation
2. Database objects
3. Database Destruction on fail

T-SQL might be the best, but I would aim at a different group than this to
ask questions.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
PJ,

Dispose your connection.
Create it new and cnnect to the Master (database="")
And try it again.

I hope this helps,

Cor
 
Cor Ligthert said:
PJ,

Dispose your connection.
Create it new and cnnect to the Master (database="")
And try it again.
Click to expand...

This procedure, of course, won't work unless you turn off connection
pooling.

David
 
Thanks, This has solved my proble just great.

--
Peter Tewkesbury


Miha Markic said:
Hi Peter,

Perhaps using something like:
USE MASTER
ALTER DATABASE <yourdatabase> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Note that this will kill all sessions and rollback active transactions.
-- do something here

USE MASTER
ALTER DATABASE <yourdatabasename> SET MULTI_USER


--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info


PJTewkesbury said:
Hi,

Environment = .NET 1.1, & ADO.NET, MS Sql

I am trying to create a database and populate it with data. If there are
no
errors, then it works well. How ever, if there are any errors, I want to
drop
the database.

To drop the database I use the sql command 'Drop Database MyDatabaseName'.
I run this command on a connection to the master Database on the Sql
Server
I get an error returned which is ' Can not drop database as it is still in
use'. If I use the SQL Server Management tools, I need to 'Force all
active
connections to close' before I can delete the database.

How can I do this in TSql code?

Thanks

Peter Tewkesbury
Click to expand...
Click to expand...
 
Back
Top