172

The following PowerShell code

#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
... rest of the script ...

Gives the following error message:

New-Object : Cannot find type [Microsoft.SqlServer.Management.SMO.Server]: make sure 
the assembly containing this type is loaded.
At C:\Users\sortelyn\ ... \tools\sql_express_backup\backup.ps1:6  char:8
+ $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Every answer on the Internet writes that I have to load the assembly - well sure I can read that from the error message :-) - the question is:

How do you load the assembly and make the script work?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Baxter
  • 3,035
  • 3
  • 20
  • 15

11 Answers11

200

LoadWithPartialName has been deprecated. The recommended solution for PowerShell V3 is to use the Add-Type cmdlet e.g.:

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

There are multiple different versions and you may want to pick a particular version. :-)

Cristian Ciupitu
  • 20,270
  • 7
  • 50
  • 76
Keith Hill
  • 194,368
  • 42
  • 353
  • 369
  • 2
    Okay I use PowerShell3 - these include commands seems very complicated. I would just expect something like "include filename". – Baxter Oct 17 '12 at 07:54
  • 6
    PowerShell is case-insensitive (unless you tell it to be case-sensitive with operators like -cmatch, -ceq). So the casing on command names and parameters doesn't matter. – Keith Hill Sep 22 '14 at 15:44
  • 5
    Yes. http://msdn.microsoft.com/en-us/library/12xc5368(v=vs.110).aspx See the note at the top - `This API is now obsolete.` Of course, that doesn't stop people from using it. – Keith Hill Nov 14 '14 at 15:30
  • 2
    While it is technically correct that `LoadWithPartialName`has been deprecated, the reasons (as outlined in http://blogs.msdn.com/b/suzcook/archive/2003/05/30/57159.aspx) clearly don't apply for an interactive Powershell session. I suggest you add a note that the API is fine for interactive Powershell usage. – Micha Wiedenmann Nov 19 '15 at 09:07
  • Most of the time, I have no problem with the SMO assembly but sometimes I need to kill powershell and when I do, I start having SMO loading issues. Adding add-type -Path fixes that. – Nicolas de Fontenay Mar 21 '16 at 20:53
  • 1
    this helped for me as well `add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Smo.dll'` – GoldenAge May 28 '19 at 11:44
  • Should this resolve (compile/interpret)-time errors or just runtime errors? I can't even get my script to run and this doesn't change anything. Could this be down to the .NET version of the library I'm trying to use? – Sphynx Apr 27 '20 at 10:18
80
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
Shay Levy
  • 121,444
  • 32
  • 184
  • 206
  • 10
    This is too useful to be deprecated without a replacement! My team uses a mixture of 2008 and 2012 client tools. This is the only way to make my PowerShell scripts work for all my team without including clumsy version-fallback logic. – Iain Samuel McLean Elder Oct 22 '13 at 15:52
  • 4
    You can pipe the output to `Out-Null` if you don't want the GAC to echo stuff. – Iain Samuel McLean Elder Oct 22 '13 at 15:54
  • 3
    @Baxter -- you should accept this answer or Keith's and let this question be marked answered. – Jaykul Nov 14 '13 at 17:37
  • 3
    I use [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") – Soeren L. Nielsen Aug 26 '15 at 07:59
  • @IainElder "clumsy version-fallback logic" You say that until you run into version incompatibility! It's not that difficult to say `Add-Type -Path [...]; if (!$?) { Add-Type -Path [...] } elseif [...]`. – Bacon Bits Mar 17 '16 at 20:59
65

Most people know by now that System.Reflection.Assembly.LoadWithPartialName is deprecated, but it turns out that Add-Type -AssemblyName Microsoft.VisualBasic does not behave much better than LoadWithPartialName:

Rather than make any attempt to parse your request in the context of your system, [Add-Type] looks at a static, internal table to translate the "partial name" to a "full name".

If your "partial name" doesn't appear in their table, your script will fail.

If you have multiple versions of the assembly installed on your computer, there is no intelligent algorithm to choose between them. You are going to get whichever one appears in their table, probably the older, outdated one.

If the versions you have installed are all newer than the obsolete one in the table, your script will fail.

Add-Type has no intelligent parser of "partial names" like .LoadWithPartialNames.

What Microsoft's .Net teams says you're actually supposed to do is something like this:

Add-Type -AssemblyName 'Microsoft.VisualBasic, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

Or, if you know the path, something like this:

Add-Type -Path 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.VisualBasic\v4.0_10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll'

That long name given for the assembly is known as the strong name, which is both unique to the version and the assembly, and is also sometimes known as the full name.

But this leaves a couple questions unanswered:

  1. How do I determine the strong name of what's actually being loaded on my system with a given partial name?

    [System.Reflection.Assembly]::LoadWithPartialName($TypeName).Location; [System.Reflection.Assembly]::LoadWithPartialName($TypeName).FullName;

These should also work:

Add-Type -AssemblyName $TypeName -PassThru | Select-Object -ExpandProperty Assembly | Select-Object -ExpandProperty FullName -Unique
  1. If I want my script to always use a specific version of a .dll but I can't be certain of where it's installed, how do I determine what the strong name is from the .dll?

    [System.Reflection.AssemblyName]::GetAssemblyName($Path).FullName;

Or:

Add-Type $Path -PassThru | Select-Object -ExpandProperty Assembly | Select-Object -ExpandProperty FullName -Unique
  1. If I know the strong name, how do I determine the .dll path?

    [Reflection.Assembly]::Load('Microsoft.VisualBasic, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a').Location;

  2. And, on a similar vein, if I know the type name of what I'm using, how do I know what assembly it's coming from?

    [Reflection.Assembly]::GetAssembly([Type]).Location [Reflection.Assembly]::GetAssembly([Type]).FullName

  3. How do I see what assemblies are available?

I suggest the GAC PowerShell module. Get-GacAssembly -Name 'Microsoft.SqlServer.Smo*' | Select Name, Version, FullName works pretty well.

  1. How can I see the list that Add-Type uses?

This is a bit more complex. I can describe how to access it for any version of PowerShell with a .Net reflector (see the update below for PowerShell Core 6.0).

First, figure out which library Add-Type comes from:

Get-Command -Name Add-Type | Select-Object -Property DLL

Open the resulting DLL with your reflector. I've used ILSpy for this because it's FLOSS, but any C# reflector should work. Open that library, and look in Microsoft.Powershell.Commands.Utility. Under Microsoft.Powershell.Commands, there should be AddTypeCommand.

In the code listing for that, there is a private class, InitializeStrongNameDictionary(). That lists the dictionary that maps the short names to the strong names. There's almost 750 entries in the library I've looked at.

Update: Now that PowerShell Core 6.0 is open source. For that version, you can skip the above steps and see the code directly online in their GitHub repository. I can't guarantee that that code matches any other version of PowerShell, however.

Update 2: Powershell 7+ does not appear to have the hash table lookup any longer. Instead they use a LoadAssemblyHelper() method which the comments call "the closest approximation possible" to LoadWithPartialName. Basically, they do this:

loadedAssembly = Assembly.Load(new AssemblyName(assemblyName));

Now, the comments also say "users can just say Add-Type -AssemblyName Forms (instead of System.Windows.Forms)". However, that's not what I see in Powershell v7.0.3 on Windows 10 2004.

# Returns an error
Add-Type -AssemblyName Forms

# Returns an error
[System.Reflection.Assembly]::Load([System.Reflection.AssemblyName]::new('Forms'))

# Works fine
Add-Type -AssemblyName System.Windows.Forms

# Works fine
[System.Reflection.Assembly]::Load([System.Reflection.AssemblyName]::new('System.Windows.Forms'))

So the comments appear to be a bit of a mystery.

I don't know exactly what the logic is in Assembly.Load(AssemblyName) when there is no version or public key token specified. I would expect that this has many of the same problems that LoadWithPartialName does like potentially loading the wrong version of the assembly if you have multiple installed.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • 3rd unanswered question: what if I don't want to require a specific version? – jpmc26 Mar 21 '17 at 19:44
  • 1
    @jpmc26 Well, you can just use `Add-Type` or `LoadWithPartialName()`, but you need to be aware that the former isn't going to be 100% consistent across versions and the latter is an obsolete method. In other words, .Net wants you care about the version of the library that you load. – Bacon Bits Mar 21 '17 at 21:06
  • @BaconBits The full answer to jpmc26 's question is that, depending on whether you're on PowerShell 5 or PowerShell 6, the assembly loaded may be different. JSON.NET has this problem with Azure PS functions. – John Zabroski Sep 04 '18 at 21:31
  • @BaconBits This is a truly fantastic deep dive into PowerShell. You should write a book. – John Zabroski Sep 04 '18 at 21:32
  • Your update should be at the top of the post. Also, why is there no mention about `[Reflection.Assembly]::Loadfile("C:\file.dll")`? – Kellen Stuart Nov 23 '19 at 17:36
  • 1
    @KolobCanyon Because in that case you should generally use `Add-Type -Path`, which is the second code mentioned, or `Assembly.LoadFrom()` which resolves dependencies for you (and, as far as I can tell, is what `Add-Type -Path` uses). The only time you should be using `Assembly.LoadFile()` is if you need to load multiple assemblies that have the same identity but different paths. That's a weird situation. – Bacon Bits Nov 24 '19 at 07:03
  • This is a great answer and should be marked as the answer to this question. – Frank Lesniak May 04 '20 at 18:46
29

If you want to load an assembly without locking it during the duration of the PowerShell session, use this:

$bytes = [System.IO.File]::ReadAllBytes($storageAssemblyPath)
[System.Reflection.Assembly]::Load($bytes)

Where $storageAssemblyPath is the file path of your assembly.

This is especially useful if you need to clean up the resources within your session. For example in a deployment script.

InteXX
  • 6,135
  • 6
  • 43
  • 80
Martin Brandl
  • 56,134
  • 13
  • 133
  • 172
  • 2
    Fantastic. Because in Visual Studio, when debugging Powershell, the PS session hangs around after execution (via PowerShellToolsProcessHost). This approach fixes that. Thanks. – CJBS Nov 29 '17 at 00:39
  • I betcha Azure DevOps hangs on to it as well, which if true would make this a suitable approach for a pipeline task. | @CJBS – InteXX Aug 14 '21 at 05:04
16

Here are some blog posts with numerous examples of ways to load assemblies in PowerShell v1, v2 and v3.

The ways include:

  • dynamically from a source file
  • dynamically from an assembly
  • using other code types, i.e. F#

v1.0 How To Load .NET Assemblies In A PowerShell Session
v2.0 Using CSharp (C#) code in PowerShell scripts 2.0
v3.0 Using .NET Framework Assemblies in Windows PowerShell

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Ralph Willgoss
  • 11,750
  • 4
  • 64
  • 67
14

You can load the whole *.dll assembly with

$Assembly = [System.Reflection.Assembly]::LoadFrom("C:\folder\file.dll");
Igor Kustov
  • 3,228
  • 2
  • 34
  • 31
Yanaki
  • 254
  • 2
  • 4
4

None of the answers helped me, so I'm posting the solution that worked for me, all I had to do is to import the SQLPS module, I realized this when by accident I ran the Restore-SqlDatabase command and started working, meaning that the assembly was referenced in that module somehow.

Just run:

Import-module SQLPS

Note: Thanks Jason for noting that SQLPS is deprecated

instead run:

Import-Module SqlServer

or

Install-Module SqlServer
dim_user
  • 969
  • 1
  • 13
  • 24
  • 2
    For anybody using this approach, FYI that `sqlps` is deprecated in favor of module `sqlserver` – Jason Jul 06 '18 at 15:29
2

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") worked for me.

Martin Brandl
  • 56,134
  • 13
  • 133
  • 172
2

You could use LoadWithPartialName. However, that is deprecated as they said.

You can indeed go along with Add-Type, and in addition to the other answers, if you don't want to specify the full path of the .dll file, you could just simply do:

Add-Type -AssemblyName "Microsoft.SqlServer.Management.SMO"

To me this returned an error, because I do not have SQL Server installed (I guess), however, with this same idea I was able to load the Windows Forms assembly:

Add-Type -AssemblyName "System.Windows.Forms"

You can find out the precise assembly name belonging to the particular class on the MSDN site:

Example of finding out assembly name belonging to a particular class

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
ThomasMX
  • 1,643
  • 2
  • 19
  • 35
2

Make sure you have below features are installed in order

  1. Microsoft System CLR Types for SQL Server
  2. Microsoft SQL Server Shared Management Objects
  3. Microsoft Windows PowerShell Extensions

Also you may need to load

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SqlWmiManagement.dll"
phuclv
  • 37,963
  • 15
  • 156
  • 475
  • I spent a week trying to load the assembly and did not see any output from the statement that loaded them but when I tried to use it, I got the error.When I installed these three things, it worked. - thanks – pparas Mar 03 '20 at 18:12
0

Add the assembly references at the top.

#Load the required assemblies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
phuclv
  • 37,963
  • 15
  • 156
  • 475
  • could you make an example out of it? – endo.anaconda Sep 27 '17 at 11:21
  • 1
    You simply need to add it at the beginning of the powershell script. For e.g : Create Backup of a Database:[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null $SQLServer= Read-Host -Prompt 'SQL Server name(optional) ' IF ([string]::IsNullOrWhitespace($SQLServer)){$SQLServer="XXX";} $SQLDBName = Read-Host -Prompt 'SQL Database name(optional) ' IF ([string]::IsNullOrWhitespace($SQLDBName)){$SQLDBName="XXX";} $SQLLogin = Read-Host -Prompt 'Login' – Amrita Basu Jan 09 '18 at 14:20