Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

How to allow Ad hoc updates in SQL Server system catalogs

MSSQLTips author Manvendra Singh By:   |   Read Comments (3)   |   Related Tips: More > 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.

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;
GO
RECONFIGURE WITH OVERRIDE;
GO


 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'
GO


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.

RECONFIGURE
GO


 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
go
reconfigure
go


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.


Last Update: 2/11/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, July 12, 2014 - 2:48:22 AM - Alok Bhatt Read The Tip

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 Read The Tip

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 Read The Tip

Thanks for your assistance!




 
Sponsor Information