Microsoft Connect Case – sp_configure – “allow updates” should be an advanced option – add your vote to fix


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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s