It is always a bad sign, when you've got a problem with a script, and you reach for Google, only to find nothing more than a string of Chinese characters on half a page, and a similar StackOverflow question left unanswered since 2008, save for advice to use SSIS instead.

This sort of thing happens when you're using Server Management Objects (SMO) and it's such a shame, as it is potentially a very useful way of automating routine jobs, such as when you need to produce database scripts that will work across SQL Server versions. In fact, almost any job that you can do via SSMS can be done through SMO. With PowerShell and SMO, you can work magic, but with that strange lonesome feeling that not many people have ever walked down the same path. It's the awful documentation, combined with the intricate arcane and multi-layered interface that make SMO hard work for the unfamiliar, but it shouldn't be like this.

Before SQL Server 2005, we had a COM object called Distributed Management Objects (SQL-DMO), originally SQLOLE, which was very serviceable for scripting databases, or the various tables or routines within them. It drove Enterprise Manager. It was useful for routine database tasks that needed to be scripted, especially because it worked across several versions of SQL Server and so removed the need for having different T-SQL for each version. It also worked with any application that could interact with COM objects. When SQL Server moved to .NET, a replacement was provided called SMO. It was a new, ambitious, design but had a lot of features that were there to provide backward compatibility. It worked with .NET languages, but until PowerShell came along, suitable scripting languages to use with it were rare.

Microsoft has always shown its ambivalence to letting users loose on SMO by neglecting to provide anything remotely resembling adequate documentation. All we get is minimal, and probably machine-generated, documentation of the SMO classes, methods and so on. Even the examples have errors. Microsoft has to keep SMO up to date because it is used for SSMS, but there is a great deal of passive resistance to supporting the users who need to use it for scripting. Six years have passed since SMOs release, and despite the initial strong promotion of Powershell as a SQL Server tool, even the sqlps utility is now under warning for deprecation, although apparently will be replaced by a module. Also, there has been little sign of effort in 'putting SMO right' by providing a good solid bank of examples and human-oriented explanation about how to go about automating routine SQL Server tasks with it. Even worse, they have now ensured that your scripts will need to be altered to work with SQL Server 2012, by renaming the .NET libraries. This isn't what we earthlings call love, Microsoft.

Phil Factor.