User table getting created as system table

  • 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?

  • 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.


    Reconfigure with override;


    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 ( in ('DBA_Test', 'DBA_Test2'));


    Reconfigure with override;

    During a maintenance window:

      MSSQLSERVER restart, which implies SQLSERVERAGENT restart, fixed the problem.

    For some reason in Enterprise Manager, right click server name, Stop ran into an error, so I used

      Start | Control Panel | Administrative Tools | Services | right click MSSQLSERVER | Restart

    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