Unanswered: SQL-DMO (Kill database) & DROP DATABASE not solving my purpose
I was facing a problem as described below:
I am using ASP to fperform certain opeations on SQL Server 2000 database as following:
a. I am taking a 'Backup' of the specified database using SQL query through the ASP - Successful
b. Then I used SQL-DMO - 'Kill database' to drop the specified database (of which I have taken the backup earlier under a.) from SQL 2000 Server through ASP. On execution, the same is removing all the contents including the Tables, Views, Stored Procedures, FT Catalogs etc. But the database still continues to exist in 'Suspect' state...How to overcome this - PLEASE HELP???
Alternatively after step a., when I use 'DROP DATABASE <Database Name>' to to drop the specified database (of which I have taken the backup earlier under a.) from SQL 2000 Server through ASP, the same is returning a error No. 3702 - Cannot drop database because database is in use...The database continues to exist - How to overcome this and successsfully drop/ kill database - PLEASE GUIDE????
Re: SQL-DMO (Kill database) & DROP DATABASE not solving my purpose
I think the problem you are having is that you are trying to delete a database the you are connected to. That's a no no. You either have a connection string with "Data Source=yourdatabase" or an ODBC DSN pointing to the database.
To solve the problem, connect to the Master database, rather than directly to your database, and use explicit syntax. For example, say your database is named MyDatabase and your table is named MyTable. You can access it this way:
SELECT * FROM MyDatabase.dbo.MyTable
If you have a lot of SQL and want to make it easier to type, use an alias:
SELECT mt.* FROM MyDatabase.dbo.MyTable mt
In this case, the mt after the table name is the alias.
Anyway, since you are connected to Master rather than your database, the drop or kill should both work. Another good reason to do this: if your code tries to connect directly to your database, but a previous iteration of it has dropped the database, you will not be able to open the database connection at all, and your code will fail. Master will always be there.