SQL Server SMO loading Assemblies Gotcha’s

One thing I’ve seen most DBA’s blogging about SQL Server and PowerShell is the use of the V1 way to load the SMO Assemblies using:

And, there’s a reason for still using this method, which I will show in a minute. Now, PowerShell Version 2.0, introduce a new command “Add-Type” use to simplify loading the assemblies:

And, works Great!  Except, when you’re working with different versions of SQL Server on the same box.  You will experience some issues loading the SMO Assembly using the “Add-Type”:

Here’s the error message:

PS [3] > Add-Type -AssemblyName “Microsoft.SqlServer.Smo”;
Add-Type : Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken
=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.
At line:1 char:9
+ Add-Type <<<<  -AssemblyName “Microsoft.SqlServer.Smo”;
+ CategoryInfo          : NotSpecified: (:) [Add-Type], FileNotFoundException
+ FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.AddTypeCommand

I’m sure, many if you may have seen this error, and that’s why people sometime go back to use the old faithful V1 format.

Now, you need to be careful because you might be loading the wrong SMO assemblies to use against your database.  But, for most cases, I sure it’s OK to load the latest one because it can handle older versions.  This is why you most test all your scripts before putting them in your production environment.

Well, the old and faithful V1 “ [reflection.assembly]::Load… ” to load your SMO assemblies work because it grabs the latest version from your windows “Assembly” folder.   Here’s an example:

In my environment I do have SQL Server Express, SQL Server 2008 R2 Developer, and SQL Server Denali.  So, when I use V1 way to load my SMO, you will notice in the PSConsole that it’s loading the assembly from SQL Server SMO version 11.0.0.0.  Notice that using the V2  command “Add-Type”, was trying to find 9.0.242.0 and fail.  Even,  if this error might be due to a registry issue, this is no the assembly I want to load.

Now, can we still use V2 “Add-Type” command to load the correct version of this assemblies.  Hell Yea!!

Here is how:

1. You need to confirm the full path and filename of the  SMO DLL, which is located in the “C:\Windows\Assembly” folder.

2. It helps to load the latest SMO assembly using V1 way, then use the following .NET line to list the location of that assembly:
[system.appdomain]::CurrentDomain.GetAssemblies() | Select location

3. Now, look for your loaded SMO assembly location, then highlight and press ‘Enter’ to copy the line:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll

4. Use the V2 “Add-Type” command with the “-Path” parameter, and paste (right-click mouse) to add the copied SMO full path.  This value must be a string so use quotes around it.  Don’t press enter yet!!

Sample line copied but change version to “10.0.0.0”:
“C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll”

In order to load a different version, you need to manually change the version number.  In my case, I need to change from 11.0.0.0 to use my SQL Server SMO version 10.0.0.0.  This is why you need to check for your multiple SMO version already installed in your system under you Windows Assembly folder.

Finally, press enter on the following command will load the SMO assembly for SQL Server 2008/R2: (this is a one-liner)

I know it’s a long line.  Remember, you just type it one time and then copy/paste to another script.

Don’t forget to save this line, then open a new PSConsole session, and run it.

Here’s screen shot how is done:

Keep in mind, if you’re wondering what assemblies are loaded during your PowerShell session, then use the following one-liner:

To test your SMO loaded successfully use the following SMO lines to connect to you local SQL instance:

Happy PowerShelling!!