WARNING: Multiple versions of Microsoft.SqlServer.SMO are loaded; reverting to the lowest version to avoid problems

Feb 25 at 4:29 PM
When I execute Get-SQLServerInventoryTOClixml.ps1 I receive this message. Is there an override to use the newest? For my SQL 2012 servers I am getting limited information on them, and I believe this is the cause.

Thank you.
--JD
Coordinator
Mar 5 at 4:22 PM
Hi Jim,
SQL Power Doc was written to be agnostic to the version of SMO but as you've noticed if multiple versions are present you'll get the warning dialog - this is a safety net to avoid throwing errors when methods that aren't found in earlier versions are called.

If you want to force a specific version of SMO to be loaded you'll have to modify code in a few places. Note the examples below are specific to SMO for SQL 2012.

In SqlServerDatabaseEngineInformation.psm1 at the end of the file, replace
# Load SMO assembly, and if we're running SQL 2008 DLLs or higher load the SMOExtended and SQLWMIManagement libraries
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
    $SmoMajorVersion = $_.GetName().Version.Major
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLEnum') | Out-Null
    if ($SmoMajorVersion -ge 10) {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
    }
}

# Now check which SMO assemblies are loaded and set $SmoMajorVersion to the lowest version
[System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object { $_.FullName -ilike 'Microsoft.SqlServer.SMO, Version=*' } | ForEach-Object {
    if ($_.GetName().Version.Major -lt $SmoMajorVersion) {
        $SmoMajorVersion = $_.GetName().Version.Major
        Write-SqlServerDatabaseEngineInformationLog -Message "Multiple versions of Microsoft.SqlServer.SMO are loaded; reverting to the lowest version to avoid problems" -MessageLevel Warning
    }
}
with
# Load SMO assembly, and if we're running SQL 2008 DLLs or higher load the SMOExtended and SQLWMIManagement libraries
$SmoAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMO.dll'
$SQLEnumAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SQLEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SQLEnum.dll'
$SMOExtendedAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SMOExtended\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMOExtended.dll'
$SQLWMIManagementAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SQLWMIManagement\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SQLWMIManagement.dll'


[System.Reflection.Assembly]::LoadFrom($SmoAssemblyPath) | ForEach-Object {
    $SmoMajorVersion = $_.GetName().Version.Major
    [System.Reflection.Assembly]::LoadFrom($SQLEnumAssemblyPath) | Out-Null
    if ($SmoMajorVersion -ge 10) {
        [System.Reflection.Assembly]::LoadFrom($SMOExtendedAssemblyPath) | Out-Null
        [System.Reflection.Assembly]::LoadFrom($SQLWMIManagementAssemblyPath) | Out-Null
    }
}

# Now check which SMO assemblies are loaded and set $SmoMajorVersion to the lowest version
[System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object { $_.FullName -ilike 'Microsoft.SqlServer.SMO, Version=*' } | ForEach-Object {
    if ($_.GetName().Version.Major -lt $SmoMajorVersion) {
        $SmoMajorVersion = $_.GetName().Version.Major
        Write-SqlServerDatabaseEngineInformationLog -Message "Multiple versions of Microsoft.SqlServer.SMO are loaded; reverting to the lowest version to avoid problems" -MessageLevel Warning
    }
}
And in NetworkScan.psm1, starting at around line 1293 (depending on the version you're running), replace
# Load SMO Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
    if ($_.GetName().Version.Major -ge 10) {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
    }
}
with
# Load SMO Assemblies
$SmoAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMO.dll'
$SQLEnumAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SQLEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SQLEnum.dll'
$SMOExtendedAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SMOExtended\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMOExtended.dll'
$SQLWMIManagementAssemblyPath = 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SQLWMIManagement\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SQLWMIManagement.dll'

[System.Reflection.Assembly]::LoadFrom($SmoAssemblyPath) | ForEach-Object {
    $SmoMajorVersion = $_.GetName().Version.Major
    [System.Reflection.Assembly]::LoadFrom($SQLEnumAssemblyPath) | Out-Null
    if ($SmoMajorVersion -ge 10) {
        [System.Reflection.Assembly]::LoadFrom($SMOExtendedAssemblyPath) | Out-Null
        [System.Reflection.Assembly]::LoadFrom($SQLWMIManagementAssemblyPath) | Out-Null
    }
}
The specific paths to your assemblies may be different depending on what version you're running and how your system is set up. Let me know if you run into any problems putting this in place.

--Kendal