November 21, 2007 at 3:49 am
Hi
We have a strange issue in our database:
There is a table, visible in the SQL Enterprise Manager, which cannot be deleted.
From within SQL/EM we get an error message:
Error 21776: [SQL-DMO] The name 'UserPrefTypes' was not found in the (null) collection. If the name is a qualified name, use [] to separate various parts of the name and try again.
From within SQL/QA when we execute the statement DROP TABLE UserPrefTypes we get the error message:
Server: Msg 3701, Level 11, State 5, Line 3
Cannot drop the table 'UserPrefTypes', because it does not exist in the system catalog.
I suspect there's something wrong with the ownership of this table: this table shows up in the QA as 'UserPrefTypes' rather than dbo.UserPrefTypes.
HELP!
Regards, Frank
November 22, 2007 at 12:22 am
Sounds like somebody may have created a schema in your database and mucked with the permissions... are you dbo (db_owner) of the database in question?
In EM does the table have a prefix/schema other than DBO visible? If yes, try issuing your drop statement including the prefix/schema name as well as the table name (e.g. drop table joe.table)
November 22, 2007 at 1:01 am
Thanks for your response. Yes, I'm dbo.
I'm pretty sure this phenomenon is caused by running a table creation script generated by the EMS DBcompare tool.
There are no other schema's other than 'dbo'. It seems that this table does not have an owner.
Eventually I've solved it by deleting this table from the sysobjects table directly.
(Requires setting a flag in EM)
Regards, Frank
November 22, 2007 at 11:29 pm
Would you like to share your CREATE TABLE script? How did you execute it (query window, or used another different tool) Are you doing remote desktop to the server and then executing it?
November 23, 2007 at 8:30 am
If SQL EM shows a table and an attempt to drop it fails, then you need to refresh SQL EM. This may mean closing it entirely and opening a new instance of SQL EM.
If you are on 2005, this is not supposed to happen but I doubt if MS fixed this feature. I would refresh SQL MS and if I can still see the table, I would close SQL MS and open a new instance.
If in either version, you still see the table, run this: "SELECT * FROM sysobjects WHERE name LIKE 'user%'
If you still find the offending table, restart SQL Server and run the same query. If it is still there and it won't drop, you have a corrupt database and should proceed from that assumption.
Good luck
d.
November 26, 2007 at 9:35 am
Thanks guys,
Still trying to reproduce this issue. Meantime I've got it fixed by deleting the table entry directly in the sysobjects table.
I think this issue was caused by an ill formed table creation script.
Thanks again!
Frank
November 27, 2007 at 4:32 am
Are you working in 2000 or 2005?
You posted to the 2k5 forum, but EM is a 2000 tool and in 2k5 you not supposed to be able to delete directly from the system tables anymore, so I'm a little confused. @=)
November 27, 2007 at 8:36 am
Oops. Reading never was my cup of tea :hehe:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply