A couple of months ago, I ran into a strange issue. Whenever I fired the “RECONFIGURE” statement, the SQL Server returned an error message – “Ad hoc update to system catalog is not supported.” I was able to trace this to the fact that somebody had set the “allow updates” setting on the server to 1. This setting is 0 by default on a new install of SQL Server. This setting was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. This setting is now deprecated since SQL Server 2005 and all SQL Server installations come with this set to 0 by default. Per Books On Line (read the BOL help on this option here), this setting has no effect, except that the RECONFIGURE statement will fail, just as it was doing in my case.

The root cause in my case was that it was a shared environment and because this configuration switch lies in full public view when sp_configure is run, some novice developer attempting to learn SQL Server inadvertently set the switch but forgot to reset the same. You can read about my experience here: http://beyondrelational.com/blogs/nakul/archive/2011/02/14/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx

Just recently, I ran into the problem again – this time on a friend’s test configuration. That’s what prompted me to open a case in Microsoft Connect. You can find the case filed here: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

My belief is that if something is deprecated, and is not meant to be used – it should not be in obvious view. We don’t keep unused stuff on the front lawns – we stuff it away in the garages and therefore, this option should at least be made an advanced option.

If you agree with me, please vote for fixing this at: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

Until we meet next time,

Be courteous. Drive responsibly.