Invoke-Sqlcmd -Query "SELECT LongText FROM MyTable" -ServerInstance $sql | out-file -filepath $filepath
Produced a file containing:
This was my really really long and boring piece of text that I had writ...
The thing is, it happened quite randomly. Or so I thought. Turns out that the out-file cmdlet outputs exactly as if it was appearing within the console on that machine and my console was too narrow for the text. (This is all in the documentation to be fair). So manipulating the size of your console window (by editing the properties) results in different output lengths.
However, each powrshell install on a machine can be different so I really need my script to be able to handle this limitation. As of writing, I couldn't find anything fullproof and my best hack was to just explicity set a width that I "knew" was big enough to cater for my text.
Invoke-Sqlcmd -Query "SELECT LongText FROM MyTable" -ServerInstance $sql | out-file -filepath $filepath -width 2000