This tip comes with a warning: You can cause serious and ireparable damage by updating system tables if you do the wrong thing - use this tip at your own risk!

If you find yourself in the position of needing to update one of the system tables in SQL Server 2000, this tip may help you.

By default (for good reason) the system tables are set to disallow updates to them directly; attempting to update them will result in the error below.

“Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.”

The error code for this problem is error 259.

On some rare occasions you may still wish to override this setting and make changes to the tables, to do this follow the process below:

1. Make a full backup of your databases (including the master database)
2. Run the commands:

sp_configure ‘allow updates’, 1
GO
RECONFIGURE WITH OVERRIDE
GO

3. Make your changes to the system tables
4. Run the commands:

sp_configure ‘allow updates’, 0
GO
RECONFIGURE WITH OVERRIDE
GO

5. Make a full backup of your database
6. Test your changes had the desired effect.

Please do be careful with the changes you make to the System Tables in SQL Server 2000 as it is very possible to damage your system beyond repair if you are not sure what you are changing.

This tip was brought to you by the team at Techita.

This entry was posted on Tuesday, April 17th, 2007 at 9:34 pm and is filed under SQL Server 2000. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.