Powershell, SMO, and Add-Member - Not Always BFF

Over the past few weeks, I’ve been working with SMO to implement some maintenance routines across my clients. Because of the nature of most of my clients, Powershell is almost always the answer. I have been trying to simplify the coding of some of these scripts by using Add-Member.
I had some issues with the added property being sporadically (at least I thought) available, so I set out to determine what was going on. Below is the tale of SMO and the Missing Property.

Why Add-Member

The most common use of Add-Member is to build a custom object.

$MyCustomObject = New-Object PSObject

$MyCustomObject | Add-Member -MemberType NoteProperty -Name NewProperty `
    -Value "NewValue"

This is neat; however, that is only the proverbial tip of the iceberg. One of the greatest things about Powershell is that everything, absolutely everything, is an object. Not only is everything an object, Add-Member seems to work on just about every object that is created. This provides an easy way to extend objects to fit the needs of any custom scripts.

$HomeFolder = Get-Item $Env:USERPROFILE

$HomeFolder | Add-Member -MemberType AliasProperty -Name FullPath `
    -Value FullName

$HomeFolder.FullPath

SMO Database – it’s an object, Add-Member will work, Right?

Yup. Well, sometimes. When debugging my scripts, I had no issue. However, when I started to test my scripts in a -noprofile session without debugging, the property that I added was empty.

Here’s an example that adding the property does not work in a Powershell session

$instancename = ".\SQL2008R2"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")

$db = $srv.Databases["master"]

$db | Add-Member -MemberType NoteProperty -Name TestNoteProperty `
    -Value "AmIHere?"
    
$db.TestNoteProperty

If you run this, you get nothing. However, if you use the -PassThru switch on Add-Member and pipe to a Select-Object, it does work, but only in the pipeline. Referencing the new property after the pipeline is done still presents no value.

$instancename = ".\SQL2008R2"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")

$db = $srv.Databases["master"]

$db | Add-Member -MemberType NoteProperty -Name TestNoteProperty `
    -Value "AmIHere?" -PassThru | Select-Object TestNoteProperty
    
$db.TestNoteProperty

Isolating the Problem

When trying to figure this out, I sent the name of the database object to the output so that I could verify that the object was there before I added the custom property. To my surprise, when I did this, the custom property worked.

$instancename = ".\SQL2008R2"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")

$db = $srv.Databases["master"]

#Output the Name to make certain the object is acutally there
$db.Name

$db | Add-Member -MemberType NoteProperty -Name TestNoteProperty `
    -Value "AmIHere?"
    
$db.TestNoteProperty

Wow – so I have to reference a property before I can add one to it? It took a few seconds before I remembered that SMO has “Optimized Performance” and waits to instantiate the object until you need a property. To quote msdn:

The SMO architecture is more efficient in terms of memory because objects are only partially instantiated at first, and minimal property information is requested from the server. Full instantiation of objects is delayed until the object is explicitly referenced. An object is fully instantiated when a property is requested that is not in the set of properties that are first retrieved, or when a method is called that requires such a property. The transition between partially instantiated and fully instantiated objects is transparent to the user.

It’s only a guess that this is the problem, but I have not experienced this problem on any other objects in Powershell. During troubleshooting, I discovered a couple of workarounds

One option that you have as a workaround is to use the New-Object cmdlet to build the object explicitly. This seems to workaround whatever issue there is with the database object.

$db = New-Object Microsoft.SqlServer.Management.Smo.Database ($srv, "master")

As mentioned previously, you can also call an inexpensive property of the database object – like Name. It seems that by doing this, it allows Add-Member to properly add the peroperty.

I debated whether or not to submit this to Connect, then debated where to put it – Powershell or SQL Server. Because this problem seems to go away depending on how I instantiate the object, I decided to submit it to Powershell. If this is important, feel free to up-vote the submission.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter