Executing a powershell script from multiple environments result in different outcomes

Question

I have a Powershell script working perfectly fine when executed from Visual Studio Code powershell terminal.

When I try to run the same script from c# code, or trying to Run as PowerShell from Windows explorer, I get the following error:

New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: verify that the assembly
containing this type is loaded.
At line:32 char:18
+         return @(& $origNewObject @psBoundParameters)
+                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

[...]

Script code is as follow:

try {
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    Start-Sleep -Seconds 5
}
catch {
    Write-Output "Failed to load the SMO assembly. Error: $($_.Exception.Message)"
    return
}

$SMOServerConn = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection")
$SMOServerConn.ServerInstance="xxx.xxx.xxx.xxx"
$SMOServerConn.LoginSecure=$false
$SMOServerConn.Login='xxxxxx'
$SMOServerConn.Password='xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

$SMOserver = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") #-argumentlist $server
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SMOServerConn)
$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"  

$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item('xxxxx')

$maxAttempts = 10
$attempt = 0

# Loop until the connection is established or the maximum number of attempts is reached
while ($db.ConnectionContext.IsOpen -eq $false -and $attempt -lt $maxAttempts) {
    $attempt++
    Write-Output "Attempt $($attempt): Connecting to database..."

    # Wait for a short duration before attempting the next connection check
    Start-Sleep -Seconds 1
}


$scripter = new-object ("$SMOserver") $srv

$line="[cccauth].[spGetTrxReconSummary]"
$line = $line.replace('[','').replace(']','')
$splitline =$line.Split(".")

$Objects = $db.storedprocedures[$splitline[1], $splitline[0]]

Write-Host "Extracting stored procedure $($line)"
Write-Host "--------------------------------"
        
$sp_extracted+=$Scripter.Script($Objects)
$sp_extracted=$sp_extracted.replace("SET QUOTED_IDENTIFIER ON", "SET QUOTED_IDENTIFIER ON `r`nGO`r`n")

Write-Host $sp_extracted -ForegroundColor Green

Write-Host "Scripting DB Finished... Press any key..."
[void][System.Console]::ReadKey($true)

If I implement the logic to connect to a second server/database on the script, then this second connection is working.

Does anyone know what is wrong here?

Thank you in advance.

Answer 1

It's not clear what kind of C# project you created or whether you're using .NET or .NET Framework. Also, I'm not familiar with VS Code, but the following may be helpful.

According to Global assembly cache APIs are obsolete:

.NET Core and .NET 5 and later versions eliminate the concept of the global assembly cache (GAC) that was present in .NET Framework. As such, all .NET Core and .NET 5+ APIs that deal with the GAC either fail or perform no operation.

According to Differences between Windows PowerShell 5.1 and PowerShell 7.x:

Windows PowerShell 5.1 is built on top of the .NET Framework v4.5. With the release of PowerShell 6.0, PowerShell became an open source project built on .NET Core 2.0. Moving from the .NET Framework to .NET Core allowed PowerShell to become a cross-platform solution. PowerShell runs on Windows, macOS, and Linux.

According to PowerShell Add-Type:

If you submit an assembly file, Add-Type takes the types from the assembly. To specify an in-memory assembly or the global assembly cache, use the AssemblyName parameter.

If using .NET 5+, it seems that one should specify -Path instead of -AssemblyName. If using .NET Framework one can use either Path or AssemblyName (if the DLL is in the global assembly cache).

If using .NET Framework, to check if Microsoft.SqlServer.Smo exists in the global assembly cache, open a cmd window and type:

"%ProgramFiles(x86)%\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 Tools\gacutil.exe" /l Microsoft.SqlServer.Smo

When specifying a path, no matter how one attempts to run the PowerShell script, the path to Microsoft.SqlServer.Smo.dll needs to be known. If one desires to run the script in Windows Explorer, one can specify the fully-qualified path to the assembly, or may be able to add the path to the Windows Path environment variable.

Where are the DLLS? When one installs SQL Server Express, the DLLs are installed in %ProgramFiles%\Microsoft SQL Server\100\SDK\Assemblies. If one adds the Microsoft.SqlServer.SqlManagementObjects NuGet package to one's project, the DLLs will be downloaded to %UserProfile%\.nuget\packages\microsoft.sqlserver.sqlmanagementobjects\<version>\lib\<version>. Then when the project is compiled, they'll be copied to the project output folder (ex: bin\Debug\net5.0-windows).

For C#, add the Microsoft.SqlServer.SqlManagementObjects NuGet package to the project.

Create a new Window Forms App (for Framework, choose.NET 6)

Add the following NuGet packages to your project:

• Microsoft.PowerShell.SDK (v.7.2.11)
• Microsoft.SqlServer.SqlManagementObjects (v.170.18.0)

Add the following using directives

• using System.Management.Automation;
• using System.Management.Automation.Runspaces;

Specifying the version of Microsoft.SqlServer.Smo.dll shouldn't be necessary. Try the following:

Add-Type -Path "Microsoft.SqlServer.Smo.dll"

Script:

try {
    Add-Type -Path "Microsoft.SqlServer.Smo.dll"
    Start-Sleep -Seconds 5
    Write-Output "Assembly loaded."
}
catch {
    Write-Output "Failed to load the SMO assembly. Error: $($_.Exception.Message)"
}

$SMOServerConn = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection')
$SMOServerConn.ServerInstance="127.0.0.1"

Write-Output "Complete"

RunPowerShell

private void RunPowerShell(string scriptText)
{
    InitialSessionState iss = InitialSessionState.CreateDefault();
    iss.ExecutionPolicy = Microsoft.PowerShell.ExecutionPolicy.RemoteSigned; //set execution policy

    using (Runspace runspace = RunspaceFactory.CreateRunspace(iss))
    {
        //open
        runspace.Open();

        using (PowerShell ps = PowerShell.Create(runspace))
        {
            var psInstance = PowerShell.Create();

            psInstance.AddScript(scriptText);

            System.Collections.ObjectModel.Collection<PSObject> outputCollection = psInstance.Invoke();

            foreach (PSObject outputItem in outputCollection)
            {
                //create reference
                string? data = outputItem.BaseObject?.ToString();

                System.Diagnostics.Debug.WriteLine(outputItem.ToString());
            }
        }
    }
}

Also see:

• Choosing the right PowerShell NuGet package for your .NET project
• How To support different Powershell-Versions in Visual Studio Cmdlet DLL