Invoke-SqlCmd broken if Set-Location used in Tasks (2.11.x)

[phdavis]

Describe the Bug

If Set-Location is used within a task and then not set back to the original location before returning future usage of Invoke-SqlCmd in other tasks generates exceptions and causes them to end prematurely.

Steps To Reproduce

1. Create 3 tasks (see below)
2. In Task1 have some simple logging such as TASKSTART and TASKFINISH. In the middle, execute a Set-Location.
3. In Task2 once again have some simple logging, but also make a couple Invoke-SqlCmd calls.
4. In Task3, simply mirror Task2.
5. Add tasks to server startup and launch one by one i.e. Invoke-PodeTask -Name 'test_task1' -Wait, Invoke-PodeTask -Name 'test_task2' -Wait etc
6. Start the server and monitor logging

Task1 should run all the way to completion, even with Set-Location being used and even Invoke-SqlCmd usage if added.
Task2 should launch fine and run everything up to the first instance of Invoke-SqlCmd. Oddly the first use will actually work (i.e. SQL Insert actually inserts) but throws an exception. Anything else afterwards is not ran.
Task3 should have the same behavior as Task2.

Add-PodeTask -Name 'test_task1' -ScriptBlock {
    # This task should execute entirely regardless of contents
    "TASK1 START" | Write-PodeErrorLog -Level Debug
    "TASK1 <LOGIC>" | Write-PodeErrorLog -Level Debug
    #$OriginalLocation = Get-Location # Optional testing
    Set-Location "$(Drive):\"
    "TASK1 <LOGIC>" | Write-PodeErrorLog -Level Debug
    # Optional
    #$SQLQuery = "INSERT INTO logs (logmessage) VALUES ('$LogMessage')"
    #$InvokeSQL = Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SQLDatabase" -Query "$SQLQuery"
    #Set-Location $OriginalLocation # Optional testing
    "TASK1 FINISH" | Write-PodeErrorLog -Level Debug
    return $true
}

Add-PodeTask -Name 'test_task2' -ScriptBlock {
    "TASK2 START" | Write-PodeErrorLog -Level Debug
    "TASK2 <LOGIC>" | Write-PodeErrorLog -Level Debug
    $SQLQuery = "INSERT INTO logs (logmessage) VALUES ('TASK2-1')"
    $InvokeSQL = Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SQLDatabase" -Query "$SQLQuery"
    #################################
    # << CRASH >>
    #################################
    "TASK2 <LOGIC>" | Write-PodeErrorLog -Level Debug
    $SQLQuery = "INSERT INTO logs (logmessage) VALUES ('TASK2-2')"
    $InvokeSQL = Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SQLDatabase" -Query "$SQLQuery"
    "TASK2 <LOGIC>" | Write-PodeErrorLog -Level Debug
    $SQLQuery = "INSERT INTO logs (logmessage) VALUES ('TASK2-3')"
    $InvokeSQL = Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SQLDatabase" -Query "$SQLQuery"
    "TASK2 FINISH" | Write-PodeErrorLog -Level Debug
    return $true
}

Add-PodeTask -Name 'test_task3' -ScriptBlock {
    "TASK3 START" | Write-PodeErrorLog -Level Debug
    "TASK3 <LOGIC>" | Write-PodeErrorLog -Level Debug
    $SQLQuery = "INSERT INTO logs (logmessage) VALUES ('TASK3-1')"
    $InvokeSQL = Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SQLDatabase" -Query "$SQLQuery"
    #################################
    # << CRASH >>
    #################################
    "TASK3 <LOGIC>" | Write-PodeErrorLog -Level Debug
    $SQLQuery = "INSERT INTO logs (logmessage) VALUES ('TASK3-2')"
    $InvokeSQL = Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SQLDatabase" -Query "$SQLQuery"
    "TASK3 <LOGIC>" | Write-PodeErrorLog -Level Debug
    "TASK3 FINISH" | Write-PodeErrorLog -Level Debug
    return $true
}

Invoke-PodeTask -Name 'test_task1' -Wait
Invoke-PodeTask -Name 'test_task2' -Wait
Invoke-PodeTask -Name 'test_task3' -Wait

Exception

Date: 2024-11-15 09:50:56
Level: Error
ThreadId: 0
Server: SERVER01P
Category: InvalidResult: (LOCALHOST\SQLEXPRESS:PSObject) [Invoke-Sqlcmd], DriveNotFoundException
Message: Cannot find drive. A drive with the name 'XYZ' does not exist.
StackTrace: at <ScriptBlock>, <No file>: line 149
at <ScriptBlock>, <No file>: line 10
at Invoke-PodeScriptBlock, C:\Program Files\PowerShell\Modules\Pode\2.11.1\Public\Utilities.ps1: line 573
at <ScriptBlock>, <No file>: line 57

TASK1 START
TASK1 <LOGIC>
TASK1 FINISHED
TASK2 START
TASK2 <LOGIC>
.....?
TASK3 START
TASK3 <LOGIC>
.....?

Expected Behavior

Before 2.11.x no exception occurs and everything continues running normally. A workaround appears to be running this in Task1 before it returns. I haven't tested this thoroughly though, and nothing else changed besides the version of Pode though I did try 2.11.0 and also PS7.4.6. Push/Pop-Location may also work.

$OriginalLocation = Get-Location
Set-Location $OriginalLocation

Platform

• OS: Server 2022
• Versions:
  • Pode: 2.11.x (2.11.1 was used during this test)
  • PowerShell: 7.3.12 was used for this test, but it was also happening with 7.4.6
  • SqlServer: 21.1.18256 ... have not had a chance to refactor code and test with newer version/s

[mdaneri]

I was reviewing the documentation here: Manage SQL Server on Linux with PowerShell Core. It seems that Invoke-Sqlcmd might be leveraging New-PSDrive for establishing connections to SQL. This makes me suspect that the issue could be related to how we’re handling runspaces.

[phdavis]

Based on what I've read it does something like SQLSERVER:\ behind the scenes, though I've never actually seen that happen directly. The workaround is easy enough if we end up going that route, but thought it was strange it just now started happening with the new release.

[mdaneri]

Before, what version of Powershell you were using ?

[phdavis]

7.3.12, but it appears to occur regardless of what PS version I test with.

[Badgerati]

In 2.10.x the errors wouldn't be present, as Tasks in 2.11.z we're updated so that any errors thrown from a task were actually logged - otherwise they were just being silently hidden.

Though, the only changes should just be around logging, so I can't immediately think why 2.10.x would work but 2.11.x wouldn't work 🤔

If you set the location back to the original at the end of each Task, does that then resolve the issue? Because might just be that at the start of a task (or even a Schedule/etc.) they just need to ensure the location is reset.

[phdavis]

@Badgerati From my initial testing yes - if I set the location back in the 1st task before it returns then other tasks etc. continue to work as expected. This specific code has been essentially as-is for 3-4 years now 😄