This line of code has - without editing it - changed it's behavior over the last several days - several times:
try {
$testfile = "c:\users\***RedactedForSecurity***\desktop\psfcatalog\copy_conversions_weekly_report.csv"
write-log((Get-Date -Format o) + " get-sqlhelper")
$sqlhelper = get-sqlhelper -serverinstance '***RedactedForSecurity***' -database '***RedactedForSecurity***' -username '***RedactedForSecurity***' -password '***RedactedForSecurity***'
Function Create-TableString{
Param(
[Parameter(Mandatory=$true)]
[string]$tableName,
[Parameter(Mandatory=$true)]
[string]$Fields
)
[string]$SQL = "CREATE TABLE " + $tableName + '(' + $Fields + ')'
Write-Host $SQL
return $SQL
}
write-log((Get-Date -Format o) + " finding first line of CSV data in file")
gc $testfile | % {
if ($_ -match "report fields") {
$line = $_.ReadCount
break
}
}
[string]$fieldNamesSQL = gc $testfile |
select -Skip $line -First 10 |
ConvertFrom-Csv |
Out-DataTable |
% {$fieldNamesSQL += $Col.ColumnName + " VARCAR(255) NULL, "} -End {$fieldNamesSQL.TrimEnd(',', ' ')}
Write-Host $fieldNamesSQL
Write-Host $csvHeaders
} catch {
$msg = command 2>&1
Write-Host $_.exception.message
Write-Host $_.exception.itemname
Write-Host $_.exception.message
Write-Host $msg
}
Alright here's the thing - notice the portion of the script:
gc $testfile | % {
if ($_ -match "report fields") {
$line = $_.ReadCount
break
}
}
That portion worked fine for about a day and then break
started to stop execution of the entire script instead of just beak the loop. How can it work for a day and then change? Anyway, I came up with a solution where I would wrap the whole piece with {}
and then recursivly execute it in my next call:
{gc $testfile | % {
if ($_ -match "report fields") {
$line = $_.ReadCount
break
}
}}
[string]$fieldNamesSQL = gc $testfile |
select -Skip $line -First 10 |
ConvertFrom-Csv |
Out-DataTable |
% {$fieldNamesSQL += $Col.ColumnName + " VARCAR(255) NULL, "} -End {$fieldNamesSQL.TrimEnd(',', ' ')}
With this setup when $line
is called, it evaluates the previous line and script execution continues seamlessly.
A day later...
Now the same script is throwing an error:
Cannot validate argument on parameter 'Skip'. The argument is null, empty, or an element of the argument collection contains a null value. Supply a collection that does not contain any null values and then try the command again.
What is happening?
Edit
This is not a duplicate. I'm aware of the foreach
alias problem. The question is why the PowerShell runtime changes its evaluation between executions. Ie: I didn't change the script but the execution changed. Why would PowerShell evaluate foreach
to an operator on one day and evaluate it to ForEach-Object
on another day?
Things I've found:
It seems that PowerShell persists certain values between executions. For instance if I set a string to value1 and run the script. Then I set it to another value2 and run the script, but the value remains value1 when I Write-Host
. I have to restart the shell and sometimes the machine to get it to evaluate correctly. It seems random so far as to when this happens - which probably just means I don't understand it.
My best guess so far is that when PowerShell interprets foreach it does so recursively so a function further down the script could cause it to evaluate differently from call to call.