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