Why do my newly created tables get created as system tables?

[yesti]

Hello,
I am using SQL Server 2000 Enterprise SP2 and when I try to create a new table, it gets created as a system table, not a user table. I am then not able to delete the table: 'You tried to delete one or more system objects. They were not deleted'. The only way to get rid of them is to delete the entire database and restore from yesterday's backup. The new tables I tried to create are called 'Maintenance' and 'test' and both show up as System tables. I guess for now I will try to use them as a User table and see if it works?

 

[wepisan]

Hi,

When creating table try prefixing the owner name before the table.

For ex:

create table dbo.table1

create table sunny.table2 etc.,

Hope this helps.

Thanks,
Vaiyapuri Subramanian

 

[tlbroadbent]

Vaiyapuri,

The owner name doesn't determine if the table is a system table or user table. This is determined by a settings stored in the sysobjects table.

Yesti,

I've never seen the behavior you've described. Normally, a table is created as a user table and must be marked as a system table after creation. If I find anyting useful, I'll post it here. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[yesti]

Thanks all,

Yes, this behavior is definately strange. I restored a backup ghost image from earlier this month and things seem to be back to normal. Maybe a rogue hotfix caused the problem? I tried installing that cumulative Slammer patch and it failed twice so it probably left things in an inconsistent state. So much for patches?

 

[SQLBill]

Yesti,

When you did the tables, were you using

USE mytable

or USE master?

I'm not positive it would make a difference, but I'm thinking that it could.

-SQLBill

 

[yesti]

Not sure if this is what you mean, but here is what I did:

I was in Enterprise Manager: Console Root | Microsoft SQL Servers | SQL Server Group | ServerName\ServerInstance | Databases | DatabaseName | Tables.

I right clicked and chose 'New Table'. I made an identity field, then saved the table under some name. Then under 'Type' it showed the table as 'System' rather than 'User'. I then could not delete the table without dropping the database and restoring from a previous backup.

 

[yesti]

8.00.0686_enu_installer.exe was the culprit. This is the cumulative SP2 security patch. I just finished painfully rolling it back (rebuilding master is a real pain) and my user tables are once again showing up as such.

I was not sure what patch level my server was at, so I ran hfnetchk and all it said is I need SP3, which I am definately leery about installing after this.

Thanks!