Add header to multi server or instance query (powershell)

Question

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

Answer 1

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}

• Enrique: That can work on long term runs but for a bit more dynamic no still, that helps in some scenarios.
• Scott Hodgin - Retired: @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.
• Enrique: Thanks, Scott I was going to work today on this, I will be sure to check both of your answer and thanks.
• Enrique: 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>
• Scott Hodgin - Retired: 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.