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.

See if this works for you (using PRINT) I don't have a way to test right now so sorry for tossing unverified suggestions. https://stackoverflow.com/questions/8387976/line-breaks-lost-in-sql-server this basically suggestions that the line breaks are stored but the method of reading the data is not displaying them. — Ty Savercool, Jul 21 '17 at 16:47
As an aside: `$formatted_output = $output | Out-String` is enough to create a single, multi-line string - the `-replace "(.\`r\`n)", "\`$1\`r\`n"` is a no-op. You're not showing us the part where the line breaks are lost, but @TySavercool's link looks promising. — mklement0, Jul 21 '17 at 16:52
added code block used to load the variable data to sql server. its pretty straight forward approach i'm using. — Siva Dasari, Jul 21 '17 at 17:47

score 1 · Accepted Answer · answered Jul 21 '17 at 18:55

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.

answered Jul 21 '17 at 18:55

Dan Guzman

43,250
3
46
71

you are right. This is happening only in SQL Server 2008 SSMS but not in 2012 / 2014 SSMS. I can't find "Retain CR/LF on copy or save" option in 2008 / 2012 / 2014. looks like they have introduced this setting with 2016 version. is there a way i can set this in 2008 SSMS ? – Siva Dasari Jul 21 '17 at 19:22
@sqlcheckpoint, I think MS changed SSMS behavior a couple of times before the option was made configurable but I don't recall the exact versions. The latest SSMS version as of this writing is 17.1 and a free download from https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms. It supports SQL 2008 and later. – Dan Guzman Jul 21 '17 at 19:33

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

1 Answers1