At virtually every company I have worked at, documentation has always lagged behind the product. This is especially true for products that are used only internally. It’s understandable to some extent. Many IT departments are understaffed and the team is spending the majority of their time either putting out fires or working to keep everything running smoothly. But for commercially available software, I have higher expectations, especially when that documentation is A) from a company as large as Microsoft and B) is available on-line, where changes can be made instantly and there are no printing delays to worry about. Documentation that is just flat out wrong not only creates headaches for people who rely on that documentation to use the software, but it also results in wasting the time of people who are already short on that precious commodity.

Let me share my latest experience with this problem in case someone else runs into the same situation. While working on my replication test project, I was using code that modified system tables, so I used this command:

The project involved working with  both a SQL 2000 machine and a 2008 machine. The command worked on the 2000 machine and seemed to work on the 2008 machine – at least, the command didn’t generate an error. I forgot that SQL Server 2008 doesn’t allow direct modification of system tables (actually, neither does 2005), so the command was turned into a NOP. According to this Microsoft TechNet post, this command was disabled in SQL 2005. It states:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect. Starting with SQL Server 2005, direct updates to the system tables are not supported.

The exact same statement is present on the TechNet page for SQL 2008. Ok. So I went about continuing my testing using other methods. But then one of my jobs failed. Specifically, I had a maintenance plan that was creating differential backups. As part of this plan, all user databases are consistency checked. This was the step that appeared to be failing. The error log showed: “Failed:(0) Alter failed for Server” A Profiler trace showed the statement that was causing the error was:

I remembered that I had run the above code to set the ALLOW UPDATES value to 1. As that was the only thing that had changed on the server, I ran it again, setting the value back to 0. The maintenance plan now worked! Hmm. So this command that supposedly has “no effect” actually does have an effect!

Further research shows Microsoft has known about this since 2007. This Knowledge Base article details this exact problem.

Additionally, the TechNet articles linked to above state that changing the ALLOW UPDATES option will generate an error. Not in all cases. Here is the command running on my 2008 SQL Server. Note the lack of an error message.

I am using the WITH OVERRIDE option. This option will disable some configuration value checking, apparently including the check that returns an error when the ALLOW UPDATES option is used. The same command without WITH OVERRIDE returns an error.

Allow me to vent for a moment. Just to get this straight – Microsoft knows this setting that they say has “no effect” actually does have an effect. They knew about this back when SQL 2005 was the latest version of SQL Server. I am experiencing the exact same behavior running SQL 2008 SP1 CU7. According to this handy SQL Server release calendar at SQLServerpedia.com, there have been 40(!) releases of patches to SQL 2005 and/or 2008 between when the KB article was written and 2008 SP1 CU7 was released. It still isn’t fixed. Let’s get it together, Microsoft. If you can’t correct the code, at least correct the documentation so that people don’t waste hours scratching their heads wondering why things aren’t working.

Phew. Now that that’s off my chest, I feel a bit better.