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

Warehouse-Scale Computing

Google Distinguished Engineer Luiz André Berroso talks first to Sigmod and later the ACM about Warehouse-Scale Computing.

You often hear how Google has multiple massive data centers across the world. He prefers to think of each one as a massive computer supporting a range of Internet services.

He introduces the concept of PUE (Power Use Effectiveness), a ratio that Google uses to measure how power efficient its computers are. It’s the amount of power spent on computation over the total amount of power spent on the computer.

I’ve seen all the Sigmod talk and I’m about halfway through the ACM talk.

Some good quotes (slightly paraphrased):

“I’m not sure what the cloud is, but I think it’s got something to do with my talk.”

“If you have things at the petabyte scale, you can think about them as a data center. A friend of mine recently got paged by the system because there were only a few petabytes of space left. That’s the kind of warehouse scale we’re talking about here.”