Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

sql

force sql to ignore dependancies

Is there any way to force sql to allow a table delete even if it has
dependancies? I just need to drop and recreate the table with ansi_nulls
enables so the table be absent briefly.
Thanks,
Jim"Jims" <biz@.neocasa.net> wrote in message
news:eqd3EckiGHA.3496@.TK2MSFTNGP04.phx.gbl...
> Is there any way to force sql to allow a table delete even if it has
> dependancies? I just need to drop and recreate the table with ansi_nulls
> enables so the table be absent briefly.
See my reply to your next post. This approach makes no sense.

force sql to ignore dependancies

Is there any way to force sql to allow a table delete even if it has
dependancies? I just need to drop and recreate the table with ansi_nulls
enables so the table be absent briefly.
Thanks,
Jim"Jims" <biz@.neocasa.net> wrote in message
news:eqd3EckiGHA.3496@.TK2MSFTNGP04.phx.gbl...
> Is there any way to force sql to allow a table delete even if it has
> dependancies? I just need to drop and recreate the table with ansi_nulls
> enables so the table be absent briefly.
See my reply to your next post. This approach makes no sense.sql

force sql to ignore dependancies

Is there any way to force sql to allow a table delete even if it has
dependancies? I just need to drop and recreate the table with ansi_nulls
enables so the table be absent briefly.
Thanks,
Jim"Jims" <biz@.neocasa.net> wrote in message
news:eqd3EckiGHA.3496@.TK2MSFTNGP04.phx.gbl...
> Is there any way to force sql to allow a table delete even if it has
> dependancies? I just need to drop and recreate the table with ansi_nulls
> enables so the table be absent briefly.
See my reply to your next post. This approach makes no sense.

Force Drop Database

Hi everybody,

I would like to know if there would be any special way to force drop a database from an ASP.NET page.

When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout.
In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.

Thanks in advanceWhy must you be able to "drop" a database while it's still in use?|||Just imagine an application that creates a database for each customer, which contains personalized data for another application. Then just imagine that one customer doesn't pay his/her month fee and we wanted to delete his/her data, but he is still using it. Of course because we are the application admins, we have to be able to do that, even if database is still in use.

I hope this answer convince you and could tell me if there's a solution.

Thank you|||Personally, I wonder what happens if just after you drop the database, the user's payment arrives. To handle this scenario, I would have some other way of controlling a user's access to the system rather than deleting the database. Perhaps a flag in the database to tell you if the user should be able to access the database. I actually wonder about one database per customer, but that is an entirely different issue...