powershellsmo

SMO Powershell broken even with loading assemblies

I have been working all day on a powershell script, when all of a sudden it broke. Here's an example of what's not working:

#load assemblies for SMO
    $assemblylist =   
    "Microsoft.SqlServer.Management.Common",  
    "Microsoft.SqlServer.Smo",  
    "Microsoft.SqlServer.Dmf ",  
    "Microsoft.SqlServer.Instapi ",  
    "Microsoft.SqlServer.SqlWmiManagement ",  
    "Microsoft.SqlServer.ConnectionInfo ",  
    "Microsoft.SqlServer.SmoExtended ",  
    "Microsoft.SqlServer.SqlTDiagM ",  
    "Microsoft.SqlServer.SString ",  
    "Microsoft.SqlServer.Management.RegisteredServers ",  
    "Microsoft.SqlServer.Management.Sdk.Sfc ",  
    "Microsoft.SqlServer.SqlEnum ",  
    "Microsoft.SqlServer.RegSvrEnum ",  
    "Microsoft.SqlServer.WmiEnum ",  
    "Microsoft.SqlServer.ServiceBrokerEnum ",  
    "Microsoft.SqlServer.ConnectionInfoExtended ",  
    "Microsoft.SqlServer.Management.Collector ",  
    "Microsoft.SqlServer.Management.CollectorEnum",  
    "Microsoft.SqlServer.Management.Dac",  
    "Microsoft.SqlServer.Management.DacEnum",  
    "Microsoft.SqlServer.Management.Utility",
    "Microsoft.SqlServer.Management.Smo"


    foreach ($asm in $assemblylist)  
    {  
        $asm = [Reflection.Assembly]::LoadWithPartialName($asm)  
    }

$PrimaryInstance = "myserver"
$PrimaryConnection = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $PrimaryInstance

I get this error:

New-Object : Cannot find type [Microsoft.SqlServer.Management.SMO.Server]: verify that the assembly containing this type is loaded. At line:30 char:22

  • ... onnection = New-Object ('Microsoft.SqlServer.Management.SMO.Server') ...
  •             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
    • FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

I went so far as to reboot everything and this is still breaking. Coworkers who run this in other places say it works for them.

I have confirmed that I can still connect to servers in SSMS, but this still fails for me no matter what server I try to connect to.

Solution

  • Not sure what changed, but uninstalling and reinstalling the SQLServer module and then adding import-module sqlserver at the top of the script seems to have fixed it