How to allow Ad hoc updates in SQL Server system catalogs

By: Manvendra Singh   |   Updated: 2013-02-11   |   Comments (6)   |   Related: > SQL Server Configurations

Problem

We were tasked with enabling SQL Server Database Mail on a few SQL Server instances. When we were running RECONFIGURE along with the "database mail xps" option through sp_configure, we were not able to run the RECONFIGURE option to enable the changes. We were getting this error message "Msg 5808, Level 16, State 1, Line 1 Ad hoc update to system catalogs is not supported."  This tip is intended to shed some light on how to avoid and rectify this issue.  Check out this tip to learn more.

Solution

Option 1 - sp_configure with the reconfigure with override option

We were running the below command (sp_configure 'Database Mail XPs', 1 GO reconfigure GO) to enable the "Database Mail XPs" feature.  Unfortunately the "reconfigure" command was not running successful and throwing the "Msg 5808, Level 16, State 1, Line 1 Ad hoc update to system catalogs is not supported." error.  See the screen shot below as a point of reference.

Before moving on, let's discuss what RECONFIGURE is and why we use it after updating/configuring any value in sp_configure. As per Books Online, "RECONFIGURE updates the currently configured value of a configuration option changed with the sp_configure system stored procedure. Because some configuration options require a server stop and restart to update the currently running value."

To resolve this issue we used the "with override" option of the RECONFIGURE command and this time it was successful.

EXEC sp_configure 'database mail XPs', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Option 2 - sp_configure 'allow updates', 0

There is one more option to fix this issue ("Msg 5808, Level 16, State 1, Line 1 Ad hoc update to system catalogs is not supported.") and that is by changing the config_value of the "allow_updates" configuration option to 0 in sp_configure. "Allow updates" was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. There is no use of this setting with SQL Server 2005 and beyond.  This is because with SQL Server 2005 and beyond, direct updates to the system tables are not supported. The default value of this setting is 0 which should not be changed otherwise the allow updates option will cause the RECONFIGURE statement to fail.

Note: Per Books Online "This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible." so the best option is to use solution number 1 to rectify this issue.

Steps to rectify the RECONFIGURE statement issue

Step 1: First check the config_value of the "allow updates" configuration option. If its set to 1, change this value to 0, which is the default value.

EXEC sp_configure 'allow updates'
GO

Step 2: As you can see SQL Server is not running with the default value. Now you can run the RECONFIGURE command to check the value or reproduce the error ("Msg 5808, Level 16, State 1, Line 1 Ad hoc update to system catalogs is not supported.") because someone has changed the value of allow updates.

RECONFIGURE
GO

Step 3: Now you can see the above error and it's because of not setting the default value. Now go ahead and change it to the default i.e. 0. You can see in the below screenshot that it executed successfully with the reconfigure command.

sp_configure 'allow updates',0
go
reconfigure
go

Next Steps

• Be careful while making any change in any value in sp_configure. This might command may impact your SQL Server instance unexpectedly.
• Read more tips on SQL Server.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips