Changing a system table to a user table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Changing a system table to a user table

Author

Topic

brandonl
Yak Posting Veteran

58 Posts

Posted - 2004-12-21 : 23:58:23

Hi all,
I have a SMS database that somehow has one of the user tables showing up in sysobjects as a system table. This is causing additions to that table to fail.

How do I convert it from a system table to a user table? I tried:

UPDATE sysobjects SET xtype = 'U'
WHERE     (name = 'System_DISC')

But got the following error:
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

Can this not be done?

~BrandonL

brandonl
Yak Posting Veteran

58 Posts

Posted - 2004-12-22 : 00:01:16

Figured it out. I used the following:

SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE

and could then change it. Then I switched it back to 0...all ok.

~BrandonL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources