July 19, 2004 at 6:42 pm
I have a database attached to SQL2000 which appears to have no name or a space for the name and contains no tables, views or any thing else. However, in Enterprise Manager, it will not allow its deletion or renaming. How do I get rid of the db?
Thanks,
Mike W
July 19, 2004 at 6:58 pm
Interesting. How do you know there is a such database there? Can you confirm that by running sp_helpdb and paste the result here?
July 19, 2004 at 7:46 pm
I'll have to do that (sp_helpdb  when I'm back in the client's office tomorrow. In Enterprise Mgr, when listing attached databases, all the databases are shown including the one that has no name. I did do a search of the all the drives and there are no strange mdf or ldf files around.
July 20, 2004 at 12:40 am
Using Query Analyzer:
select name,dbid from master.dbo.sysdatabases
will list the name and database id (dbid) for all the databases on your server. Then using the dbid you can drop the database from the server with:
drop database QUOTENAME(db_name([dbid]))
eg: drop database QUOTENAME(db_name(14))
Julian Kuiters
juliankuiters.id.au
July 20, 2004 at 5:53 am
Ran sp_helpdb and it returned list of databases with the first one nothing under name, db_size null, owner administrator, dbid=11, created Nov 20,2003, status=suspect, updateability= read, write,...
Error msg are 1038, level 15, cannot use empty object or column names.
Drop database quotename(db_name(dbid)) doesn't work 'cause there is not db_Name and hence generates the error.
Question: can the db be dropped based in the dbid alone?
July 20, 2004 at 6:46 am
Can you detatch the db from EM ?
(rightclick and detatchdb)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 20, 2004 at 8:27 am
Negative on EM detachdb. Returns error mgs #21776 - [SQL-DMO] The name " was not found in the Database collection. If the name is a qualified name, use [] to separate various parts of the name and try again.
Any more suggestions?
July 20, 2004 at 12:13 pm
You might be able to update sysdatabases table in the master database with a new name and then try dropping it using the new name.
Good Luck!
July 20, 2004 at 1:43 pm
Already tried altering the master db sysdatabases and it would not allow the changes.
July 20, 2004 at 2:04 pm
Look up -
system tables, direct updates
in BOL. This will show you how to alter system tables
July 20, 2004 at 2:08 pm
After much trial and error, the command to get rid of the db with no name is as follows:
Execute sp_detachdb @dbname=N' '
GO
Note the space between the single quotes. Thanks for all the comments and help tips.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply