Throwing missing assembly in powershell.exe but not ISE

[CLC-ZS]

Having an issue, I built a tool in Powershell ISE using SimplySQL and, though it works the ISE, when I try to run it in powershell.exe, it throws an error:

New-Object : Cannot find type [System.Collections.Generic.Queue[SqlMessage]]: verify that the assembly containing this
type is loaded.
At line:32 char:16
+         return & $origNewObject @psBoundParameters
+                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

I've looked at the other submitted issue that is similar (but different) and pre-emptively run this command for you:

Import-Module SimplySql -Force
Get-Module SimplySql | Select-Object ModuleType, Version, Name
(Get-Module SimplySql).ExportedCommands.Keys | Where-Object { $_ -like "open*"}

Output:

ModuleType Version Name
---------- ------- ----
    Script 1.6.2   SimplySql
Open-MySqlConnection
Open-OracleConnection
Open-PostGreConnection
Open-SqlConnection
Open-SQLiteConnection

I've tried a number of things to try to get it to work (here's a non-comprehensive list):

• adding the assembly directly via add-type -path
• adding the assembly via add-type -name
• checking the $env:profile and setting the .exe to have the same results for $env:profile
• compared all the assemblies loaded between the 2, found the 21 differences and loaded those
• tried to copy the content of Classes.ps1 in the module folder straight into SimplySql.psm1 (over the line where it dot-sources that classes file).

Willing to troubleshoot if you have any thoughts, not sure why ISE works and not .exe; otherwise I will have to find another way to connect to the db. Thanks :)

oh, also:

PS C:\Windows\system32> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      5.1.18362.628
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.18362.628
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

[mithrandyr]

@CLC-ZS So.. lets do some simply checks. First launch powershell commandline (powershell.exe) without a profile powershell -noprofile

Then...

# completely remove the powershell module (b/c it has been tweaked)
Get-Module simplysql -ListAvailable |
    Select-Object -ExpandProperty ModuleBase
    | Remove-Item -Recurse -Force

# reinstall the module
Install-Module SimplySql

# quick check of files in module... should be 155
Get-Module simplysql -ListAvailable |
    Where-Object version -eq 1.6.2 |
    Select-Object -ExpandProperty ModuleBase |
    Get-ChildItem -Recurse |
    Measure-Object

# lear errors
$error.Clear()

# import the module & check what was imported
Import-Module SimplySql -Verbose

# you should see output like
<#
VERBOSE: Importing function 'Clear-SqlMessage'.
VERBOSE: Importing function 'Close-SqlConnection'.
VERBOSE: Importing function 'Complete-SqlTransaction'.
VERBOSE: Importing function 'Get-SqlConnection'.
VERBOSE: Importing function 'Get-SqlMessage'.
VERBOSE: Importing function 'Invoke-SqlBulkCopy'.
VERBOSE: Importing function 'Invoke-SqlQuery'.
VERBOSE: Importing function 'Invoke-SqlScalar'.
VERBOSE: Importing function 'Invoke-SqlUpdate'.
VERBOSE: Importing function 'Open-MySqlConnection'.
VERBOSE: Importing function 'Open-OracleConnection'.
VERBOSE: Importing function 'Open-PostGreConnection'.
VERBOSE: Importing function 'Open-SqlConnection'.
VERBOSE: Importing function 'Open-SQLiteConnection'.
VERBOSE: Importing function 'Set-SqlConnection'.
VERBOSE: Importing function 'Show-SqlConnection'.
VERBOSE: Importing function 'Start-SqlTransaction'.
VERBOSE: Importing function 'Test-SqlConnection'.
VERBOSE: Importing function 'Undo-SqlTransaction'.
VERBOSE: Importing function 'xTest'.
VERBOSE: Importing alias 'csc'.
VERBOSE: Importing alias 'gsc'.
VERBOSE: Importing alias 'isq'.
VERBOSE: Importing alias 'iss'.
VERBOSE: Importing alias 'isu'.
VERBOSE: Importing alias 'ssc'.
VERBOSE: Importing alias 'tsc'.
#>

# lets check to see if there are any errors on load.
$error.count

<# if there are errors, post a summary to this thread & upload a file with the full details
(if file is more than 1 mb, reduce the depth to 3) #>
$error # get a summary
$error | Export-Clixml -Path SimplySqlErrors.clixml -Depth 4 # create a file with full details

# Lets do a simple test and see if there are any errors
Open-SQLiteConnection
Invoke-SqlUpdate -Query "Create Table test (someVal string)"
Invoke-SqlUpdate -Query "INSERT INTO test (someval) VALUES (@val)" -Parameters @{val = "Hello, world!"}
Invoke-SqlQuery -Query "SELECT * FROM test"
Close-SqlConnection

<# results should be like this (if not, check errors, etc)
0
1

someVal
-------
Hello, world!
#>

If all of this works, then it would seem that something you are doing in your tool is causing a problem -- see if you can extract the relevant SimplySql portions of code and run them from powershell.exe.

[mithrandyr]

@CLC-ZS Following up -- the line of code that is erroring is not anywhere in my source code -- so I don't think my module is the problem... but again, if you can share more details -- happy to take a look.

[CLC-ZS]

Sorry I haven't responded, I've been out sick, I will test this tomorrow :) And yeah, I looked high and low and couldnt find that line anywhere in the source code either...it's quite odd indeed... but again, will test tomorrow (or the day after if I get worse)

[CLC-ZS]

@mithrandyr Okay, so here goes

I copied all the code in your test into a file and saved it I opened powershell as an admin Called the to run the code as powershell -noprofile -file "C:\Users\UserName\Documents\_Scripts\Powershell\_testing\troubleshooting.ps1"

it prompted to install the modules, I hit [Y] Yes

Files in Module

Count    : 154
Average  :
Sum      :
Maximum  :
Minimum  :
Property :

Verbose output from the import module was identical to what you posted

Error Count: 0

When it tried to open the SQLite connection:

New-Object : Cannot find type [System.Collections.Generic.Queue[SqlMessage]]: verify that the assembly containing this
type is loaded.
At line:32 char:16
+         return & $origNewObject @psBoundParameters
+                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Basically the same error as before.... no reason why :( Only thing I really noticed as wrong was that the files included showed 154 but you said 155, other than that, no issues besides opening the connection that fails, just like with the MySQL cmdlet

[mithrandyr]

@CLC-ZS, ok I've attached a fileList -- you can get something similar by navigating to your modules\simplysql\1.6.2 directory and running:

gci -recurse -name | sort

fileList.txt Do a compare and lets see what which file is missing..

also -- here is a zip file with 1.6.2 from my system ... you can try that (extract it somewhere.. make sure you have a clear powershell session.. use import-module with a direct path. 1.6.2.zip

[mithrandyr]

Closed as unreproducible

[MartynKeigher]

@CLC-ZS - I'm also seeing this issue. Downloaded the module a couple of days ago and I get it often.

Was you able to resolve this at all, or did the issue go away??

[mithrandyr]

@CLC-ZS -- I am willing to reopen this issue, please read through the discussion and see if where you experience is the same or different from the original submitter. Also, please provide additional information (OS, Powershell Version, verify module version -- file count in the folder, etc). Thanks

[MartynKeigher]

Hey @mithrandyr, and thank you for re-opening this. I installed the module about 3 or 4 days ago, using the following command:

Install-Module SimplySQL -Scope AllUsers -SkipPublisherCheck -AllowClobber -Force

I am running this Module on a Server 2019 & 2016 OS. Both servers are on pwsh 5.1 The version of the module installed is 1.6.2

PS C:\Users\mkadm> $PSVersionTable.PSVersion
Major  Minor  Build  Revision
-----  -----  -----  --------
5      1      17763  1432    

PS C:\Users\mkadm> Get-Module -ListAvailable | ?{$_.Name -match 'sql'}
    Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version    Name                                ExportedCommands                                                                                              
---------- -------    ----                                ----------------                                                                                              
Script     1.6.2      SimplySql                           {Undo-SqlTransaction, Get-SqlMessage, Invoke-SqlUpdate, Invoke-SqlScalar...}                                  

PS C:\Users\mkadm> cd "C:\Program Files\WindowsPowerShell\Modules\SimplySql"
PS C:\Program Files\WindowsPowerShell\Modules\SimplySql> (Get-ChildItem -Recurse | Measure-Object).Count
155

I am using this module to perform simple SELECT COUNT(*) statements on a MySQL db. Also, as stated by @CLC-ZS when he opened this issue.. the module works fine when used in an ISE session, but not when used at the Powershell or CMD prompt (calling PowerShell).

Again, appreciate you willing to take another look at this!

[mithrandyr]

@MartynKeigher -- when you are launching powershell -- are you using a -noprofile switch? Please try that, import the module and reproduce the problem -- attach the sequence of commands that leads up to the error(s) and then the full error(s). Thanks!

[MartynKeigher]

Hey @mithrandyr, So I did a little more testing on this and I'm happy to report that this is NOT an issue with the module at all. It is more so my AV solution.

With AV enabled: It works fine in PowerShell ISE, but both PowerShell & CMD prompts, the execution is blocked (with the error message reported previously New-Object : Cannot find type [System.Collections.Generic.Queue[SqlMessage]]

With AV disabled: It works fine in Powershell ISE, Powershell prompt AND cmd prompt.

The issue I am running into, imho is beyond the scope of this project\module..... there is obviously something more 'over-arching' at play here.

I would be fine with you re-closing this issue, although I do have 2 quesitons for you ...

1. I added the C:\Program Files\WindowsPowerShell\Modules\SimplySQL to AV' product's exclusion list, but it still blocked the module (got same error) Do you have any ideas as to what other folders I may want\need to exclude?

2. Why would it work in ISE, but not at the prompts??

[mithrandyr]

@MartynKeigher -- as for 1) and 2) Did you verify that the files themselves are not "Blocked" (unblock-file)? -- because there are binaries (dll files) packaged with the module, if the binary gets blocked, then it won't not get loaded. I can see how both powershell and cmd might react differently to that blocking than the ISE. (this is probably a long shot)

Also, you would need to add powershell/cmd to the AV exclusion list.. not necessarily the file (because the exclusion is based upon the executable, typically). -- Easy check, is the ISE on the AV exclusion list but not powershell/cmd?

[joalcorn]

FWIW, I was hitting the same issues here and I went looking for an answer. I think I found the issue, I submitted a pull request with a change that worked for my particular case.