Allow Updates Option for SQL Server 2005

Posted on by

SQL Server 2005 doesn’t have the allow updates option. So, if you execute:

sp_configure ‘allow_updates’,1

and then if you execute reconfigure, you would get the following error:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

After this all changes to the sp_configure settings followed by a reconfigure would yield this error. To rectify this, you will have to change the allow_updates option back to 0 and run reconfigure. As per SQL Server 2005 Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.

Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

So, if in case you use allow_updates in any script in SQL Server 2005, please refrain from doing so. Updates to the system catalogs are not permitted in SQL Server 2005 and any attempt/changes made to the System Resource database would get you into an unsupported scenario.

2 thoughts on “Allow Updates Option for SQL Server 2005”

  1. I am using SQL server 2008 and have the same problem. Does SQL server 2008 support direct updates to tables ?

    Like

  2. Direct updates to system tables are not allowed starting from SQL Server 2005. Any such changes are undocumented. Such modifications would render the SQL instance in an unsupported scenario and should be done only under the supervision and recommendation of a Microsoft SQL Support professional. We do not recommend any such changes under drastic circumstances.

    Like

Comments are closed.