How to copy output of a Select Query from a DataSet to a log file using PowerShell?

Question

I am using the below code to select a value from table present in SQLServer, the code executes successfully and displays output on the Command Prompt of PowerShell which I have assigned to a variable, but when I try adding the output assigned to $MsgBody to the log file it copies System.Data.DataRow to the log file.

How can I add the output to the log file any help would be appreciated.

$scriptPath = $PSScriptRoot
$logFilePath = Join-Path $scriptPath "DemoResults.log"

# If log file exists, then clear its contents 
if (Test-Path $logFilePath) {
    Clear-Content -Path $logFilePath
} 

# It displays the date and time of execution of powershell script in log file.
$log = "Date Of Testing: {0} " -f (Get-Date)
$logString = "Process Started." 
Add-Content -Path $logFilePath -Value $log -Force 
Add-Content -Path $logFilePath -Value $logString -Force
$SQLServer = "AP-PON-SRSTEP\MSSQLSERVER12" #use Server\Instance for named SQL instances! 
$SQLDBName = "SystemDB"

$SqlQuery2 = "Select * from SystemDB.dbo.Version_Solution WHERE Notes ='9.2.7'"
$sa = "srp"
$pass = "Stayout"

$connectionString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;User ID=$sa;Password=$pass";

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-Object System.Data.SqlClient.SqlCommand($SqlQuery2, $connection);
$connection.Open();
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$MsgBody = $DataSet.Tables[0] 
#Displays output in Command shell
$MsgBody

$MsgBody | Add-Content $logFilePath
Get-Content $logFilePath

$connection.Close();

Though I tried using this and it copies output to the file but deletes the other previous output from the log file.

$MsgBody > $logFilePath

Edited part :- After using this >>

$MsgBody >> $logFilePath

It copies the output to the logfile in this format horizontally,

S o l u t i o n             :   i n t e l l   C o m p o n e n t           :   S y s t e m D B  M a j o r                   :   9  M i n o r                   :  2 S e r v i c e P a c k       :   1  H o t f i x                 :   0  I n s t a l l e d D a t e   :   1 2 / 1 2 / 2 0 1 7   6 : 5 7 : 4 8   P M  N o t e s                   :   9 . 2 . 1  R o l l U p R e l e a s e   :   0

which looks ugly, I want it to be copied in this way vertically -

Solution      : intell
Component     : SystemDB
Major         : 9
Minor         : 2
ServicePack   : 1
Hotfix        : 0
InstalledDate : 12/12/2017 6:57:48 PM
Notes         : 9.2.1
RollUpRelease : 0

I answered only the 2nd part of your question as you are not specifying how you want the output to be displayed. — Clijsters
– Clijsters, Commented Dec 13, 2017 at 8:46

Clijsters · Accepted Answer · 2017-12-13 08:40:55Z

1

Though I tried using this and it copies output to the file but deletes the other previous output from the log file.
$MsgBody > $logFilePath 

Taking a look at Get-Help about_redirection tells us:

Operator  Description               Example
--------  ----------------------    ------------------------------
>         Sends output to the       Get-Process > Process.txt
          specified file.

>>        Appends the output to     dir *.ps1 >> Scripts.txt
          the contents of the
          specified file.

[...]

That means, by just replacing your > operator with an double greater than >> your approach should work.

answered Dec 13, 2017 at 8:40

Clijsters

4,3722 gold badges30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SRP Over a year ago

@ Clijsters, It is copying the output horizontally, how can i copy it in sorted order like a table format or something like that. Also can't we use Add-Content to copy it to the log file?

SRP Over a year ago

@ Clijsters, If I use this > it copies the output Vertically the way i want but it overwrites the other output, but when i use this >> it copies the output horizontally which makes it look dirty and ugly to read. How can i copy the output in proper format like in vertical format so that it is easy to read using this >>

Clijsters Over a year ago

As I already commented, please add some example output to your question of how it actually looks like and how you want it to look.

SRP Over a year ago

@ Clijsters, I have edited the question at top please look at it.

Clijsters Over a year ago

Are you sure that your editor not just needs CRLF for line breaks?

|

Jesus A. Sanchez · Accepted Answer · 2017-12-13 20:40:49Z

0

You can try the following very simple modification:

$MsgBody | Add-Content $logFilePath

to

$MsgBody | Out-File -Path $logFilePath -Append -Force

I think that's what you're looking for.

answered Dec 13, 2017 at 20:40

Jesus A. Sanchez

1464 bronze badges

5 Comments

SRP Over a year ago

No, I have tried both the way for $MsgBody | Add-Content $logFilePath, it copies this in log file System.Data.DataRow and when i use this $MsgBody | Out-File -Path $logFilePath -Append -Force it copies the output but overwrites the other output in the log file.

Jesus A. Sanchez Over a year ago

Have you tried giving it format first? ---> $MsgBody | Format-Table -AutoSize | Out-File -Path $logFilePath -Append

SRP Over a year ago

No, I haven't tried it this way, will confirm you with the result once I try it.

SRP Over a year ago

@ Jesus A. Sanchez, Even --> $MsgBody | Format-Table -AutoSize | Out-File -Path $logFilePath -Append this does not copy the output in proper format it also copies it horizontally like the one i have copied in my question above.

Jesus A. Sanchez Over a year ago

Which editor are you using to check the output file? Can you use Notepad++ and see which non-printable characters are the separators of each "line"? The issue could be encoding. Is your database in the same character set as your Windows machine?

jaismeensandhu · Accepted Answer · 2017-12-15 16:26:28Z

0

you can try

$DataSet.Tables[0] | format-list >> abc.txt

it worked for me

edited Dec 15, 2017 at 16:26

answered Dec 15, 2017 at 16:18

jaismeensandhu

591 silver badge7 bronze badges

1 Comment

SRP Over a year ago

@ jaismeensandhu, No even this doesn't give the desired output to me, it also copies the output horizontally in log file just like the one i have asked in question. Although now i am using little lengthy code to copy the output to log file in Vertical format.

SRP · Accepted Answer · 2017-12-18 12:30:16Z

The answer to the above question, which i have found is this though it is little lengthy but it works fine for me.

$scriptPath = $PSScriptRoot
$logFilePath = Join-Path $scriptPath "DemoResults.log"

# If log file exists, then clear its contents 
if (Test-Path $logFilePath) {
    Clear-Content -Path $logFilePath
} 

# It displays the date and time of execution of powershell script in log file.
$log = "Date Of Testing: {0} " -f (Get-Date)
$logString = "Process Started." 
Add-Content -Path $logFilePath -Value $log -Force 
Add-Content -Path $logFilePath -Value $logString -Force
$SQLServer = "AP-PON-SRSTEP\MSSQLSERVER12" #use Server\Instance for named SQL instances! 
$SQLDBName = "SystemDB"

$SqlQuery2 = "Select * from SystemDB.dbo.Version_Solution WHERE Notes ='9.2.1'"
$sa = "srp"
$pass = "Stayout"

$connectionString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;User ID=$sa;Password=$pass";

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-Object System.Data.SqlClient.SqlCommand($SqlQuery2, $connection);
$connection.Open();
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$MsgBody = $DataSet.Tables[0] 
#Displays output in Command shell
$MsgBody

#Instead of this code, I have used the below code to copy output in Vertical Format in log file. 
<#
$MsgBody | Add-Content $logFilePath
Get-Content $logFilePath
#>

#Code to copy the output of select statement to log file.
    $logString="Version_Solution Table in SystemDB"
    add-content -Path $logFilePath -Value $logString -Force
    add-content -Path $logFilePath -Value "`n" -Force

    $MsgBody = $MsgBody | Select-Object Solution, Component, Major, Minor ,ServicePack,Hotfix,InstalledDate,Notes,RollUpRelease
    $Solution=$MsgBody.Solution
    $Component=$MsgBody.Component
    $Major=$MsgBody.Major
    $Minor=$MsgBody.Minor
    $ServicePack=$MsgBody.ServicePack
    $Hotfix=$MsgBody.Hotfix
    $InstalledDate=$MsgBody.InstalledDate
    $Notes=$MsgBody.Notes
    $RollUpRelease=$MsgBody.RollUpRelease

    add-content -Path $LogFilePath -Value "Solution: $Solution" -Force   
    add-content -Path $LogFilePath -Value "Component: $Component" -Force  
    add-content -Path $LogFilePath -Value "Major: $Major" -Force  
    add-content -Path $LogFilePath -Value "Minor: $Minor" -Force  
    add-content -Path $LogFilePath -Value "ServicePack: $ServicePack" -Force  
    add-content -Path $LogFilePath -Value "Hotfix: $Hotfix" -Force  
    add-content -Path $LogFilePath -Value "InstalledDate: $InstalledDate" -Force  
    add-content -Path $LogFilePath -Value "Notes: $Notes" -Force  
    add-content -Path $LogFilePath -Value "RollUpRelease: $RollUpRelease" -Force  
    add-content -Path $logFilePath -Value "`n" -Force

$connection.Close();

Collectives™ on Stack Overflow

How to copy output of a Select Query from a DataSet to a log file using PowerShell?

4 Answers 4

7 Comments

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related