PowerShell 7.4.6 along with dbatools 2.1.28 & dbatools.library 2024.4.12 - Microsoft.DataSqlClient.dll - Assembly with same name is already loaded

[RareCrawdad]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Get-DbaAgentJob -SqlInstance $DestinationServer | Where-Object { …

The 'Get-DbaAgentJob' command was found in the module 'dbatools', but the module could not be loaded due to the
following error: [Couldn't import C:\Program
Files\PowerShell\7\Modules\dbatools.library\2024.4.12\core\lib\win-sqlclient\Microsoft.Data.SqlClient.dll |
Assembly with same name is already loaded] For more information, run 'Import-Module dbatools'.
     
     
     

### Steps to Reproduce

Running this using PowerShell 7.4.6, dbatools 2.1.28 & dbatools.library 2024.4.12. Calling a .ps1 file on a local drive.

# Define the source and destination SQL instances
$SourceServer= 'Server1,1433'
$DestinationServer = 'Server2,1433'

# Define the jobs to exclude
$excludeJobs = @()

# Delete all jobs on the destination server except the excluded ones
Get-DbaAgentJob -SqlInstance $DestinationServer | Where-Object { $_.Name -notin $excludeJobs } | Remove-DbaAgentJob -Confirm:$false

Start-Sleep -Seconds 5

# Copy jobs from the source server to the destination server, excluding the specified jobs
Copy-DbaAgentJob -Source $SourceServer -Destination $DestinationServer -ExcludeJob $excludeJobs -Force


### Please confirm that you are running the most recent version of dbatools

Script     2.1.28                dbatools                    
Script     2024.4.12             dbatools.library

### Other details or mentions

Windows Environmental Variables order:

PS C:\Users\xxxx> $env:PSModulePath -split ';'

C:\Program Files\PowerShell\7\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules
C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\
C:\Program Files\PowerShell\Modules
C:\Users\xxxx\Documents\PowerShell\Modules

Troubleshooting Steps:

1. I have removed VSCode, PowerShell 7 & All cmdlets
2. Reinstalled only PowerShell 7 after reboot
3. Imported downloaded Modules for dbatools and dbatools.library
4. The .ps1 script ran
5. Rebooted Server
6. Received the Assembly with same name already loaded error. I have run out of options to test at this point.

### What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

### PowerShell Host Version


Name                           Value
----                           -----
PSVersion                      7.4.6
PSEdition                      Core
GitCommitId                    7.4.6
OS                             Microsoft Windows 10.0.20348
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

Microsoft SQL Server 2022 (RTM-CU16) (KB5048033) - 16.0.4165.4 (X64)   Nov  6 2024 19:24:49   Copyright (C) 2022 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor) 

### .NET Framework Version

[System.Runtime.InteropServices.RuntimeInformation]::get_FrameworkDescription() | Set-Clipboard

.NET 8.0.10

-- if that doesn't work, you can try Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -Recurse | Get-ItemProperty -Name version -EA 0 | Where PSChildName -Match '^(?!S)\p{L}' | Select PSChildName, version

PSChildName                      Version
-----------                      -------
v2.0.50727                       2.0.50727.4927
v3.0                             3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation  3.0.6920.4902
v3.5                             3.5.30729.4926
Client                           4.8.04161
Full                             4.8.04161
Client                           4.0.0.0

[RareCrawdad]

I just tried this method from

https://learn.microsoft.com/en-us/powershell/scripting/dev-cross-plat/resolving-dependency-conflicts?view=powershell-7.4#why-do-dependency-conflicts-occur

, but getting same error:

WrapperScript.ps1

Path to the main script

$scriptPath = "C:\path\to\CopyJobs.ps1"

Use pwsh -c to run the main script in a new session

pwsh -c "& '$scriptPath'"

[RareCrawdad]

I have also tried the following method:

https://stackoverflow.com/questions/77545580/upgrade-to-powershell-7-dbatools-2-causing-assembly-with-same-name-is-already

Same error message.

[RareCrawdad]

I was able to get this to run several times, but still testing:

Unload the conflicting assembly if it's already loaded

$assembly = [System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object { $_.GetName().Name -eq "Microsoft.Data.SqlClient" }
if ($assembly) {
[System.AppDomain]::CurrentDomain.RemoveAssembly($assembly)
}

Load the required assembly

$dllPath = "C:\path\to\Microsoft.Data.SqlClient.dll"
Add-Type -Path $dllPath

I still have to run a clean PS session via the WrapperScript.ps1, but by adding the following on the main script, it seems to work thus far.

Still does not explain why the sudden change after updating Powershell and dbatools.