Ad hoc update to system catalogs is not supported - System Table Modified


  • Hi
    I do an operation on SQL Server into the Database MSDB. This operation was to Add a column to the table "sysoperators". So when the screen open, I removed this "new column" and I put nvarchar(120) for the column "email_address".  This is my error. When I did this, the system seem to have detect an alteration. So the table "sysoperators" is now NOT a SystemTable.
    Is it possible to return this table into the SystemTable ? The update cannot be done (Ad hoc update to system catalogs is not supported) even when I'm on the console on the server when I use "ADMIN:ServerName". (I also use the commande sp_configure 'allow_update',1 RECONFIGURE...)
    For the moment, this issue don't cause to me a real problem, but it is not clear on the server...

    Wednesday, September 23, 2009 2:19 PM

All replies

  • What gives you the impression that sysoperators is no longer a system table?

    You shouldn't be updating system tables anyway, but I'm sure you already know that....

    Aaron Alton |
    MCITP: Database Administrator
    Thursday, September 24, 2009 2:29 AM
  • Yes, I know, but I tried and now, I know it more than ever :)

    First, when  I browse with SQL Manager, this table don't appear in the "System table"..
    Also when I do :



    * from Sys.tables

    This table is not "is_ms_shipped" = 0

    Then, when I look into, I don't remember exacly, but another table that seem to be use for update by MS, it doesn't appear.

    And really, it's my modification that cause the "new version" of this table...

    Thursday, September 24, 2009 8:07 PM
  • Hi Daniel

    What version of SQL are you using?

    Ideally, you would revert to backup to correct this issue. Alternatively, you could try resetting the system status (after reverting your change).

    exec sys.sp_MS_marksystemobject 'objectname'

    Haven't tried this for a table, only a proc. So cannot guarantee that it will work for you.



    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Friday, September 25, 2009 8:06 AM
  • Hi,

    You could use the sp_MS_marksystemobject stored procedure to mark a ojbect as system object, such as sp_MS_marksystemobject 'dbo.sysoperators'. If you have any backup for your system databases, restorsing the backup is also an option. However, we strongly recommand you not modify any system objects.

    If there are any more questions, please let me know.

    ***Xiao Min Tan***Microsoft Online Community***
    Friday, September 25, 2009 8:11 AM