0

I have the following PowerShell script I am looking to add a header to this results without having a duplicate per each iteration but I am not familiar enough with Powershell if anyone can point me in the right direction it would be helpful.

$servers = 'E:\DBA\servers.txt' $outfile = 'E:\DBA\out.csv' $queryfile = 'E:\DBA\query.sql'

Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File $outFile -Force} Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

The error is enter image description here

Improve this question

1 Answer 1

Reset to default
2

If you just want to hard-code the header, you can use the Powershell New-Item cmdlet. I used the -Force parameter to initialize the outfile with the single header row and the rest of your script uses -Append to add the detail information.

New-Item $outfile -value "Header,Header,Header,Header`n" -Force
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

If you want dynamic columns generated based on the actual query, you can run an initial Get-Content using -First 1 (to only get the first server) and limit the output to the first row only by using -First 1. Then, follow up with your usual Powershell script.

Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File $outFile -Force}
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}
Improve this answer
10
  • That can work on long term runs but for a bit more dynamic no still, that helps in some scenarios.
    – Enrique
    Commented Jul 25, 2017 at 18:31
  • @Enrique - Just letting you know that I updated my answer, which should allow you to retrieve the header information from the actual query instead of hard-coding the header information.
    – Scott Hodgin - Retired
    Commented Jul 26, 2017 at 14:36
  • Thanks, Scott I was going to work today on this, I will be sure to check both of your answer and thanks.
    – Enrique
    Commented Jul 26, 2017 at 16:26
  • Thanks Scoth it work but I am getting a weird error even when it works correctly and I am not that good to see the error. <code>At D:\DBAPoSH\Mail\QueryExecuteMultiServer.ps1:11 char:49 + Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryf ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand</code>
    – Enrique
    Commented Jul 26, 2017 at 22:57
  • Strange - I didn't receive any errors during my tests - can you update your post to include your entire Powershell script? Perhaps you have something extra. You might also try to update the file that $servers points to and include only ONE server to facilitate testing.
    – Scott Hodgin - Retired
    Commented Jul 27, 2017 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.