I’ve been working a lot lately with SMO and the differences between the various versions between SQL 2005, 2008, and 2012. Through this process, I’ve come to understand why “[Reflection.Assembly]::LoadWithPartialName” is not a good option in PowerShell. Not only is it obsolete, it doesn’t allow you to choose which version of the SMO library you want to load and always loads the most recent. So, if you have SQL 2005, 2008, and 2012, you’ll get the 2012 versions regardless.

I wanted more control, so started to switch from LoadWithPartialName to Add-Type. I used the following code on my workstation and attempted to load the SMO assembly and it worked just fine.

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

When I checked what version of the assembly that had loaded, I realized that it had chosen the 2005 version? I thought, great, this does the opposite of LoadWithPartialName and loads the oldest… So, I tried it on box that did not have the 2005 client installed. To my surprise, it failed with the following error:

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.

A quick Bing search found an interesting Connect posting that offered some explanation. To quote “Microsoft” in the comment “The list is hard coded so that anybody writing “Add-Type –Assembly Microsoft.SqlServer.Smo” gets the same version.” In a similar vein, the documentation for Add-Type states that if “you enter a simple or partial name, Add-Type resolves it to the full name, and then uses the full name to load the assembly”. While this makes some sense, neither of these statements is clear exactly from what list this pulls from.

I spent some time with ProcessMonitor trying to determine if it was getting it from the registry or possibly from a different DLL with no luck. After spending some intimate time Bingling, trying to hit up methods and properties of the AddTypeCommand class, and otherwise coming up empty handed, I decided to take an alternate approach.

All the assemblies that I currently care to load for my work will be stored in one of the six folders of the GAC (Global Assembly Cache) on my Windows 7 Workstation. I decided to take this known list of assemblies and toss it to Add-Type and see what fell out.

The script below will take all of the assembly names that it can find and throw them at the Add-Type cmdlet. If the cmdlet loads the assembly, the current assembly list is examined to determine the full name of the assembly that was loaded.

If the cmdlet fails, the type of failure is recorded. If the assembly could not be loaded because the short name references an assembly that just doesn’t exist. Technically, Add-Type has that assembly in its list, so we can take the information out of the exception and know what the full assembly name was and store that value.

There are several assemblies that Add-Type does not seem to have a reference for. These will throw a custom error of ASSEMBLY_NOT_FOUND which means that the lookup failed. These assemblies are not in the list.

The full code is below. I understand, this is a very resource intensive approach, but there is little other choice that I could find that would tell me what versions the cmdlet would be loading for any given assembly. On my machine, it attempted to load 1073 assemblies (took a while). Of those, I was able to determine that 308 of them are in the Add-Type list. I’ve also included my list below. I do not believe this list to be comprehensive, but I do think it is safe to say that anything on this list reflects the accurate full name that Add-Type will resolve to if you provide a partial name.

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

#Best to Start this in a powershell.exe -noprofile session

$CSVLocation = "$Env:USERPROFILE\Add_Type_Lookup.csv"

#Initialize Array to hold assemblies to attempt to load
$AssemblyNames = @()
$AssemblyReferences = @()

#Get all assemblies currently in the _old_ GAC
Get-ChildItem "C:\Windows\assembly\GAC" | Select -Unique Name |
    ForEach-Object{$AssemblyNames += $_.Name}
    
#32/64 bit compatibles
Get-ChildItem "C:\Windows\assembly\GAC_MSIL" | Select -Unique Name |
    ForEach-Object{if(
        $AssemblyNames -notcontains $_.Name){$AssemblyNames += $_.Name}}

#Get any 32-bit specific assemblies not already loaded
Get-ChildItem "C:\Windows\assembly\GAC_32" | Select -Unique Name |
    ForEach-Object{if(
        $AssemblyNames -notcontains $_.Name){$AssemblyNames += $_.Name}}

#Get any 64-bit specific assemblies
Get-ChildItem "C:\Windows\assembly\GAC_64" | Select -Unique Name |
    ForEach-Object{if(
        $AssemblyNames -notcontains $_.Name){$AssemblyNames += $_.Name}}

#New GAC

#Combined 32/64
Get-ChildItem "C:\Windows\Microsoft.NET\assembly\GAC_MSIL" | 
    Select -Unique Name |
    ForEach-Object{if(
        $AssemblyNames -notcontains $_.Name){$AssemblyNames += $_.Name}}
    
#32 only
Get-ChildItem "C:\Windows\Microsoft.NET\assembly\GAC_32" | 
    Select -Unique Name |
    ForEach-Object{if(
        $AssemblyNames -notcontains $_.Name){$AssemblyNames += $_.Name}}
    
#64 only
Get-ChildItem "C:\Windows\Microsoft.NET\assembly\GAC_64" | 
    Select -Unique Name |
    ForEach-Object{if(
        $AssemblyNames -notcontains $_.Name){$AssemblyNames += $_.Name}}


#Now that I have all the assembly names in my GAC, loop over each of them
#and see how add-type reacts
foreach($AssemblyName in $AssemblyNames)
{
    
    #Create object to throw into array for further evaluation
    $AssemblyReference = "" | select Name, FullName
    
    #The name of the assembly is present, no need to gather that
    #from any of the code below
    $AssemblyReference.Name = $AssemblyName
    
    try
    {
        Add-Type -AssemblyName $AssemblyName -ErrorAction Stop
        
        
        #If there is no error, the assembly is in the hard-coded list within
        #Add-Type, so let's interrogate the current appdomain assembly list 
        #to determine the actual full name that was loaded
        
        $AssemblyReference.FullName = (
            [AppDomain]::CurrentDomain.GetAssemblies() |
                ?{$_.FullName -like "$AssemblyName,*"}).FullName
    }
    catch [System.IO.FileNotFoundException]
    {
        #If the name is in the Add-Type hard-coded list, it attempts to load it
        #If it can't be loaded, it throws a convenient FileNotFoundException
        #Fortunately, the "FileName" is the full name of the assembly
        #that was attempted to be loaded
        
        $AssemblyReference.FullName = $_.Exception.FileName
        
    }
    catch 
    {

        if($_.FullyQualifiedErrorID -like "ASSEMBLY_NOT_FOUND*")
        {
            #Add-Type throws an error of ASSEMBLY_NOT_FOUND if the short name
            #is not in the list - so regardless of how hard we wish, we can't
            #load this assembly unless we use the full name
            $AssemblyReference.FullName = "Not In List"
        }
        else
        {
            #Some other error occured that is not expected
            #Log the fullname as unknown so that it can be evaluated later
            $AssemblyReference.FullName = "UNKNOWN"
        }
    }
    
    #Add the assembly object which has the short and full name to the array
    $AssemblyReferences += $AssemblyReference
}

#Send the array out to a CSV file and open it
$AssemblyReferences | ?{("Not In List") -notcontains $_.FullName} | 
    Sort-Object Name | 
    Export-Csv -NoTypeInformation -Path $CSVLocation

Invoke-Item $CSVLocation

Add-Type Assembly Reference List from my Windows 7 64-bit workstation: