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.

As there is no $origNewObject in your script, I quess it could be something in your PowerShell profile? — iRon
– iRon, Commented Jun 2, 2023 at 17:55
Please output $PSVersionTable from your script. Maybe you are running under different PowerShell versions. — zett42
– zett42, Commented Jun 2, 2023 at 19:21
Break your script apart and start running it line by line on both systems to see how it's supposed to work, and which part(s) scream. If you go back to the same system that was not working after it did work sending to the other system, does it work on subsequent runs against that same system which initially did not work? One time fluke, some sort of extension used in VSCode not full loaded in PowerShell per your logic, etc. rule those out just in case some oddball issue like that. — Bitcoin Murderous Maniac
– Bitcoin Murderous Maniac, Commented Jun 3, 2023 at 2:28
This sounds like a bittiness issue. Possibly you only have Microsoft.SqlServer.Smo installed in GAC_64 so the 32 bit versions of PowerShell can't see it, or maybe vice-versa. — AlwaysLearning
– AlwaysLearning, Commented Jun 3, 2023 at 7:43
First check log files in SQL Server using SQL Server Management Studio in the explorer under Management and check the account (group) that was used when script worked and did not work. It sound like when code worked you were using an Admin Account and when it didn't work you were not using an Admin Account. Power shell does not automatically run As Admin. Try running PS by right click on PS shortcut and select Run As Admin. — jdweng
– jdweng, Commented Jun 3, 2023 at 15:34

It all makes cents · Accepted Answer · 2023-06-03 23:34:39Z

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:

Collectives™ on Stack Overflow

Executing a powershell script from multiple environments result in different outcomes

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related