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

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}
  • That can work on long term runs but for a bit more dynamic no still, that helps in some scenarios. – Enrique Jul 25 '17 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 Jul 26 '17 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 Jul 26 '17 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 Jul 26 '17 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 Jul 27 '17 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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