Ad hoc update to system catalogs is not supported.

Today someone handed over to me a server that was partially set up so that I could finish the task.

One thing yet to configure was the Database Mail settings. We use execute script on each new server to configure Database Mail so that it can send server alerts to our inboxes.

The first part of the script is simple enough. It reconfigures SQL Server to enable the database mail stored procedures.

The batch looks like this:

EXEC msdb.dbo.sp_configure
@configname = ‘Database Mail XPs’,
@configvalue = 1;

RECONFIGURE;

Today the RECONFIGURE statement failed with an error I’ve never seen before. The output of the batch looked like this:

Configuration option ‘Database Mail XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

You normally see an error like this when you try to do something silly like directly modify the system tables:

DELETE FROM sys.objects;

The server will respond with a similar but distinct error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

This error is a good thing. It protects us from making careless updates that trash the system.

But why should I see this error when I call sp_configure? By using the procedure, I’m asking the server to modify system tables on my behalf.

Nakul Vachhrajani at [Beyond Relational](http://beyondrelational.com/modules/2/blogs/77/posts/11282/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx) has faced the same problem, and documented how to fix it.

There is a deprecated SQL Server setting called ‘[allow updates](http://msdn.microsoft.com/en-us/library/ms179262.aspx)’ that is set to 0 by default. When it is set to 1, it causes the RECONFIGURE statement to fail.

To fix the issue, I executed this batch on the server:

EXECUTE sys.sp_configure
@configname = ‘allow updates’,
@configvalue = 0;

RECONFIGURE;

It produced the usual output message, and no errors:

Configuration option ‘allow updates’ changed from 1 to 0. Run the RECONFIGURE statement to install.

Why didn’t the reconfigure statement raise an error this time? I’m unsure, but it looks like the effect of sp_configure takes place immediately for this setting.

Querying sys.configurations shows ‘allow updates’ is now set to 0:

SELECT *
FROM sys.configurations
WHERE name = N’allow updates’;

configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
102 allow updates 0 0 1 0 Allow updates to system tables 1 0

Now I can set up Database Mail!

Advertisements

Leave a Reply

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