Sql output works in main as expected, but not in the DoWork function. Why?
function Main {
$result = DoWork
$result | Format-Table -AutoSize
}
function DoWork {
#return results without assigning to variable
$result = Invoke-Sqlcmd -Query "select top 1 * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables
#$result | Format-Table -AutoSize
}
Main
To expand on the comments...
Background
Format-Table and Format-List don't actually write to the console themselves - they just convert an object into "meta formatting" objects. You need to pipe these into out-host to render output to the console:
You can, in fact, also just pipe the original value to out-host as well, but you'll get the default formatting without any customisations you might want to specify in Format-Table or Format-List:
The cmdlets that contain the Out verb, Out-, don't format objects. They render objects and send them to the specified display destination. If you send an unformatted object to an Out- cmdlet, the cmdlet sends it to a formatting cmdlet before rendering it.
You can see this if you try the following:
PS> $x = @( "aaa", "bbb" ) | format-table
PS> $x | foreach-object { $_.GetType().FullName }
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
PS> $x | out-host
aaa
bbb
You might also note this:
PS> $x
aaa
bbb
But that's because the top level scope (including the interactive prompt) is special - any uncaptured objects returned to the top level scope will be automatically passed to Out-Host to be formatted and displayed in the host (i.e. the console):
Out-Host is automatically appended to every command that is executed. It passes the output of the pipeline to the host executing the command.
Answer
In your script, $result | Format-Table -AutoSize in your DoWork function is converting $result into formatting objects and sending them into the output pipeline from DoWork and they’re being captured into the $result variable in Main, never to find their way to the console.
By contrast, the output from $result | Format-Table -AutoSize in Main is being returned to the top-level scope and is getting automatically sent to out-host, and then rendered to the console.
If you want DoWork to write output to the console you can do this instead $result | Format-Table -AutoSize | Out-Host or, my personally preferred approach Write-Host ($result | Format-Table -AutoSize | Out-String) as it's easier to scan the code for Write-Host on the left of the line rather than | Out-Host on the right, but that's a stylistic choice...
Format-Tabledoesn’t write to the console by itself - it just formats an object into “meta formatting” objects. You need to pipe it intoout-hostto render it in the console. YourDoWorkis sending the formatting objects into the output pipeline fromDoWorkand they’re being captured into the$resultvariable in ```Main’``Mainis being returned to the top-level scope and is getting automatically sent toout-hostand shown in the console.