Error 259

Severity Level 15

Message Text

Ad-hoc updates to system catalogs not enabled. System Administrator must reconfigure system to allow this.

Explanation

This error occurs when SQL Server has detected an attempt to directly modify the system catalogs while the allow updates system configuration option is set to 0.

The allow updates system configuration option allows the system administrator to directly change the system catalogs.

Caution Severe problems can result from the direct manipulation of the system catalogs. Do not modify the system catalogs unless instructed to do so by your primary support provider.

Action

Before modifying any system catalogs, be sure that you have a valid backup of the database. For details on backup operations, see Chapter 12, "Backing Up and Restoring." Incorrect modification of the system catalogs can result in database corruption or data loss. Be sure to read all instructions carefully before attempting to alter any system catalog information.

If possible, restart SQL Server in single-user mode (/m) so that inadvertent modifications do not occur. For details, see Microsoft SQL Server Setup.

To modify system catalogs, use the isql command-line utility to perform the following steps. Only the system administrator can carry out this procedure.

1. Execute the sp_configure system stored procedure and then the RECONFIGURE WITH OVERRIDE statement to alter the allow updates system configuration setting. You will not need to restart SQL Server for the allow updates option to take effect. Type the following:

   sp_configure 'allow updates', 1
   go
   RECONFIGURE WITH OVERRIDE
   go

2. Verify that the allow updates system configuration option has been successfully changed. Type:

   sp_configure 'allow updates'
   go
   Results
   name    config_value    run_value
   allow updates    1    1

3. Make the specified changes to the system catalogs. It is always a good idea to use a begin transaction statement paired with a specific WHERE clause in your data modification statement to protect the system catalog data. For example:

   begin transaction
   data modification statement,
   including the specific WHERE clause
   go

   Important Never modify the syslogs table directly. Modification causes a looping effect and will completely fill your database, possibly making it unusable in the future.

4. Issue a select statement to double check that all data modification changes occurred as expected.
5. If changes took place correctly, then commit the transaction. If data modification did not occur correctly, issue the ROLLBACK TRANSACTION statement to reverse the system catalog changes. Type:

   rollback transaction
   go

6. Be sure to reset the allow updates system configuration option so that system catalog updates are no longer allowed. Type:

   sp_configure 'allow updates', 0
   go
   RECONFIGURE WITH OVERRIDE
   go

7. Verify that the allow updates system configuration option has been successfully reset. Type:

   sp_configure 'allow updates'
   go

   Results
   name    config_value    run_value
   allow updates    0    0