Powershell Add-Type – Where’s That Assembly!

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:

Name	FullName
Accessibility	Accessibility, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
ADODB	ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
AspNetMMCExt	AspNetMMCExt, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
CppCodeProvider	CppCodeProvider, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
cscompmgd	cscompmgd, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
CustomMarshalers	CustomMarshalers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
EnvDTE	EnvDTE, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
EnvDTE80	EnvDTE80, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
EnvDTE90	EnvDTE90, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
EventViewer	EventViewer, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
EventViewer.Resources	EventViewer.resources, Version=6.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Extensibility	Extensibility, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
IEExecRemote	IEExecRemote, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
IEHost	IEHost, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
IIEHost	IIEHost, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
ipdmctrl	ipdmctrl, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
ISymWrapper	ISymWrapper, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
MFCMIFC80	MFCMIFC80, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.AnalysisServices	Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.AnalysisServices.AdomdClient	Microsoft.AnalysisServices.AdomdClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.AnalysisServices.DeploymentEngine	Microsoft.AnalysisServices.DeploymentEngine, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.Build.Conversion.v3.5	Microsoft.Build.Conversion.v3.5, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Build.Engine	Microsoft.Build.Engine, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Build.Framework	Microsoft.Build.Framework, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Build.Tasks	Microsoft.Build.Tasks, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Build.Tasks.v3.5	Microsoft.Build.Tasks.v3.5, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Build.Utilities	Microsoft.Build.Utilities, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Build.Utilities.v3.5	Microsoft.Build.Utilities.v3.5, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.DataWarehouse.Interfaces	Microsoft.DataWarehouse.Interfaces, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.ExceptionMessageBox	Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.GroupPolicy.Interop	Microsoft.GroupPolicy.Interop, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.GroupPolicy.Reporting	Microsoft.GroupPolicy.Reporting, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.GroupPolicy.Reporting.Resources	Microsoft.GroupPolicy.Reporting.resources, Version=2.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.Ink	Microsoft.Ink, Version=6.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.Ink.Resources	Microsoft.Ink.resources, Version=6.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.Internal.VisualStudio.Shell.Interop.9.0	Microsoft.Internal.VisualStudio.Shell.Interop.9.0, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Interop.Security.AzRoles	Microsoft.Interop.Security.AzRoles, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.JScript	Microsoft.JScript, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.ManagementConsole	Microsoft.ManagementConsole, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.ManagementConsole.Resources	Microsoft.ManagementConsole.resources, Version=3.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.mshtml	Microsoft.mshtml, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.MSXML	Microsoft.MSXML, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.NetEnterpriseServers.ExceptionMessageBox	Microsoft.NetEnterpriseServers.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.Office.InfoPath	Microsoft.Office.InfoPath, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.InfoPath.Client.Internal.Host	Microsoft.Office.InfoPath.Client.Internal.Host, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.InfoPath.Client.Internal.Host.Interop	Microsoft.Office.InfoPath.Client.Internal.Host.Interop, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.InfoPath.FormControl	Microsoft.Office.InfoPath.FormControl, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.InfoPath.Permission	Microsoft.Office.InfoPath.Permission, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.InfoPath.Vsta	Microsoft.Office.InfoPath.Vsta, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Access	Microsoft.Office.Interop.Access, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Access.Dao	Microsoft.Office.Interop.Access.Dao, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Excel	Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Graph	Microsoft.Office.Interop.Graph, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.InfoPath	Microsoft.Office.Interop.InfoPath, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.InfoPath.SemiTrust	Microsoft.Office.Interop.InfoPath.SemiTrust, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.InfoPath.Xml	Microsoft.Office.Interop.InfoPath.Xml, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.OneNote	Microsoft.Office.Interop.OneNote, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Outlook	Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.OutlookViewCtl	Microsoft.Office.Interop.OutlookViewCtl, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.PowerPoint	Microsoft.Office.Interop.PowerPoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Publisher	Microsoft.Office.Interop.Publisher, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.SmartTag	Microsoft.Office.Interop.SmartTag, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Interop.Word	Microsoft.Office.Interop.Word, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Office.Tools.Common	Microsoft.Office.Tools.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Office.Tools.Excel	Microsoft.Office.Tools.Excel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Office.Tools.Outlook	Microsoft.Office.Tools.Outlook, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Office.Tools.Word	Microsoft.Office.Tools.Word, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.PowerShell.Commands.Diagnostics	Microsoft.PowerShell.Commands.Diagnostics, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Commands.Management	Microsoft.PowerShell.Commands.Management, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Commands.Management.Resources	Microsoft.PowerShell.Commands.Management.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Commands.Utility	Microsoft.PowerShell.Commands.Utility, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Commands.Utility.Resources	Microsoft.PowerShell.Commands.Utility.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.ConsoleHost	Microsoft.PowerShell.ConsoleHost, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.ConsoleHost.Resources	Microsoft.PowerShell.ConsoleHost.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Editor	Microsoft.PowerShell.Editor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Editor.Resources	Microsoft.PowerShell.Editor.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.GPowerShell	Microsoft.PowerShell.GPowerShell, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.GPowerShell.Resources	Microsoft.PowerShell.GPowerShell.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.GraphicalHost	Microsoft.PowerShell.GraphicalHost, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.GraphicalHost.Resources	Microsoft.PowerShell.GraphicalHost.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Security	Microsoft.PowerShell.Security, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.PowerShell.Security.Resources	Microsoft.PowerShell.Security.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.ReportViewer.Common	Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.ReportViewer.Design	Microsoft.ReportViewer.Design, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.ReportViewer.ProcessingObjectModel	Microsoft.ReportViewer.ProcessingObjectModel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.ReportViewer.WebDesign	Microsoft.ReportViewer.WebDesign, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.ReportViewer.WebForms	Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.ReportViewer.WinForms	Microsoft.ReportViewer.WinForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.SqlServer.BatchParser	Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.ConnectionInfo	Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.CustomControls	Microsoft.SqlServer.CustomControls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.GridControl	Microsoft.SqlServer.GridControl, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.Instapi	Microsoft.SqlServer.Instapi, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.MgdSqlDumper	Microsoft.SqlServer.MgdSqlDumper, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.RegSvrEnum	Microsoft.SqlServer.RegSvrEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.Replication	Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.Replication.BusinessLogicSupport	Microsoft.SqlServer.Replication.BusinessLogicSupport, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.Rmo	Microsoft.SqlServer.Rmo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.ServiceBrokerEnum	Microsoft.SqlServer.ServiceBrokerEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.Smo	Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.SmoEnum	Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.SqlEnum	Microsoft.SqlServer.SqlEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.SqlTDiagM	Microsoft.SqlServer.SqlTDiagM, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.SString	Microsoft.SqlServer.SString, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.WizardFrameworkLite	Microsoft.SqlServer.WizardFrameworkLite, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.SqlServer.WmiEnum	Microsoft.SqlServer.WmiEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Microsoft.StdFormat	Microsoft.StdFormat, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Tpm	Microsoft.Tpm, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft.Tpm.Resources	Microsoft.Tpm.resources, Version=6.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
Microsoft.Transactions.Bridge	Microsoft.Transactions.Bridge, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Transactions.Bridge.Dtc	Microsoft.Transactions.Bridge.Dtc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Vbe.Interop	Microsoft.Vbe.Interop, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.Vbe.Interop.Forms	Microsoft.Vbe.Interop.Forms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Microsoft.VisualBasic	Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualBasic.Compatibility	Microsoft.VisualBasic.Compatibility, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualBasic.Compatibility.Data	Microsoft.VisualBasic.Compatibility.Data, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualBasic.Vsa	Microsoft.VisualBasic.Vsa, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualC	Microsoft.VisualC, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualC.STLCLR	Microsoft.VisualC.STLCLR, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualC.VSCodeParser	Microsoft.VisualC.VSCodeParser, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualC.VSCodeProvider	Microsoft.VisualC.VSCodeProvider, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio	Microsoft.VisualStudio, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.CommandBars	Microsoft.VisualStudio.CommandBars, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.CommonIDE	Microsoft.VisualStudio.CommonIDE, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Configuration	Microsoft.VisualStudio.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Debugger.Interop	Microsoft.VisualStudio.Debugger.Interop, Version=8.0.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Debugger.InteropA	Microsoft.VisualStudio.Debugger.InteropA, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.DebuggerVisualizers	Microsoft.VisualStudio.DebuggerVisualizers, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Design	Microsoft.VisualStudio.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Designer.Interfaces	Microsoft.VisualStudio.Designer.Interfaces, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Diagnostics.ServiceModelSink	Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Editors	Microsoft.VisualStudio.Editors, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.EnterpriseTools	Microsoft.VisualStudio.EnterpriseTools, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.EnterpriseTools.ClassDesigner	Microsoft.VisualStudio.EnterpriseTools.ClassDesigner, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.EnterpriseTools.Shell	Microsoft.VisualStudio.EnterpriseTools.Shell, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.EnterpriseTools.TypeSystem	Microsoft.VisualStudio.EnterpriseTools.TypeSystem, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.HostingProcess.Utilities	Microsoft.VisualStudio.HostingProcess.Utilities, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.HostingProcess.Utilities.Sync	Microsoft.VisualStudio.HostingProcess.Utilities.Sync, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.ManagedInterfaces	Microsoft.VisualStudio.ManagedInterfaces, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Modeling	Microsoft.VisualStudio.Modeling, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Modeling.ArtifactMapper	Microsoft.VisualStudio.Modeling.ArtifactMapper, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Modeling.ArtifactMapper.VSHost	Microsoft.VisualStudio.Modeling.ArtifactMapper.VSHost, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Modeling.Diagrams	Microsoft.VisualStudio.Modeling.Diagrams, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Modeling.Diagrams.GraphObject	Microsoft.VisualStudio.Modeling.Diagrams.GraphObject, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.OLE.Interop	Microsoft.VisualStudio.OLE.Interop, Version=7.1.40304.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Package.LanguageService	Microsoft.VisualStudio.Package.LanguageService, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.ProjectAggregator	Microsoft.VisualStudio.ProjectAggregator, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Publish	Microsoft.VisualStudio.Publish, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.QualityTools.Resource	Microsoft.VisualStudio.QualityTools.Resource, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.QualityTools.UnitTestFramework	Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Shell	Microsoft.VisualStudio.Shell, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Shell.9.0	Microsoft.VisualStudio.Shell.9.0, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Shell.Design	Microsoft.VisualStudio.Shell.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Shell.Interop	Microsoft.VisualStudio.Shell.Interop, Version=7.1.40304.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Shell.Interop.8.0	Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Shell.Interop.9.0	Microsoft.VisualStudio.Shell.Interop.9.0, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.TeamSystem.PerformanceWizard	Microsoft.VisualStudio.TeamSystem.PerformanceWizard, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.TemplateWizardInterface	Microsoft.VisualStudio.TemplateWizardInterface, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.TextManager.Interop	Microsoft.VisualStudio.TextManager.Interop, Version=7.1.40304.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.TextManager.Interop.8.0	Microsoft.VisualStudio.TextManager.Interop.8.0, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.TextManager.Interop.9.0	Microsoft.VisualStudio.TextManager.Interop.9.0, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Adapter	Microsoft.VisualStudio.Tools.Applications.Adapter, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Adapter.v9.0	Microsoft.VisualStudio.Tools.Applications.Adapter.v9.0, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.AddInManager	Microsoft.VisualStudio.Tools.Applications.AddInManager, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Blueprints	Microsoft.VisualStudio.Tools.Applications.Blueprints, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.ComRPCChannel	Microsoft.VisualStudio.Tools.Applications.ComRPCChannel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Contract	Microsoft.VisualStudio.Tools.Applications.Contract, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Contract.v9.0	Microsoft.VisualStudio.Tools.Applications.Contract.v9.0, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.DesignTime	Microsoft.VisualStudio.Tools.Applications.DesignTime, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Hosting	Microsoft.VisualStudio.Tools.Applications.Hosting, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Hosting.v9.0	Microsoft.VisualStudio.Tools.Applications.Hosting.v9.0, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.InteropAdapter	Microsoft.VisualStudio.Tools.Applications.InteropAdapter, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.Runtime	Microsoft.VisualStudio.Tools.Applications.Runtime, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Applications.ServerDocument	Microsoft.VisualStudio.Tools.Applications.ServerDocument, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Tools.Office.Runtime	Microsoft.VisualStudio.Tools.Office.Runtime, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VCCodeModel	Microsoft.VisualStudio.VCCodeModel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VCProject	Microsoft.VisualStudio.VCProject, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VCProjectEngine	Microsoft.VisualStudio.VCProjectEngine, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VirtualTreeGrid	Microsoft.VisualStudio.VirtualTreeGrid, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VSContentInstaller	Microsoft.VisualStudio.VSContentInstaller, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VSHelp	Microsoft.VisualStudio.VSHelp, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.VSHelp80	Microsoft.VisualStudio.VSHelp80, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Windows.Forms	Microsoft.VisualStudio.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.WizardFramework	Microsoft.VisualStudio.WizardFramework, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VisualStudio.Zip	Microsoft.VisualStudio.Zip, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Vsa	Microsoft.Vsa, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.Vsa.Vb.CodeDOMProcessor	Microsoft.Vsa.Vb.CodeDOMProcessor, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.VSDesigner	Microsoft.VSDesigner, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Microsoft.WSMan.Management	Microsoft.WSMan.Management, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Microsoft_VsaVb	Microsoft_VsaVb, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
MiguiControls	MIGUIControls, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
MiguiControls.Resources	MIGUIControls.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
MMCEx	MMCEx, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
MMCEx.Resources	MMCEx.resources, Version=3.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
MMCFxCommon	MMCFxCommon, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
MMCFxCommon.Resources	MMCFxCommon.resources, Version=3.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
MSClusterLib	MSClusterLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
mscomctl	mscomctl, Version=10.0.4504.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
mscorcfg	mscorcfg, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
mscorlib	mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
MSDATASRC	MSDATASRC, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
msddslmp	msddslmp, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
msddsp	msddsp, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
napcrypt	napcrypt, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
naphlpr	naphlpr, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
napinit	napinit, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
napinit.resources	napinit.resources, Version=6.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
napsnap	napsnap, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
napsnap.resources	napsnap.resources, Version=6.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
office	office, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.1.0.Microsoft.Ink	Policy.1.0.Microsoft.Ink, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Policy.1.0.Microsoft.Interop.Security.AzRoles	Policy.1.0.Microsoft.Interop.Security.AzRoles, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Policy.1.2.Microsoft.Interop.Security.AzRoles	Policy.1.2.Microsoft.Interop.Security.AzRoles, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Policy.1.7.Microsoft.Ink	Policy.1.7.Microsoft.Ink, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Policy.11.0.Microsoft.Office.Interop.Access	Policy.11.0.Microsoft.Office.Interop.Access, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.Excel	Policy.11.0.Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.Graph	Policy.11.0.Microsoft.Office.Interop.Graph, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.InfoPath	Policy.11.0.Microsoft.Office.Interop.InfoPath, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.InfoPath.Xml	Policy.11.0.Microsoft.Office.Interop.InfoPath.Xml, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.Outlook	Policy.11.0.Microsoft.Office.Interop.Outlook, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.OutlookViewCtl	Policy.11.0.Microsoft.Office.Interop.OutlookViewCtl, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.PowerPoint	Policy.11.0.Microsoft.Office.Interop.PowerPoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.Publisher	Policy.11.0.Microsoft.Office.Interop.Publisher, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.SmartTag	Policy.11.0.Microsoft.Office.Interop.SmartTag, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Office.Interop.Word	Policy.11.0.Microsoft.Office.Interop.Word, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.Microsoft.Vbe.Interop	Policy.11.0.Microsoft.Vbe.Interop, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Policy.11.0.office	Policy.11.0.office, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
PresentationBuildTasks	PresentationBuildTasks, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationCFFRasterizer	PresentationCFFRasterizer, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationCore	PresentationCore, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationFramework	PresentationFramework, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationFramework.Aero	PresentationFramework.Aero, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationFramework.Classic	PresentationFramework.Classic, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationFramework.Luna	PresentationFramework.Luna, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationFramework.Royale	PresentationFramework.Royale, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
PresentationUI	PresentationUI, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
ReachFramework	ReachFramework, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
SMDiagnostics	SMDiagnostics, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
soapsudscode	soapsudscode, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
stdole	stdole, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
sysglobl	sysglobl, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System	System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.AddIn	System.AddIn, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.AddIn.Contract	System.AddIn.Contract, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Configuration	System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Configuration.Install	System.Configuration.Install, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Core	System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data	System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data.DataSetExtensions	System.Data.DataSetExtensions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data.Linq	System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data.OracleClient	System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data.SqlXml	System.Data.SqlXml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Deployment	System.Deployment, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Design	System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.DirectoryServices	System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.DirectoryServices.AccountManagement	System.DirectoryServices.AccountManagement, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.DirectoryServices.Protocols	System.DirectoryServices.Protocols, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Drawing	System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Drawing.Design	System.Drawing.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.EnterpriseServices	System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.IdentityModel	System.IdentityModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.IdentityModel.Selectors	System.IdentityModel.Selectors, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.IO.Log	System.IO.Log, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Management	System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Management.Automation	System.Management.Automation, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Management.Automation.Resources	System.Management.Automation.resources, Version=1.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
System.Management.Instrumentation	System.Management.Instrumentation, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Messaging	System.Messaging, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Net	System.Net, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Printing	System.Printing, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Runtime.Remoting	System.Runtime.Remoting, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Runtime.Serialization	System.Runtime.Serialization, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Runtime.Serialization.Formatters.Soap	System.Runtime.Serialization.Formatters.Soap, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Security	System.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.ServiceModel	System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.ServiceModel.Install	System.ServiceModel.Install, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.ServiceModel.WasHosting	System.ServiceModel.WasHosting, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.ServiceModel.Web	System.ServiceModel.Web, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.ServiceProcess	System.ServiceProcess, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Speech	System.Speech, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Transactions	System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Web	System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Web.Extensions	System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Web.Extensions.Design	System.Web.Extensions.Design, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Web.Mobile	System.Web.Mobile, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Web.RegularExpressions	System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Web.Services	System.Web.Services, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
System.Windows.Forms	System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Windows.Presentation	System.Windows.Presentation, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Workflow.Activities	System.Workflow.Activities, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Workflow.ComponentModel	System.Workflow.ComponentModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Workflow.Runtime	System.Workflow.Runtime, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.WorkflowServices	System.WorkflowServices, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
System.Xml	System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Xml.Linq	System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
TaskScheduler	TaskScheduler, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
TaskScheduler.Resources	TaskScheduler.resources, Version=6.0.0.0, Culture=en, PublicKeyToken=31bf3856ad364e35
UIAutomationClient	UIAutomationClient, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
UIAutomationClientsideProviders	UIAutomationClientsideProviders, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
UIAutomationProvider	UIAutomationProvider, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
UIAutomationTypes	UIAutomationTypes, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
VSLangProj	VSLangProj, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
VSLangProj2	VSLangProj2, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
VSLangProj80	UNKNOWN
VsWebSite.Interop	VsWebSite.Interop, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
WebDev.WebHost	WebDev.WebHost, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
WindowsBase	WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
WindowsFormsIntegration	WindowsFormsIntegration, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

The 2012 Powershell Scripting Game(shows) in review

Over the past few weeks, those around me know that I’ve been talking a lot about Powershell, how much it can improve your life, and about the 2012 Powershell Scripting Games. Nearly everyone I’ve shared with has asked “What are the Scripting Games?”

My answer to them was and will always be “It’s a bunch of geeks learning new stuff by attempting to solve 10 real-world scenarios by using Powershell.” For the less technical, I would follow that up with “It’s kind of like a reality game show only with computers and stuff.” Oddly enough, that made sense to most people, so I’ll continue with that theme as I share a few of my observations below.

“Survivor” it was not

When others around me (both technical and non-technical) heard this was titled “Games”, the near immediate response was “What do you get if you win?” Before feeling compelled to look it up, the first few people got the answer “I don’t know…” I really didn’t know, and I wouldn’t be surprised if others competing would have had a similar response. Never did I feel like the other competitors were in it to beat me – I know I wished no ill will on any of the other competitors. To the contrary – unlike Survivor – I found that those who were participating did their best to help everyone else while not giving up the actual solutions. The Powershell community is a group of people who like sharing knowledge, not a bunch of people who want to out-wit their peers, clamoring to win. Even as the games came to a close, the winners were grateful for their recognition.

“Wheel of Fortune”

Growing up, my Papaw Neier was an avid wheel-watcher. He would watch Wheel of Fortune every night. If we were at his house, it was mandatory to watch it with him. Each of us grandchildren would sometimes randomly scream letters at the TV, but Papaw would always be there to offer sage advice on what letter combinations made the most sense and guide us to the correct solution, never letting on that he knew the answer. These games, as in real life as an IT professional, were a lot like Wheel of Fortune. The initial event description provided the basis – the RSTLN and E. However, it didn’t stop there. We weren’t left to blindly guess with only the letters first put on the board. A lot of the information was available, but it was the comments to the posts that helped to complete the picture. Scripters would ask questions and someone, usually IamMred, would clarify the requirements, but always without giving up too much information as to give away the solution. Without this careful dissemination of information, some of these events would have been difficult at best. This is a good practice to take back into real life – always be ready to help out someone else, but be slow to give up the answer. Encourage learning, not leeching.

“Beat the Clock”

During the first week of the games, I was able to get the scripts turned in within a reasonable time frame, some within the same day. I even won one of the daily prizes donated by O’Reilly Media! As the second week came, life happened. I had to leave the games on the back burner, not knowing whether I’d get back to them or not. As the second week ended and life returned to normal, I realized I still had time to get all the events completed, but it wouldn’t be easy. I had work to be done and leaving the family to fend for themselves favor of the scripting games was not an option. Each evening, after everyone was in bed, I began working feverishly into the early morning (fortunately, Eastern time-zone) to get the events completed just minutes to hours before each of the deadlines. I did my best to make certain that my wife and kids were only moderately affected by my own personal game of “Beat the Clock”. I knew the task, I knew how much time I had to get it done, and I knew no one else could do it for me. As a microcosm of real life – we only have so much time, so use it wisely, do it right, and don’t forget those who love you.

“Dancing with the Stars”

I’ve tried on multiple occasions to get through an entire episode of “Dancing with the Stars” without success. While I’m certain that it has significant entertainment value to others, I have never been able to enjoy watching it. However, the premise of the show seems clear – learn from the expert and you’ll do better. I am incredibly grateful for the time that the expert judges took to not only analyze and run our scripts, but take the time to comment and offer advice in the process. In Event 2, it was suggested to me to avoid using Read-Host and switch to using PromptForChoice() by Boe Prox. I had never heard of this before, but soon realized how cool it was, implementing it in Event 8. In the comments of my Event 3, Bartek Bielawski introduced me to the ConvertToDateTime WMI method, which I have used professionally already. My scripting and therefore my career are now better off having been granted the privilege of “Dancing with the Stars” during the scripting games. These guys are masters of the craft. For as much time as I put in just writing 10 scripts, I can’t imagine how many hours these judged sacrificed to make each of us better at what we do – for free. They deserve an award.

Thanks again to the Scripting Guys, all the judges, and their families for creating an environment where we can all learn to do our jobs better, for free, and have fun doing it. Congratulations to the winners and to everyone, both beginner and advanced who competed.

I look forward to gaining more knowledge and experience next year!

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

PowerShell: Using Exponents and Logs to Format Byte Sizes

In a recent Scripting Games event, I had to format some byte values into their most appropriate size according to how many bytes were present. Anyone who hangs around with me long enough will realize I love numbers and the preciseness that they provide. This is one of many reasons why I enjoy computer programming and especially working with SQL Server. Even more than my love of numbers is my hatred toward duplicating work.

This is one of my longer posts, but I promise some neat stuff if you stick with me on this…

When I started to code for the event, I headed down the path of using the PowerShell switch statement. I had never known that you could use an expression to evaluate a switch – so that was something really cool. This is the function I initially came up with (all the commenting and help removed for brevity):

switch ($a)
{
    #each step in the switch increases by a multiplier of 1024
    {$_ -lt 1024} {"{0:N2} Bytes" -f ($a)}
    {$_ -ge 1024 -and $_ -lt (1048576) } {"{0:N2} KiloBytes" -f ($a/1024)}
    {$_ -ge 1048576 -and $_ -lt (1073741824) } {"{0:N2} MegaBytes" -f ($a/1048576)}
    {$_ -ge 1073741824 -and $_ -lt (1099511627776) } {
        "{0:N2} GigaBytes" -f ($a/1073741824)}
        
    #Stop @ TeraBytes, could be more than 1024 TeraBytes, but that is acceptable
    default {{"{0:N2} TeraBytes" -f ($a/1099511627776)}}
    
}

This was a workable function, but it didn’t feel right. I had to do a lot of typing and we all know we only have so many keystrokes in our lives, so I stepped back and re-evaluated.

At each expression, I was looking at a multiplier of 1024. Like the veil was removed, I realized that these were exponents. Time for the .NET Math Class (pun intended). Among some of the other cool methods of this class is the Pow method. No, we’re not in the original Batman series fighting the super villain, it’s the equivalent of the T-SQL POWER function. I soon had a simple function and all those large nasty numbers replaced with their exponential equivalents. Here is version two:

function Get-Power
{
    
    param([double]$RaiseMe)
    
    [Math]::Pow(1024, $RaiseMe)

}

switch ($a)
{
    #each step in the switch increases by a multiplier of 1024
    {$_ -lt (Get-Power 1)} {"{0:N2} Bytes" -f ($a)}
    {$_ -ge (Get-Power 1) -and $_ -lt (Get-Power 2) } {
        "{0:N2} KiloBytes" -f ($a/(Get-Power 1))}
        
    {$_ -ge (Get-Power 2) -and $_ -lt (Get-Power 3) } {
        "{0:N2} MegaBytes" -f ($a/(Get-Power 2))}
        
    {$_ -ge (Get-Power 3) -and $_ -lt (Get-Power 4) } {
        "{0:N2} GigaBytes" -f ($a/(Get-Power 3))}
        
    #Stop @ TeraBytes, could be more than 1024 TeraBytes, but that is acceptable
    default {"{0:N2} TeraBytes" -f ($a/(Get-Power 4))}
    
}

This worked the same and it was a little cleaner, but it still bothered me. There was a lot of copied and pasted code in there and it was all to determine what text to slap on the end of a quotient. I once again stepped back and realized that it was the switch’s fault. I then made it my goal to eliminate the switch.

In the process, I remembered that 1024 is 2^10… this meant that 1024^2 was 2^20, 1024^3 was 2^30 and so on. Believe it or not, this made my path clear.

A quick Algebra refresher – exponents and logs are functionally the same things, just expressed in a way that makes each valuable for different circumstances. For instance, 2^10=1024 is the functional equivalent of LOG_2(1024) = 10. For our purposes, we will want to know the power of 2 each number being provided is. To find the unknown “power” value in an equation, (the 10 above) the log base is technically irrelevant. With that in mind, we can use the natural logarithm (ln) to determine what power of 2 any number represents.

2^x = 1024 bytes
ln(2^x) = ln(1024 bytes)
ln(2)x = ln(1024 bytes)
x = ln(1024) / ln(2)
x = 6.93 / 0.693
x = 10

Before you think, that’s great, thanks for the math lesson and stop reading - this information really does have some decent uses in PowerShell. Let’s see how this math can be applied to make my function above re-usable and easily maintainable code.

The Math class has a method named Log . When only provided with a single value, this function will use “e” as the base – the natural algorithm which is the functional equivalent of “ln” used above. Armed with this function, we can determine which power of 2 any particular number is. More importantly, this information can eventually be used to format any size of number appropriately.

In the code below, I use PowerShell to extract the closest whole power of 2 that makes up the $byte value by implementing the Floor method.

$PowerOfTwo = [Math]::Floor([Math]::Log($byte)/[Math]::Log(2))

If I now create an array of my “descriptors”, I can use this text to be added to the end of each of the numbers to make it pretty.

$ByteDescriptors = ("B", "KB", "MB", "GB", "TB", "PB", 
                "EB", "ZB", "YB", "WYGTMSB")

With the array prepared, the $PowerOfTwo variable can be divided by 10 (and Floored) to provide the index into the descriptor array.

$DescriptorID = [Math]::Floor($PowerOfTwo/10)

Finally, we use the format method to combine all this information into an output. The $Scale variable is set in the script to be 2. Not only is the $DescriptorID used to determine the description, it is also used as a power of 2 in the divisor with the total byte value as the dividend.

Write-Output ("{0:N$Scale} $($ByteDescriptors[$DescriptorID])" -f (
                $byte / [Math]::Pow(2, ($DescriptorID*10))))

I’ve included the full function below which includes all of these pieces as well as full comments and a few extra parameters. While I may have been able to use the first function, the flexibility that the most recent iteration of this script provides seems worth the effort. Not only do I think this is a neat function, I now have an answer for my kids when they ask “When am I ever going to use this stuff in real life?” :-)

function Format-Byte
{
<#    
    .SYNOPSIS
    Formats a number into the appropriate byte display format.

    .DESCRIPTION
    Uses the powers of 2 to determine what the appropriate byte descriptor
    should be and reduces the number to that appropriate descriptor.
    
    The LongDescriptor switch will switch from the generic "KB, MB, etc."
    to "KiloBytes, MegaBytes, etc."
    
    Returns valid values from byte (2^0) through YottaByte (2^80).
    
    .PARAMETER ByteValue        
    Required double value that represents the number of bytes to convert.
    
    This value must be greater than or equal to zero or the function will error.

    This value can be passed as a positional, named, or pipeline parameter.    
    
    .PARAMETER LongDescriptor
    Optional switch parameter that can be used to specify long names for 
    byte descriptors (KiloBytes, MegaBytes, etc.) as compared to the default
    (KB, MB, etc.) Changes no other functionality.
    
    .PARAMETER Scale
    Optional parameter that specifies how many numbers to display after 
    the decimal place.
    
    The default value for this parameter is 2. 
    
    .EXAMPLE
    Format-Byte 123456789.123
    
    Uses the positional parameter and returns returns "117.74 MB"
    
    .EXAMPLE
    Format-Byte -ByteValue 123456789123 -Scale 0
    
    Uses the named parameter and specifies a Scale of 0 (whole numbers). 
    
    Returns "115 GB"
    
    .EXAMPLE
    Format-Byte -ByteValue 123456789123 -LongDescriptor -Scale 4
    
    Uses the named parameter and specifies a scale of 4 (4 numbers after the
    decimal) 
    
    Returns "114.9781 GigaBytes"
    
    .EXAMPLE
    (Get-ChildItem "E:\KyleScripts")|ForEach-Object{$_.Length}|Format-Byte
    
    Passes an array of the sizes of all the files in the E:\KyleScripts folder
    through the pipeline.
    
    .NOTES
    Author:    Kyle Neier
    Blog: http://sqldbamusings.blogspot.com
    Twitter: Kyle_Neier
    
    Because of the 14 significant digit issue, anything nearing 2^90
    will be marked as WYGTMSB aka WheredYouGetThatMuchStorageBytes. If you
    have that much storage, feel free to find a different function and or
    travel back in time a hundred years years or so and slap me...

#>    

    [CmdletBinding()]
    
    param(
        [parameter(
                Mandatory=$true, 
                Position=0,
                ValueFromPipeline= $true
                
            )]
             #make certain value won't break script
             [ValidateScript({$_ -ge 0 -and 
                 $_ -le ([Math]::Pow(2, 90))})] 
        [double[]]$ByteValue,
        
        [parameter(
                Mandatory=$false, 
                Position=1,
                ValueFromPipeline= $false
                
            )]
        [switch]$LongDescriptor,
        
        [parameter(
                Mandatory=$false, 
                Position=2,
                ValueFromPipeline= $false
                
            )]
            [ValidateRange(0,10)]
             [int]$Scale = 2
        
    )
    
    #2^10 = KB, 2^20 = MB, 2^30=GB...
    begin
    {
        if($LongDescriptor)
        {
            Write-Verbose "LongDescriptor specified, using longer names."
            $ByteDescriptors = ("Bytes", "KiloBytes", "MegaBytes", "GigaBytes", 
                "TeraBytes", "PetaBytes", "ExaBytes", "ZettaBytes", 
                "YottaBytes", "WheredYouGetThatMuchStorageBytes")
        }
        else
        {
            Write-Verbose "LongDescriptor not specified, using short names."
            $ByteDescriptors = ("B", "KB", "MB", "GB", "TB", "PB", 
                "EB", "ZB", "YB", "WYGTMSB")
        }
    }
    
    process
    {
        foreach($byte in $ByteValue)
        {
            #Determine which power of 2 this value is based from
            Write-Verbose "Determine which power of 2 the byte is based from."
            $PowerOfTwo = [Math]::Floor([Math]::Log($byte)/[Math]::Log(2))
            
            #Determine position in descriptor array for the text value
            Write-Verbose "Determine position in descriptor array."
            $DescriptorID = [Math]::Floor($PowerOfTwo/10)

            #Determine appropriate number by rolling back up through powers of 2
            #format number with appropriate descriptor
            Write-Verbose ("Return the appropriate number with appropriate "+
                "scale and appropriate desciptor back to caller.")
                
            Write-Output ("{0:N$Scale} $($ByteDescriptors[$DescriptorID])" -f (
                $byte / [Math]::Pow(2, ($DescriptorID*10))))
            
        }
    }
}

PowerShell and T-SQL: Implementing UNION, INTERSECT, and EXCEPT

I’ve been thoroughly enjoying my experience this year with the Scripting Games. This is the first year I've competed and I decided to participate in the Advanced category just to push myself a little harder. I’ve been using Powershell for quite some time, however, I’ve never had the pleasure of using the platform while performing the duty of a Windows administrator. Those days were all about .bat and .vbs files. It’s been a real treat to be able to get my hands dirty with some of the cmdlets and functionality of Powershell that I have yet to use.

On a couple of events, I’ve found good use of some of my T-SQL set based thinking. Most database geeks understand that the database is designed to work in sets. However, PowerShell, similar to its scripting roots, is designed to think iteratively. However, there are times when understanding how different sets of data compare with each other could be a handy concept to understand. I’m going to spend some time on some very cool operators in T-SQL and show how this same concept can be implemented in PowerShell using arrays of objects.

The T-SQL operators UNION, INTERSECT, and EXCEPT and the Compare-Object cmdlet all operate on entire sets of data. We just came out of Easter, so I encourage you to forget about data for a moment – I know, it’s hard for me too – and think of two baskets of jelly beans.

In your left basket, you have seven jelly beans, one for each color of the rainbow (ROY G BIV). On your right side, your over-sugared children have eaten ROY, leaving you with G BIV and a half eaten Peep. In the middle, we have at this moment an empty basket.

Before we start moving jelly beans around, here are some initial set ups for the T-SQL and PowerShell discussions:

T-SQL:

CREATE TABLE LeftBasket
    (
     ID INT NOT NULL,
     Candy VARCHAR(10) NOT NULL
    )

CREATE TABLE RightBasket
    (
     ID INT NOT NULL,
     Candy VARCHAR(10) NOT NULL
    )

GO
INSERT  INTO LeftBasket
        (ID, Candy)
VALUES
        (1, 'Red'),
        (2, 'Orange'),
        (3, 'Yellow'),
        (4, 'Green'),
        (5, 'Blue'),
        (6, 'Indigo'),
        (7, 'Violet')

INSERT  INTO RightBasket
        (ID, Candy)
VALUES
        (4, 'Green'),
        (5, 'Blue'),
        (6, 'Indigo'),
        (7, 'Violet'),
        (8, 'Peep')

PowerShell:

$LeftBasket = ("Red", "Orange", "Yellow", "Green", "Blue", "Indigo", "Violet")
$RightBasket = ("Green", "Blue", "Indigo", "Violet", "Peep")

UNION

The UNION operator is like taking the ROY G BIV from the left basket, the peep from the right basket, and tossing the G BIV from the right basket across the room because you can’t look at any more jelly beans. UNION will give you the distinct results of the combination of the two baskets of jelly beans. In reality, it doesn’t really toss those from the right, but it made sense in the analogy. You end up with 7 jelly beans and a peep in your middle basket.

This can be represented in T-SQL with the following query (based upon the set up mentioned earlier:

SELECT ID, Candy FROM LeftBasket
UNION
SELECT ID, Candy FROM RightBasket
ORDER BY ID

In PowerShell, we can also represent this same concept using Compare-Object

Compare-Object -ReferenceObject $LeftBasket -DifferenceObject $RightBasket ` 
 -IncludeEqual

The important point of the above command is the –IncludeEqual switch. This switch modifies the behavior of Compare-Object to not just show differences, but to show you all of the values that are the same as well. The results are identical to the UNION operator in T-SQL.

UNION ALL

UNION ALL has a similar function as UNION, but it doesn’t take the time to determine which to throw out and just dumps all the candy in the middle basket. You end up with 11 jelly beans (ROY GG BB II VV) and a peep in your middle basket.

T-SQL:

SELECT ID, Candy FROM LeftBasket
UNION ALL
SELECT ID, Candy FROM RightBasket
ORDER BY ID

PowerShell:

Write-Output ($LeftBasket,$RightBasket)

You’ll notice that I did not use the Compare-Object for the UNION ALL equivalent. This is because no combination of switches for the Compare-Object will show you duplicates. Since all we really want is just the combination of the two string arrays, we can combine them and replicate the results of UNION ALL.

INTERSECT

If you apply the INTERSECT operator to your baskets of jelly beans, you end up with only four jelly beans (G BIV) in the middle. This is because INTERSECT represents the jelly beans that are in common in both baskets. Anything unique on either side is tossed out.

T-SQL:

SELECT ID, Candy FROM LeftBasket
INTERSECT
SELECT ID, Candy FROM RightBasket
ORDER BY ID

PowerShell:

Compare-Object -ReferenceObject $LeftBasket -DifferenceObject $RightBasket `
 -IncludeEqual -ExcludeDifferent

You’ll notice in the PowerShell command that there are two optional switches. We implement the –ExcludeDifferent and the –IncludeEqual switches to make the output not include differences and include any matches. We get only the matched items of the arrays.

EXCEPT

Finally, EXCEPT. This one will take any jelly beans on the left side that do not exist on the right side and put them in your middle basket. In this case, you would have ROY.

T-SQL:

SELECT ID, Candy FROM LeftBasket
EXCEPT
SELECT ID, Candy FROM RightBasket
ORDER BY ID

Powershell:

Compare-Object -ReferenceObject $LeftBasket -DifferenceObject $RightBasket | 
 Where-Object{$_.SideIndicator -eq "<="}

In this PowerShell command, I am adding no switches. Compare-Object is working with default behavior. However, in order to get only those items that exist on the left, we have to implement the Where-Object in the pipeline to get only those that exist on the left and not on the right.

Now, the coolest part about EXCEPT is that if you swap the baskets, you end up with different results. Starting from the right basket, you would end up with nothing more than a peep because all of the jelly beans on the right exist in the set of jelly beans on the left.

T-SQL:

SELECT ID, Candy FROM RightBasket
EXCEPT
SELECT ID, Candy FROM LeftBasket
ORDER BY ID

PowerShell:

Compare-Object -ReferenceObject $RightBasket -DifferenceObject $LeftBasket | 
 Where-Object{$_.SideIndicator -eq "<="}

Ok, if you’re not ready to throw up from all the sugar yet, there is a small gotcha with Compare-Object . If you did a Get-Help on this cmdlet, you may have noticed the –SyncWindow parameter. This parameter tells PowerShell how many array objects in either direction to look for similarities. In PowerShell 1.0, this was a default of 5. This meant that if your two arrays were not ordered by the same property and you had the same object stored more than five items away, you could end up with inaccurate results. Fortunately, in PowerShell 2.0, they have modified this default to [Int32]::MaxValue – so we should be covered.


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

T-SQL Tuesday #029 – SQL 2012 and Treating TempDB like it’s really Temporary.

A while back, my young son thought it would be fun to use a CD as a virtual surfboard on our kitchen floor. While he was a very proud boy, all I could think of was how much that CD cost and how unlikely it was that we were going to ever be able to listen to it again.

As my kids got older, I found significant value in exercising my right to make a copy my children’s CDs for personal use. It became part of the process of buying a new CD to copy it and put the original in a safe place – far away from little hands. If the CD copy gets scratched or otherwise destroyed, it is as simple as pulling out the original and making another copy.

We use these copies extensively and, while we encourage treating them properly, life happens. They get left outside, in the car, under the couch, you get the picture. Similarly, we use TempDB extensively, and ever more so as more features of SQL Server use TempDB internally.

In SQL 2008R2 and older, TempDB had to be on a shared disk when implementing SQL Server in a cluster.

This may not seem like a big deal, but think back to the CD analogy. Our user databases are like the original CDs. We need to keep them protected and safe from the children. We keep the databases spread across redundant disks for the highest resiliency. We configure security to be certain that only those who are responsible enough can touch the databases. TempDB is like the copies. Anyone can use it and it gets used all the time by both users and by the system. We have little concern and generally little control over how TempDB gets used.

With the shared disk requirement for TempDB, we have to treat our CD copies in a similar fashion as the originals (the user databases). The data is redundantly protected and, in several cases, spread across the same disks as the user data. Getting the performance necessary out of TempDB in some cases was difficult.

In SQL 2012, this has been changed. A very useful feature of SQL 2012 is the ability to put TempDB on a local disk, even when implementing SQL Server on a cluster. This prevents the use of expensive SAN storage for something temporary and volatile. It also introduces the ability to significantly increase performance of some key functionality of SQL Server (Snapshot Isolation, sorts, hash, CheckDB, etc.) by opening up more high-performance options in the local box. SAN based SSD is possible for shared storage, however, enterprise class shared SSD is still incredibly expensive. Local SSD is not as cheap as spinning disk, but is cheaper, and in some cases, faster than SAN based SSD.

Moving TempDB local is not the ultimate solution for all environments and the significance of this feature may not be universal. However, it is another very useful tool in the DBA toolbox that has the potential to reduce the cost of and increase the performance of a SQL Server deployment.

Links to Microsoft concerning this new feature:
http://msdn.microsoft.com/en-us/library/bb500459%28v=SQL.110%29.aspx
http://msdn.microsoft.com/en-us/library/ms143506.aspx#storagetypes

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

Powershell – Validating an IP Address

 

A task that I have had to do several times over the years is determine if an IP Address string entered from a prompt or imported from a file is indeed valid. Even more, determining if that IP Address is in use once verifying that the IP Address is well formed.

I’ve created the following Powershell function – Test-IPAddressString – to handle both of these tasks.

Either provide a single or several IP Address candidates to this function and it will return a $true if it is valid or a $false if it is not. As an added bonus, if you enable the –FailIfInUse swtich,the function will attempt to ping a valid IP Address. If the address responds to a ping, $false is returned.

function Test-IPAddressString
{
<#
        .SYNOPSIS
        Tests to see if an IP address string is a valid address and can determine
        if IP Address is responding on network.

        .DESCRIPTION
        Implements the .Net.IPAddress classes to validate that the string provided
        can parse into an IP Address. If the parse is successful, the function returns
        boolean $true unless the -FailIfInUse switch is provided.
        
        If -FailIfInUse is specified, a $true is only returned if the IP Address
        does not respond to a ping three times.

        This function supports the -Verbose switch as well.
        
        .PARAMETER IPaddressString
        This is a string value representing the address that you want to test.
        
        This value can be passed either from pipeline or as a parameter.
        
        .PARAMETER FailIfInUse 
        This is a switch parameter. Specifying this in the parameter list will
        cause the IP Address, if successfully parsed, to attempt to be pinged.
        
        A successful ping result will result in a $false value being returned
        if this switch is used.
        
        
        .EXAMPLE
        Test-IPAddressString -IPAddressString "192.168.1.1"
        
        Using full parameter name

        .EXAMPLE
        Test-IPAddressString "192.168.1.1" -FailIfInUse
        
        Using Positional parameters and specifiying to fail test if IP Address responds to ping.
        
        .EXAMPLE
        ("192.168.1.1", "192.123456", "IsThisAnIP") | Test-IPAddressString -Verbose
        
        Passing serveral potential IP Address strings to the function through the pipeline. 
        
        FailIfInUse is ommitted, so no ping attempts will be made on valid IP Addresses.
        
        This function supports the verbose switch. Using this switch will provide you with
        several indicators of progress through the process.
        
        .NOTES
        
        Author:    Kyle Neier
        Blog: http://sqldbamusings.blogspot.com
        Twitter: Kyle_Neier
        
        .LINK
        http://sqldbamusings.blogspot.com/2012/04/powershell-validating-ip-address.html
    #>

    param(
    [parameter(
            Mandatory=$true, 
            Position=0,
            ValueFromPipeline= $true
        )]
        [string]$IPaddressString,
    [parameter(
            Mandatory=$false, 
            Position=1,
            ValueFromPipeline= $false
        )]
        [switch]$FailIfInUse
    )

    process
    {
        [System.Net.IPAddress]$IPAddressObject = $null    
        if([System.Net.IPAddress]::tryparse($IPaddressString,[ref]$IPAddressObject) -and
             $IPaddressString -eq $IPAddressObject.tostring())
        {
            Write-Verbose "$IPaddressString successfully parsed."
            if($FailIfInUse -eq $true)
            {
                $Pinger = new-object System.Net.NetworkInformation.Ping
                $p = 1
                $p_max = 3
                do 
                {
                    Write-Verbose "Attempting to ping $IPaddressString - Attempt $p of $p_max"
                    $PingResult = $Pinger.Send("$IPaddressString")
                    Write-Verbose "Connection Result: $($PingResult.Status)"
                    $p++
                    Start-Sleep -Milliseconds 500

                } until ($PingResult.Status -eq "Success" -or $p -gt $p_max)

                if($PingResult.Status -eq "Success")
                {
                    Write-Verbose "The IP Address $IPAddressString parsed successfully but is responding to ping."
                    Write-Output $false
                }
                else
                {
                    Write-Verbose "The IP Address $IPAddressString parsed successfully and is not responding to ping."
                    Write-Output $true
                }
            }
            else
            {
                Write-Verbose "The IP Address $IPAddressString parsed successfull - No ping attempt made."
                Write-Output $true
            }
        
        }
        else
        {
            Write-Verbose "The IP Address $IPAddressString could not be parsed."
            Write-Output $false
        }
    }
}

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