How to allow Ad hoc updates in SQL Server system catalogs

By:   |   Comments (6)   |   Related: > SQL Server Configurations


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.


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.

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

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;

 Run EXEC sp_configure 'database mail XPs', 1; and reconfigure with override

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'

EXEC sp_configure 'allow updates'

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.


 Run Reconfigure and get the Msg 5808, Level 16, State 1, Line 1 Ad hoc update to system catalogs is not supported error

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

Changing the allow updates value to zero for a successful sp_configure execution
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh 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

Comments For This Article

Sunday, May 20, 2018 - 12:03:37 PM - kamran Back To Top (75983)



Cool Thanks

Tuesday, August 9, 2016 - 1:23:06 PM - ahmed Back To Top (43088)

 thanks alot , it solved my issue


Tuesday, January 19, 2016 - 7:06:10 AM - Milendra gupta Back To Top (40440)

 Thanks for this post.


Saturday, July 12, 2014 - 2:48:22 AM - Alok Bhatt Back To Top (32667)

We are facing issue to update svrname in

sysservers table in masters database , its showing error ''Msg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.'' , So if you can check there we need to allow the ad hoc gets enable.

Wednesday, March 26, 2014 - 6:34:38 AM - faraz Back To Top (29881)

I am using San drive in my SQ server to store dtabse. The issue is that when I am using maintenance plan to map the local server drive it is not visible.Kindly suggest the solution.

Wednesday, February 19, 2014 - 9:59:04 AM - samson Back To Top (29503)

Thanks for your assistance!

get free sql tips
agree to terms