No Column Name

Before I learned the joys of dbatools, I had to always run the depreciated [System.Reflection.Assembly]::LoadWithPartialName() method to make sure that I could access the SMO objects of SQL Server Instances and Databases.

Why did I have to do this?…

…well once, pre-SqlServer Module stage, I wanted to connect to my SQL Server Instance using SMO. However, when I ran New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArguementList "server name" I got an error message complaining that the assembly wasn’t loaded.

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

There’s a potential problem…

…here because a lot of my custom function rely on Microsoft.SqlServer.Smo and if that assembly isn’t loaded then I’m going to have a bad time!

But how do we check if an assembly is loaded already?

AppDomain to the rescue!

So what assemblies do I have?

[appdomain]::CurrentDomain.GetAssemblies() |
Sort-Object -Property FullName |
Select-Object -Property FullName;

Now that I know what assemblies I have loaded, I can see that Smo isn’t loaded. So I would probably load it using LoadWithPartialName().

You may have noticed that I mentioned “pre-SqlServer module” and “before […] dbatools“. This is because with these two modules, I don’t need to worry about assemblies anymore.

To quote the philosopher Bruno Mars, “don’t believe me, just watch!”

We’re going to use the SqlServer module to load the required assemblies for us.

First of all, let’s use a throw away cmdlet so that PowerShell can auto-load the module for us. Normally people would chose Get-SqlDatabase but it doesn’t matter. As long as it’s a cmdlet in the SqlServer module, then this is going to work.

In this case, I’m going to use Get-SqlAgent and throw the results away.

$null = Get-SqlAgent -ServerInstance 'localhost\SQLServer2K16';

Now, if we check if our Smo assembly is loaded…

[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -like '*SMO*';

2 result sets? How much assemblies does it load for us? Running our original assembly query check again, it seems to be a lot bigger than just 2 assemblies.

So what else gets loaded for us?

Opening a new PowerShell window, and throwing the results of our assemblies check into $PreLoad, we then call a SqlServer Module cmdlet. We then throw the results of our assemblies check into $PostLoad.

$PreLoad = [AppDomain]::CurrentDomain.GetAssemblies() |
Sort-Object -Property FullName |
Select-Object -Property FullName;

$null = Get-SqlAgent -ServerInstance 'localhost\SQLSERVER2K16';

$PostLoad = [AppDomain]::CurrentDomain.GetAssemblies() |
Sort-Object -Property FullName |
Select-Object -Property FullName;

Then we can use our good old Compare-Object, that we’ve used before, to see what else gets loaded for us

Compare-Object -ReferenceObject $PreLoad.FullName -DifferenceObject $PostLoad.FullName;

Look at all those SqlServer and AnalysisServices goodies!

I probably wouldn’t have even known about them, and they get loaded for me! I have to check them out.

It is nice to know that you don’t have to manually load assemblies.
LoadWithPartialName() is deprecated and I don’t think anybody wants to memorize the FullName of the assembly to do it the Add-Type way.

Don’t worry though, you don’t have to anymore.

PowerShell got you covered 🙂