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
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();