Encountering an issue with Start-Transcript, SQLDataReader and Passing it to a function for processing. This occurs in Powershell 6, started in 5.1, was not present in Windows Powershell 3 (Windows Powershell 4 has issues passing the SqlDataReader object to a function anyway).

When you call a function within a Start-Transcript block, and that function then fills a SqlDataReader object, and then passes it onto another function, it increments the record a number of times, as if calling the read() method 7 or 8 times. This causes a number of issues, firstly, a lot of data is skipped for processing, secondly, you end up trying to read data which isn't available, resulting in an error.

This only occurs when Start-Transcript is called before running (and only while the transcript is actually running), and only when passing the SqlDataReader object to another function.
Steps to reproduce

Run the following script. It will output 4 lots of values. The first one will error, the next 3 will not.

It attempts to run in the following manner -

Pass 1 (Fail) - SqlDataReader passed to a function within a Start-Transcript block
Pass 2 (Success) - SqlDataReader passed to a function
Pass 3 (Success) - SqlDataReader processed inline within a Start-Transcript block
Pass 4 (Success) - SqlDataReader processed inline

Replace the $SQLConnectionString string with a real connection string, any SQL database should do (so long as it's SQL 2012+), no data needed in there. Also point the $transcriptPath to a file which can be saved to.

$actualSql = "select top 25 cast(ROW_NUMBER() over(order by name) as nvarchar(255)) from sys.objects"
$SQLConnectionString = "<SQL Connection String Here>"
$transcriptPath = "<Transcript File Path Here>"
function get-Row{

param(

    [System.Data.SqlClient.SqlDataReader] $TempReader

)


$row = @{}

$row["FirstColumn"] = $TempReader.GetSqlValue(0)

return $row

}

function test-SQL{

param(

    [switch]$NoFunctionCall

)

$sqlConn = New-Object System.Data.SqlClient.SqlConnection

$sqlConn.ConnectionString = $SQLConnectionString

$sqlConn.Open()


$sourcecmd = New-Object System.Data.SqlClient.SqlCommand

$sourcecmd.Connection = $sqlConn

$sourcecmd.CommandText = $actualSql

$sourcecmd.CommandTimeout = 600

[System.Data.SqlClient.SqlDataReader] $reader = $sourcecmd.ExecuteReader();

try {

    if ($reader.HasRows -eq $true) {

        while ($reader.Read()) {

            if($NoFunctionCall){

                write-output $reader.GetString(0)

                $processed = @{}

                $processed["FirstColumn"] = $reader.GetSqlValue(0)

                write-output $reader.GetString(0)

                Write-Output $processed.Value

            }

            else{

                write-output $reader.GetString(0)

                $processed = get-Row $reader

                write-output $reader.GetString(0)

                Write-Output $processed.Value

            }

        }

    }

}

catch{

    Write-Error $_.Exception | format-list -force

}

}

Write-Output "Running With Transcript and Function"
Start-Transcript -Path $transcriptPath
test-SQL
Stop-Transcript

Write-Output "."
Write-Output "."
Write-Output "."
Write-Output "Running Without Transcript With Function"
test-SQL

Write-Output "."
Write-Output "."
Write-Output "."
Write-Output "Running With Transcript Without Function"
Start-Transcript -Path $transcriptPath
test-SQL -NoFunctionCall
Stop-Transcript

Write-Output "."
Write-Output "."
Write-Output "."
Write-Output "Running Without Transcript And Function"
test-SQL -NoFunctionCall

Since May 2019 Security Rollup for Windows Server 2016, access to .net object via powershell is very slow, when a powershell variable breakpoint is set in the powershell.

For example loading speed of [System.Environment] takes over ten times with breakpoint set against no breakpoint set.

Since the behavior only occurs with latest security updates installed (May 2019 (KB4494440) or June 2019 (KB4503267)) i think it is related to the update.

Did some troubleshooting with procmon until now. For me it looks like when PSBreakpoint is set, there are multiple reads from HKLM\System\CurrentControlSet\Control\Session Manager\Environment__PSLockdownPolicy can't repoduce this when there is no breakpoint set and can't reproduce this effect without one of the updates installed.

Following powershell code runs more than 10 times slower when i install Windows Security Rollup update Mai / June 2019 on my Windows Server 2016.

$measure1 = measure-command{
foreach($i in 1..10000){

[System.Environment]

}
} |select -ExpandProperty TotalSeconds

Set-PSBreakpoint -Variable "test" -Action {$null}

$measure2 = measure-command{
foreach($i in 1..10000){

[System.Environment]

}
} |select -ExpandProperty TotalSeconds

Write-Host "First measurement took $measure1 seconds, Second measurement took $measure2"

>>First measurement took 0.0482474 seconds, Second measurement took 0.8722697

Tested on:

PSVersion: 5.1.14393.2969
PSEdition: Desktop
PSCompatibleVersions: {1.0, 2.0, 3.0, 4.0...}
BuildVersion:10.0.14393.2969
CLRVersion:4.0.30319.42000
WSManStackVersion:3.0
PSRemotingProtocolVersion:2.3
SerializationVersion:1.1.0.1

Can anyone observe this behavior as well?

Do you have any suggestions to workaround this problem?

Is it related to some settings or installed software?

Steps to reproduce

In File Explorer on an NTFS volume, create the file: [abc].txt

Following this run:

PS> ls '[abc].txt'

Expected behavior

Directory: C:\Users\Dave

Mode LastWriteTime Length Name

-a---- 2019-05-06 12:07 AM 0 [abc].txt

Actual behavior

No output displayed.

Environment data

Name Value

PSVersion 5.1.17763.316
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.316
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Additional Cases and Information

The file name can be displayed using tab completion, but the brackets are preceded by back ticks, which indicates that Windows can see the file and assumes that the brackets are special characters. When attempting to list the file using the ls command, the following error is generated:

ls : Cannot find path 'C:\Users\Dave`[abc].txt' because it does not exist.
At line:1 char:1
+ ls '.\[abc`].txt'
+ ~~~~~~~~~~~~~~~~~~

+ CategoryInfo          : ObjectNotFound: (C:\Users\Dave\`[abc`].txt:String) [Get-ChildItem], ItemNotFoundExceptio

n

+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Other commands generate no output or errors as well.

PS> Get-FileHash '[abc].txt'

Generates no output

PS> 1 | Out-File '[abc].txt'

Generates error stating that the wildcard path did not resolve to a file