December 6, 2010 at 5:11 pm
I am having a wierd problem with SQL Server 2000 for the past 1 week. Whenever I create a new table, it flags the table as a System Table in the Enterprise Manager but still shows under the User Tables in Query Analyzer. It doesn't let me delete the table from Enterprise Manager but I can use a DROP TABLE statement from Query Analyzer. However, I cannot rename the table either from enterprise manager or using sp_rename from Query Analyzer. It does the same with temporary tables too. This problem happens on all the databases I have on that SQL Server installation. The sysobjects table mentions the table as a user table (xtype = 'u') Any ideas?
July 4, 2012 at 5:29 am
I realise that this is an old thread, well single message, on an old release, but I just ran into it.
So just in case it helps anyone else, here goes.
In my case, both Type and XType from dbo.sysObjects were both 'U'. However, IsMsShipped, also in dbo.sysObjects, was set, which caused the table to be a system table.
Since it was a combined development and production server, (ugh, don't ask), this snippet fixed it during the day:
-- Allowing direct updates is dangerous.
Exec SP_CONFIGURE 'ALLOW UPDATES', 1;
Reconfigure with override;
Go
Update sO
Set sO.Status = sO.Status & 0x7FFFFFFF
-- Use sO.Status since neither Set sO.IsMsShipped = 0 nor Set sO.Category = 0 will work.
from dbo.sysObjects sO
where (sO.name in ('DBA_Test', 'DBA_Test2'));
Exec SP_CONFIGURE 'ALLOW UPDATES', 0;
Reconfigure with override;
During a maintenance window:
For some reason in Enterprise Manager, right click server name, Stop ran into an error, so I used
which worked fine.
A much better answer is here:
In particular, use:
Exec master.dbo.sp_MS_upd_sysobj_category 2
Your mileage may vary.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply