dbatools
dbatools copied to clipboard
dataplat
Missing SqlServer Module Commands - VS Code
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
Read-SqlXEvent: C:\Users\xxxx\Documents\PowerShell\Modules\dbatools\1.1.122\allcommands.ps1:95885:21
Line |
95885 | $enum = Read-SqlXEvent -FileName $currentfile
| ~~~~~~~~~~~~~~
| The term 'Read-SqlXEvent' is not recognized as a name of a cmdlet, function, script file, or
| executable program. Check the spelling of the name, or if a path was included, verify that the path
| is correct and try again.
Steps to Reproduce
Using the PowerShell v2022.7.2 module in VS Code, run the following.
Read-DbaXEFile -Path 'E:\xxxx\Archive\xxxx_*.xel'
Please confirm that you are running the most recent version of dbatools
1.1.122
Other details or mentions
I think it was working for a while. The error above is with PowerShell v2022.7.2. It appears that SqlServer module can get loaded before I execute any commands. It does not have all the expected commands. (I think this did not happen a few times.)
import-module sqlserver
Import-Module: Assembly with same name is already loaded
InvalidOperation: The variable '$binaryModule' cannot be retrieved because it has not been set.
Import-Module: Assembly with same name is already loaded
InvalidOperation: The variable '$binaryModule' cannot be retrieved because it has not been set.
Add-Type: Assembly with same name is already loaded
Add-Type: Assembly with same name is already loaded
Add-Type: Assembly with same name is already loaded
I can get it to work, but it's strange. I went back to PowerShell v2022.5.1. Then I have to import either the SqlServer or DBATools module first. If I don't load either, the automatic load results in the error of the missing command. Update: This stopped working as well.
Import-Module SqlServer
Read-DbaXEFile -Path 'E:\xxxx\Archive\xxxx_0_133043632858370000.xel' | Select -ExpandProperty Name -First 1
Get-Command -Module SqlServer Read-SqlXEvent | Select -ExpandProperty Name
WARNING: SQLPS or SqlServer was previously imported during this session. If you encounter weird issues with dbatools, please
restart PowerShell, then import dbatools without loading SQLPS or SqlServer first.
WARNING: To disable this message, type: Set-DbatoolsConfig -Name Import.SqlpsCheck -Value $false -PassThru | Register-DbatoolsConfig
rpc_starting
Read-SqlXEvent
Anyway, I found going back to PowerShell module v2022.5.1 and re-installing VS Code allowed me to get it working. If I install the latest PowerShell, it will stop working again. I don't know what to think. Stuff like this makes me think either VS Code or PowerShell is too unstable.
What PowerShell host was used when producing this error
VS Code (integrated terminal)
PowerShell Host Version
Name Value
PSVersion 7.2.5 PSEdition Core GitCommitId 7.2.5 OS Microsoft Windows 10.0.19043 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0
SQL Server Edition and Build number
NA
.NET Framework Version
.NET 6.0.6
Thank you. Can you please test this in powershell.exe? If it works, please submit a bug report to the VS Code extension and let me know here.
If I don't load either, the automatic load results in the error of the missing command.
It's actually not an automatic load -- we load it explicitly when we load SqlServer.XEvent.dll in the psm1.
Hi Chrissy. I tested it in PowerShell and it works there. I checked the SqlServer and DBATools module versions available.
Get-Module -ListAvailable SqlServer, DBATools
The results were different. In VS Code, I could additional installs in the user path. I removed those. The uninstall-module would not work on one, even as admin. I renamed it's module folder. Now I see the same results as below in both Windows PowerShell and PowerShell.
Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version PreRelease Name PSEdition ExportedCommands
---------- ------- ---------- ---- --------- ----------------
Script 1.1.125 dbatools Desk {Select-DbaObject, Set-DbatoolsConfig, Get-DbaDbServic…
Script 21.1.18256 SqlServer Desk {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlA…
I ran the following after freshly opening PowerShell and VS Code.
Import-Module DBATools
Get-Command -Module SqlServer
I see the cmdlets in Windows PowerShell.
CommandType Name Version Source
----------- ---- ------- ------
Alias Decode-SqlName 21.1.18256 SqlServer
Alias Encode-SqlName 21.1.18256 SqlServer
Function Invoke-SqlNotebook 21.1.18256 SqlServer
Function SQLSERVER: 21.1.18256 SqlServer
Cmdlet Get-SqlAssessmentItem 21.1.18256 SqlServer
Cmdlet Invoke-SqlAssessment 21.1.18256 SqlServer
Uncharacteristically optimistic, I ran the test again. It still failed in VS Code. Curious, I took a look at the assemblies. They are the same.
[System.AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Like 'Microsoft.SqlServer.*' |
ForEach-Object {
$splitFullname = $_.FullName.Split(',')
[PSCustomObject]@{
FullName = $_.Fullname
Name = $splitFullname[0].trim()
Version = $splitFullname[1].trim()
Culture = $splitFullname[2].trim()
PublicKeyToken = $splitFullname[3].trim()
Location = $_.Location.Replace('C:\Program Files\WindowsPowerShell\Modules\','').trim()
}
} |
Select-Object Name, Version, Location |
Sort-Object Name, Version |
Format-Table * -AutoSize
I have noticed that it would work once in a while. I'm still making attempts to use it. I'm thinking it might have something to do with comments. Does VS Code preload assemblies based on what's in the file? For example, if I make a comment not to load a module first, will it load it firs because it's mentioned first? Just a guess. No idea what's really going one. I will open a ticket with Microsoft once I can get a reliable path to failure.
Unfortunately, I know zero about VS Code, other than it does handle things differently and isn't as perfect of an implementation of PowerShell as the console or even the ISE. We've encountered DLL issues in the past and had to revert some changes, but that was totally on us (I tried to do some weird splicing). This seems totally on whatever changes have been made recently in VS Code.
I just submitted a PR to fully qualify that command so that it knows exactly which command from which module we want to use. You can try that branch or just do the replace in VS Code like I did, close VS Code and load it again. Either way, if all tests pass, I'll be merging this PR as I think it's a good idea.
The Extensions Terminal (no longer called Integrated Terminal) does not automatically load any module without being told to do so. There are two things that will trigger a module import by the extension:
-
powershell.enableProfileLoadingset to true (is by default) - Editor intellisense that triggered by specific command being in a PS1 file that is open
Open VS Code in a new window, open the extension's terminal (or show command PowerShell: Show Extension Terminal) and then run: Get-Module. The modules listed will show what gets loaded on initial startup. After that, any action or editor file you have open can trigger the extension to import a module into your session as it is used for intellisense.
Okay, I see neither DBATools or SqlServer loaded on the new terminal.
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item,…
Manifest 7.0.0.0 Microsoft.PowerShell.Security {ConvertFrom-SecureString, ConvertTo-Sec…
Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, C…
Script 0.2.0 PowerShellEditorServices.Commands {Clear-Host, ConvertFrom-ScriptExtent, C…
Binary 0.2.0 PowerShellEditorServices.VSCode {Close-VSCodeHtmlContentView, New-VSCode…
Script 2.2.6 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLin…
After running the script to and getting the failure.
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, A…
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item,…
Manifest 7.0.0.0 Microsoft.PowerShell.Security {ConvertFrom-SecureString, ConvertTo-Sec…
Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, C…
Script 0.2.0 PowerShellEditorServices.Commands {Clear-Host, ConvertFrom-ScriptExtent, C…
Binary 0.2.0 PowerShellEditorServices.VSCode {Close-VSCodeHtmlContentView, New-VSCode…
Script 2.2.6 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLin…
Script 21.1.18256 SqlServer {Get-SqlAssessmentItem, Invoke-SqlAssess…
@potatoqualitee I updated my allcommands.ps1 with the SqlServer.XEvent\Read-SqlXEvent change. It still failed. However, once I imported the dll, it started to work. As a test, I ran the following in all environments. My allcommands is still updated to reference "SqlServer.XEvent\Read-SqlXEvent". I suspect there are other cmdlets needing this sort of fix to avoid loading the SQL Server module.
cls
Get-Module
$file = New-Item -Path C:\temp -Name 'empty.xel' -ItemType File -Force
try { Read-DbaXEFile -Path $file -EnableException } catch { $Error[0].Exception.InnerException.Message }
Get-command Read-SqlXEvent -All | Out-String
Get-Module | Out-String
Import-Module "C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\SqlServer.XEvent.dll"
Get-command Read-SqlXEvent -All | Out-String
Get-Module | Out-String | Out-String
This is the output in PowerShell ISE. The DBATools cmdlet worked, but it loaded the SqlServer module and used it's version of Read-SqlXEvent. Should DBATools ever cause the load of an "external" SqlServer module?
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 1.0.0.0 ISE {Get-IseSnippet, Import-IseSnippet, New-IseSnippet}
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Attempted to read past the end of the stream.
CommandType Name Version Source
----------- ---- ------- ------
Cmdlet Read-SqlXEvent 21.1.18256 SqlServer
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, Add-DbaAgListener...}
Script 1.0.0.0 ISE {Get-IseSnippet, Import-IseSnippet, New-IseSnippet}
Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Script 21.1.18256 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlAzureAuthenti...
CommandType Name Version Source
----------- ---- ------- ------
Cmdlet Read-SqlXEvent 1.0.0.0 SqlServer.XEvent
Cmdlet Read-SqlXEvent 21.1.18256 SqlServer
PS C:\Users\xxxx> Get-Module
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, Add-DbaAgListener...}
Script 1.0.0.0 ISE {Get-IseSnippet, Import-IseSnippet, New-IseSnippet}
Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Script 21.1.18256 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlAzureAuthenti...
Binary 1.0.0.0 SqlServer.XEvent Read-SqlXEvent
This is the PowerShell output. It also worked. The SqlServer module was loaded, but it's cmdlets are not available. I suspect the SqlServer module was used. Perhaps SqlServer.XEvent\Read-SqlXEvent is not specific to a dll? I can import the DBA Tools version of the dll and see the command. After I do so, I see a new module listed.
PS C:\Users\xxxx> Get-Module
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item, Clear-ItemProperty…}
Script 2.1.0 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Remove-PSReadLineKeyHandler, Set-PSReadLineKeyHand…
PS C:\Users\xxxx> $file = New-Item -Path C:\temp -Name 'empty.xel' -ItemType File -Force
PS C:\Users\xxxx> try { Read-DbaXEFile -Path $file -EnableException } catch { $Error[0].Exception.InnerException.Message }
Attempted to read past the end of the stream.
PS C:\Users\xxxx> Get-command Read-SqlXEvent -All | Out-String
Get-Command: The term 'Read-SqlXEvent' is not recognized as a name of a cmdlet, function, script file, or executable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
PS C:\Users\xxxx> Get-Module | Out-String
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, Add-DbaAgListener…}
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item, Clear-ItemProperty…}
Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object…}
Script 2.1.0 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Remove-PSReadLineKeyHandler, Set-PSReadLineKeyHandl…
Script 21.1.18256 SqlServer {Get-SqlAssessmentItem, Invoke-SqlAssessment, Invoke-SqlNotebook, SQLSERVER:…}
PS C:\Users\xxxx> Import-Module "C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\SqlServer.XEvent.dll"
PS C:\Users\xxxx> Get-command Read-SqlXEvent -All | Out-String
CommandType Name Version Source
----------- ---- ------- ------
Cmdlet Read-SqlXEvent 1.0.0.0 SqlServer.XEvent
PS C:\Users\xxxx> get-module
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, Add-DbaAgListener…}
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item, Clear-ItemProperty…}
Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object…}
Script 2.1.0 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Remove-PSReadLineKeyHandler, Set-PSReadLineKeyHand…
Script 21.1.18256 SqlServer {Get-SqlAssessmentItem, Invoke-SqlAssessment, Invoke-SqlNotebook, SQLSERVER:…}
Binary 1.0.0.0 SqlServer.XEvent Read-SqlXEvent
This is the output from VS Code. It fails.
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item, Clear-Ite…
Manifest 7.0.0.0 Microsoft.PowerShell.Security {ConvertFrom-SecureString, ConvertTo-SecureString,…
Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Obj…
Script 0.2.0 PowerShellEditorServices.Commands {Clear-Host, ConvertFrom-ScriptExtent, ConvertTo-S…
Binary 0.2.0 PowerShellEditorServices.VSCode {Close-VSCodeHtmlContentView, New-VSCodeHtmlConten…
Script 2.2.6 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLineOption, R…
Get-Command: C:\Users\xxxx\Documents\GitHub\dba-extended-event-analysis\test_read.ps1:5:1
Line |
5 | Get-command Read-SqlXEvent -All | Out-String
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The term 'Read-SqlXEvent' is not recognized as a name of a cmdlet, function, script file, or executable
| program. Check the spelling of the name, or if a path was included, verify that the path is correct and
| try again.
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDat…Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item, Clear-Item…Manifest 7.0.0.0 Microsoft.PowerShell.Security {ConvertFrom-SecureString, ConvertTo-SecureString, …Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Obje…Script 0.2.0 PowerShellEditorServices.Commands {Clear-Host, ConvertFrom-ScriptExtent, ConvertTo-Sc…Binary 0.2.0 PowerShellEditorServices.VSCode {Close-VSCodeHtmlContentView, New-VSCodeHtmlContent…Script 2.2.6 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Re…Script 21.1.18256 SqlServer {Get-SqlAssessmentItem, Invoke-SqlAssessment, Invok…
CommandType Name Version Source
----------- ---- ------- ------
Cmdlet Read-SqlXEvent 1.0.0.0 SqlServer.XEvent
PS C:\Users\xxxx\Documents\GitHub\dba-extended-event-analysis> Get-Module
ModuleType Version PreRelease Name ExportedCommands
---------- ------- ---------- ---- ----------------
Script 1.1.125 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDa…
Manifest 7.0.0.0 Microsoft.PowerShell.Management {Add-Content, Clear-Content, Clear-Item, Clear-Ite…
Manifest 7.0.0.0 Microsoft.PowerShell.Security {ConvertFrom-SecureString, ConvertTo-SecureString,…
Manifest 7.0.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Obj…
Script 0.2.0 PowerShellEditorServices.Commands {Clear-Host, ConvertFrom-ScriptExtent, ConvertTo-S…
Binary 0.2.0 PowerShellEditorServices.VSCode {Close-VSCodeHtmlContentView, New-VSCodeHtmlConten…
Script 2.2.6 PSReadLine {Get-PSReadLineKeyHandler, Get-PSReadLineOption, R…
Script 21.1.18256 SqlServer {Get-SqlAssessmentItem, Invoke-SqlAssessment, Invo…
Binary 1.0.0.0 SqlServer.XEvent Read-SqlXEvent
If I run the Read-DbaXEFile command in VS Code after the explicit dll import, it works.
PS C:\Users\xxxx\Documents\GitHub\dba-extended-event-analysis> try { Read-DbaXEFile -Path $file -EnableException
} catch { $Error[0].Exception.InnerException.Message }
Attempted to read past the end of the stream.
@potatoqualitee @wsmelton Hi, modules might not be loaded automatically, but I do see assemblies loaded. I think the culprit is intellisense. Perhaps it would be better to automatically load modules instead of assemblies?
I created a new folder and added a single file with the code below. When I run this, I see no output for loaded SqlServer module assemblies. No assemblies were loaded for DBATools or SqlServer. The result remains the same if I run it again after waiting a long while. (An odd thing to say, but there's a reason.)
#$file = New-Item -Path C:\temp -Name 'empty.xel' -ItemType File -Force; Read-DbaXEFile -Path $file
[System.AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Like 'Microsoft.SqlServer.*' |
Sort-Object -Property Location |
Select-Object -Property Location
Now if I select all the code excluding the # and F8, I get the good result with the expected error. (The file is empty, so the end of stream error is a good thing.)
PS C:\Users\xxxx\Documents\GitHub\Test> $file = New-Item -Path C:\temp -Name 'empty.xel' -ItemType File -Force; Read-DbaXEFile -Path $file
[System.AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Like 'Microsoft.SqlServer.*' |
Sort-Object -Property Location |
Select-Object -Property Location
Read-SqlXEvent: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\allcommands.ps1:95899:21
Line |
95899 | … $enum = SqlServer.XEvent\Read-SqlXEvent -FileName $currentfile
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| One or more errors occurred. (**Attempted to read past the end of the stream.**)
Location
--------
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Dmf.Common.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.Dmf.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.PSProvider.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.PSSnapins.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.RegisteredServers.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEvent.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventDbScoped.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventDbScopedEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.ServiceBrokerEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Smo.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.SmoExtended.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.SqlEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XE.Core.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XEvent.Linq.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XEvent.XELite.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Dac.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Dac.Extensions.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.TransactSql.ScriptDom.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Types.dll
C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\coreclr\Microsoft.SqlServer.Assessment.Cmdlets.dll
C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\coreclr\Microsoft.SqlServer.Assessment.dll
C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\coreclr\Microsoft.SqlServer.Dmf.dll
Now I remove the # and save the file and close VS Code. After reopening VS Code, I wait a bit in case intellisence needs time to do its thing. (If I am too fast, the result be different and be the same as above. Intellisence has not loaded assemblies yet. This explains why my code would sometimes work. I suppose there could be many results, depending on how far along intellisence got loading assemblies.)
$file = New-Item -Path C:\temp -Name 'empty.xel' -ItemType File -Force; Read-DbaXEFile -Path $file
[System.AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Like 'Microsoft.SqlServer.*' |
Sort-Object -Property Location |
Select-Object -Property Location
The error (after waiting a bit to run) is now unexpected and indicates the cmdlet is not working. There is also a different number of assemblies loaded.
PS C:\Users\xxxx\Documents\GitHub\Test> . "c:\Users\xxxx\Documents\GitHub\Test\test.ps1"
SqlServer.XEvent\Read-SqlXEvent: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\allcommands.ps1:95899:21
Line |
95899 | $enum = SqlServer.XEvent\Read-SqlXEvent -FileName $curren …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| **The module 'SqlServer.XEvent' could not be loaded.** For more information, run 'Import-Module SqlServer.XEvent'.
Location
--------
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Dmf.Common.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.Dmf.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.PSProvider.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.PSSnapins.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.RegisteredServers.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEvent.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventDbScoped.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventDbScopedEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.ServiceBrokerEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Smo.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.SmoExtended.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.SqlEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XE.Core.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XEvent.Linq.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XEvent.XELite.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Dac.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Dac.Extensions.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.TransactSql.ScriptDom.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Types.dll
The suggestion for more information does not help. I don't think I'm meant to load DBATools dlls directly.
PS C:\Users\xxxx\Documents\GitHub\Test> Import-Module SqlServer.XEvent
Import-Module: The specified module 'SqlServer.XEvent' was not loaded because no valid module file was found in any module directory.
To be more obvious about it, I reopen VS Code with the uncommented version of the file. I select just the part that lists the assemblies and use F8 to run it. If I'm fast enough, there is no result. Then a second or third F8 shows a result.
PowerShell Extension v2022.7.2
Copyright (c) Microsoft Corporation.
https://aka.ms/vscode-powershell
Type 'help' to get help.
PS C:\Users\xxxx\Documents\GitHub\Test>
PS C:\Users\xxxx\Documents\GitHub\Test> [System.AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Like 'Microsoft.SqlServer.*' |
Sort-Object -Property Location |
Select-Object -Property Location
PS C:\Users\xxxx\Documents\GitHub\Test>
PS C:\Users\xxxx\Documents\GitHub\Test> [System.AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Like 'Microsoft.SqlServer.*' |
Sort-Object -Property Location |
Select-Object -Property Location
Location
--------
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Dmf.Common.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.Dmf.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.PSProvider.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.PSSnapins.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.RegisteredServers.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEvent.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventDbScoped.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventDbScopedEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Management.XEventEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.ServiceBrokerEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.Smo.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.SmoExtended.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.SqlEnum.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XE.Core.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XEvent.Linq.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\Microsoft.SqlServer.XEvent.XELite.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Dac.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Dac.Extensions.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.TransactSql.ScriptDom.dll
C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.125\bin\smo\coreclr\win\Microsoft.SqlServer.Types.dll
PS C:\Users\xxxx\Documents\GitHub\Test>
The SqlServer module has logic to select a different set of assemblies depending upon the environment. I think in the VS Code environment, the SqlServer module will select some dlls from a "coreclr" subfolder. I don't know if DBATools will do the same. In any case, assemblies loaded appear to depend upon intellisense, not the code in the modules.
So, is this a module issue with intellisence or an intellisence issue with modules?
I put "import-dbatools" into my $profile. It seems to work. I think it's still a race between PowerShell and Intellisence. I suppose I could close all my files before closing VS Code to prevent giving intellisence anything to do while my profile script loads the module.
Any ideas on what should be done about this?
No idea :( I think the PowerShell team will know how to fix it, considering all of your research and feedback.
@potatoqualitee I'm a DBA, not a .net developer, so I do not understand fully the ramifications of private vs shared assemblies. It does seem that the concept of private does not apply to modules because all modules load into the same runspace. (What stays in Vegas...is shared with everybody in Vegas.) I can understand including a specific dll dependencies in the module to avoid dealing with the constant integration of dependency change, but these choices seem to affect the runspace. Wouldn't it be possible to separately install a "supported" version of SqlServer module instead?
I can't help but think of the old days and DLL Hell. Abandon all hope ye who mix dlls.
https://www.codemag.com/article/0207071/End-DLL-Hell-with-.NET-Version-Control-and-Code-Sharing
A fun read about loading assemblies.
https://docs.microsoft.com/en-us/dotnet/framework/deployment/best-practices-for-assembly-loading
It appears that in theory it is possible to load different versions of the same assembly in a single runspace using Assembly Load Contexts. Perhaps a feature request?
https://docs.microsoft.com/en-us/powershell/scripting/dev-cross-plat/resolving-dependency-conflicts?view=powershell-7.2
An interesting bit from the above link.
"Currently, the best example of using an ALC for dependency isolation in PowerShell is in PowerShell Editor Services, the language server for the PowerShell extension for Visual Studio Code. An ALC is used to prevent PowerShell Editor Services' own dependencies from clashing with those in PowerShell modules."
It does not appear intellisence uses an ALC. If intellisence did use an ALC, would it provide incorrect information from different assemblies?
I do not understand fully the ramifications of private vs shared assemblies
In PowerShell this does not exists. An assembly or library is loaded