locked
loading powershell command output to sql server database in a formatted way. RRS feed

  • Question

  • Hello,

    I'm trying to execute remote commands using powershell and trying to load the commands output to the database. I'm able to load the data to database successffully but the data is loading as a paragraph format which is not readable. Could someone suggest any solution for this ?

    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------>

    Code used to insert/update the sqlserver table:

    $connString = "Server="+$server+";Database="+$db+";Integrated Security=sspi"
    $sqlConnect = New-Object System.Data.SqlClient.SqlConnection $connString
    $sqlConnect.Open()
    $sqlcmd = "Update table01 set log ='"+$formatted_output+"' where server=servername"
    $sqlCommand = $sqlConnect.CreateCommand()
    $sqlCommand.CommandText = $sqlcmd
    $sqlCommand.ExecuteNonQuery()  
    $sqlConnect.Close()


    But when i try to insert this variable data to sql server it is saving the data as paragraph format. I would like to retain the carriage returns when inserted to the database. Could someone guide me achieving this?

    thank you.


    • Edited by dvsrk Friday, July 21, 2017 5:48 PM added code
    Friday, July 21, 2017 5:41 PM

Answers

  • Because that is how SMSS works.  It does not provide formatting.

    Do this:

    $splat =@{
    	Query = 'select varchar from TestText'
     	Database = 'localtest' 
    	ServerInstance = '.\sqlexpress'
    }
    Invoke-SQlCmd @splat | Format-List
    Replace with your values.


    \_(ツ)_/

    • Marked as answer by dvsrk Friday, July 21, 2017 7:55 PM
    Friday, July 21, 2017 7:47 PM

All replies

  • The database will store whatever you send it.  SQLServer knows nothing about "paragraph" format.

    The RegEx is wrong for removing breaks.

    ($output1|out-String) -replace '\r\n'

    What you are asking is rather vague.  Why would you want to replace all line breaks?


    \_(ツ)_/

    Friday, July 21, 2017 5:55 PM
  • Thank you for your response.

    The regex expressions are something I was just trying out to make it work :) so, the output after i inserted to the database is as below,

    But i would like to retain the line breaks. so, if i copy that to a notepad it will be easy to read. 

    <------starting batch-------> Volume in drive C has no label. Volume Serial Number is 0CE1-E926 Directory of C:\temp06/26/2017  03:17 PM    <DIR>          .06/26/2017  03:17 PM    <DIR>          ..08/10/2016  09:07 PM    <DIR>          Clt-Inst06/07/2016  04:31
     PM        56,406,016 splunkforwarder-6.4.1-debde650d26e-x64-release.msi06/26/2017  02:44 PM        35,922,892 Windows6.0-KB968930-x64.msu               2 File(s)     92,328,908 bytes               3 Dir(s)  418,917,388,288 bytes free<servername><------ending bat
    ch------>
    Friday, July 21, 2017 6:15 PM
  • Then just do this:

    $formatted_output = $output | Out-String


    \_(ツ)_/

    Friday, July 21, 2017 6:17 PM
  • It didn't work ... just tried.

    Does the data type of the column in database matters? The column is now defined as varchar type, not sure if changing it to text type will work. let me give it a try.

    Friday, July 21, 2017 6:31 PM
  • changing text type didn't work either...
    Friday, July 21, 2017 6:38 PM
  • The varchar type will store all text and line breaks. 

    How are you trying to display the field?  There is no default program for a database field.

    Perhaps you are trying to store Unicode. In that case you need to use "nvarchar" as the type.  You can also use the column type "text" for free text such as "notepad" files..

    Here is some background on using Unicode in SQS: https://stackoverflow.com/questions/612430/when-must-we-use-nvarchar-nchar-instead-of-varchar-char-in-sql-server

    Also note that line breaks displayed I a browser will not show or work unless you enclose the text in "<pre> ... </pre>"  tags.


    \_(ツ)_/

    Friday, July 21, 2017 6:48 PM
  • simply running the tsql command (select) from sql server management studio and copying the content to notepad. 

    select log from table01 where server='servername'

    Friday, July 21, 2017 7:03 PM
  • What is it that you are storing in "log"?


    \_(ツ)_/

    Friday, July 21, 2017 7:05 PM
  • $formatted_output variable data is stored. I have added the PS code in my original thread showing how i'm doing it. added same below,

    $connString = "Server="+$server+";Database="+$db+";Integrated Security=sspi"
    $sqlConnect = New-Object System.Data.SqlClient.SqlConnection $connString
    $sqlConnect.Open()
    $sqlcmd = "Update table01 set log ='"+$formatted_output+"' where server=servername"
    $sqlCommand = $sqlConnect.CreateCommand()
    $sqlCommand.CommandText = $sqlcmd
    $sqlCommand.ExecuteNonQuery()  
    $sqlConnect.Close()

    Friday, July 21, 2017 7:12 PM
  • I stored your example in two fields - text and varchar.

    Here is the result of returning a datatable with "Invoke-SQlCmd"

    PS C:\temp> $dt.Rows[0].varchar
    <------starting batch------->
     Volume in drive C is Windows
     Volume Serial Number is F89A-041B
    
     Directory of C:\temp
    
    07/17/2017  04:53 PM    <DIR>          .
    07/17/2017  04:53 PM    <DIR>          ..
    05/06/2017  05:27 AM        90,699,643 1009715_05-07-2017.pdf
    05/26/2017  01:42 PM       108,035,163 1009782_05-28-2017.pdf
    03/29/2017  05:39 AM                26 AuditPolicy.csv
    05/06/2017  06:13 AM        36,991,715 B_1025711_792c92c87634.pdf
    05/19/2017  06:35 PM               100 InitJsonInSvc.dat
    05/19/2017  06:35 PM               100 InitJsonOutSvc.dat
    06/13/2017  04:30 PM             1,910 shares.htm
    04/19/2017  05:20 PM        19,992,576 sys.evtx
    07/01/2017  10:34 PM               700 temp.txt
                   9 File(s)    255,721,933 bytes
                   2 Dir(s)  118,289,424,384 bytes free
    ALPHA
    <------ending batch------>
    PS C:\temp>


    \_(ツ)_/

    Friday, July 21, 2017 7:14 PM
  • finally, identified the problem. it is the issue with 2008 SQL Server Management Studio. When i run the same select query on 2012 / 2014 SSMS i'm able to copy the CR\LFs to the notepad but the same doesn't work in 2008 SSMS. Looks like from SQL 2016 onward they have introduced "Retain CR/LF on copy or save" option in SSMS to address this. 

    I was breaking my head on this :)

    Anyways thank you for your continuous help on this.

    Friday, July 21, 2017 7:26 PM
  • It works fine for me.


    \_(ツ)_/

    Friday, July 21, 2017 7:26 PM
  • using SQL Server 2008 SSMS ?
    Friday, July 21, 2017 7:27 PM
  • using SQL Server 2008 SSMS ?

    What does that mean?  SSMS is a tool and not a database.

    What question does this answer?


    \_(ツ)_/

    Friday, July 21, 2017 7:30 PM
  • Ok, let me clarify this...

    Yes, there is no issue with the powershell or either saving the data to the database. The data is saving to the database as expected which contains carriage returns. 

    I'm using sql server 2008 SSMS tool to query the database & read this data. so the SSMS is not displaying the carriage return values when i query the database. if you ask me why? 'no idea'.

    And again with newer SSMS releases of Microsoft that are 2012, 2014 & 2016 it works fine. 

    Friday, July 21, 2017 7:39 PM
  • Because that is how SMSS works.  It does not provide formatting.

    Do this:

    $splat =@{
    	Query = 'select varchar from TestText'
     	Database = 'localtest' 
    	ServerInstance = '.\sqlexpress'
    }
    Invoke-SQlCmd @splat | Format-List
    Replace with your values.


    \_(ツ)_/

    • Marked as answer by dvsrk Friday, July 21, 2017 7:55 PM
    Friday, July 21, 2017 7:47 PM
  • Yes, got it. Thank you :)
    Friday, July 21, 2017 7:55 PM