storing powershell invoke-command output to sql server database with carriage returns

Question

I'm trying to execute batch commands on remote machine using powershell invoke-command commandlet & able to get the output to a variable. But when I'm trying the save it to the sql server database it is storing the content as a paragraph without carriage returns.

could someone suggest me how to save this data to the database with carriage returns, so it will be in a readable format?

sample code:

$output = invoke-command -session $session -ScriptBlock {
    echo '<------starting batch------->'
    cd 'C:\temp\'    
    cmd /c 'dir'
    cmd /c 'hostname'
    echo '<------ending batch------>'
}    
$formatted_output = ($output | Out-String) -replace "(.`r`n)", "`$1`r`n"
write-host $formatted_output

sample variable output:

<------starting batch------->    
 Volume in drive C has no label.    
 Volume Serial Number is 0CE1-E926    

 Directory of C:\temp    

06/26/2017  03:17 PM    <DIR>          .    
06/26/2017  03:17 PM    <DIR>          ..    
08/10/2016  09:07 PM    <DIR>          Clt-Inst    
06/07/2016  04:31 PM        56,406,016 splunkforwarder-6.4.1-debde650d26e-x64-release.msi    
06/26/2017  02:44 PM        35,922,892 Windows6.0-KB968930-x64.msu    
               2 File(s)     92,328,908 bytes    
               3 Dir(s)  418,918,834,176 bytes free    
<servername>    
<------ending batch------>

Note: The output printed in the console does have CLRF characters but when i same this variable value to the database it is saving as pasragraph. which is hard to read.

Answer 1

But when I'm trying the save it to the sql server database it is storing the content as a paragraph without carriage returns.

It is likely data are saved as expected but you are using SSMS to view the data. Make sure the "Retain CR/LF on copy or save" option is selected under Query-->Options-->Results--Grid.