February 5, 2002 at 8:52 am
Hi there!
I seem to be having a problem trying to DROP a database!
It's a simple test database on SQL Server v 7, which I have created using T-SQL (via Visual FoxPro).
The ODBC connection is set as the 'sa' login and password.
If I attempt to DROP the database I get a 'Database is in use' error. If I try to drop a table within the database I receive the message 'Tables are not listed in the system catalog'!
Basically I am looking for a way of dropping a database (regardless of any transaction going on at the time), ie immediately dropping any users and then the database etc.
This all needs to be done via T-SQL.
Hope someone can help!
February 5, 2002 at 9:00 am
quote:
The ODBC connection is set as the 'sa' login and password.
I suggest dropping this ODBC connection to the database and waiting for it to time out or restarting the server. The ODBC shouldn't be required if you are dropping the database. If the ODBC will not drop then stop the web server and drop it. then restart web server.
I would advise not using sa account to develop in as the ODBC connection you created had full rights on your SQL Server.
Even if you then meant to craete a user for the live environment with less rights. your testing in the dev enviro would be inconsistemnt with the live enviro.
Hope this is usefull
Mr Peter Livesey
Mr Peter Livesey
February 5, 2002 at 10:55 am
I typically change the db to dbo only, then run sp_who to check for connections and kill them as needed. That gets rid of current connections and prevents new ones.
Andy
February 5, 2002 at 11:25 am
Be sure you are not connected to the server in that database. If so, you will not be able to drop the db because you have a "shared lock" on the database.
Steve Jones
February 5, 2002 at 11:29 pm
I find that detaching the database can help.
If you use EM you can warn the users connected to the DB and force them off.
Once detached, just delete the files for that database.
February 6, 2002 at 5:08 am
Just a note to say many thanks for the responses! They have been very helpful and very much appreciated.
February 6, 2002 at 5:14 am
try to connect to master db and with that connection drop your database!
quote:
Hi there!I seem to be having a problem trying to DROP a database!
It's a simple test database on SQL Server v 7, which I have created using T-SQL (via Visual FoxPro).
The ODBC connection is set as the 'sa' login and password.
If I attempt to DROP the database I get a 'Database is in use' error. If I try to drop a table within the database I receive the message 'Tables are not listed in the system catalog'!
Basically I am looking for a way of dropping a database (regardless of any transaction going on at the time), ie immediately dropping any users and then the database etc.
This all needs to be done via T-SQL.
Hope someone can help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply