Add header to multi server or instance query (powershell)

Enrique yesterday at 07:35. 1 answers, 0 views
sql-server powershell

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

1 Answers


Scott Hodgin 07/25/2017.

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}

HighResolutionMusic.com - Download Hi-Res Songs

1 The Chainsmokers

Beach House flac

The Chainsmokers. 2018. Writer: Andrew Taggart.
2 (G)I-DLE

POP/STARS flac

(G)I-DLE. 2018. Writer: Riot Music Team;Harloe.
3 Anne-Marie

Rewrite The Stars flac

Anne-Marie. 2018. Writer: Benj Pasek;Justin Paul.
4 Ariana Grande

​Thank U, Next flac

Ariana Grande. 2018. Writer: Crazy Mike;Scootie;Victoria Monét;Tayla Parx;TBHits;Ariana Grande.
5 Diplo

Close To Me flac

Diplo. 2018. Writer: Ellie Goulding;Savan Kotecha;Peter Svensson;Ilya;Swae Lee;Diplo.
6 BTS

Waste It On Me flac

BTS. 2018. Writer: Steve Aoki;Jeff Halavacs;Ryan Ogren;Michael Gazzo;Nate Cyphert;Sean Foreman;RM.
7 Clean Bandit

Baby flac

Clean Bandit. 2018. Writer: Jack Patterson;Kamille;Jason Evigan;Matthew Knott;Marina;Luis Fonsi.
8 Imagine Dragons

Bad Liar flac

Imagine Dragons. 2018. Writer: Jorgen Odegard;Daniel Platzman;Ben McKee;Wayne Sermon;Aja Volkman;Dan Reynolds.
9 BlackPink

Kiss And Make Up flac

BlackPink. 2018. Writer: Soke;Kny Factory;Billboard;Chelcee Grimes;Teddy Park;Marc Vincent;Dua Lipa.
10 Nicki Minaj

No Candle No Light flac

Nicki Minaj. 2018. Writer: Denisia “Blu June” Andrews;Kathryn Ostenberg;Brittany "Chi" Coney;Brian Lee;TJ Routon;Tushar Apte;ZAYN;Nicki Minaj.
11 Rita Ora

Cashmere flac

Rita Ora. 2018. Writer: Sean Douglas;Lindy Robbins.
12 Backstreet Boys

Chances flac

Backstreet Boys. 2018.
13 Brooks

Limbo flac

Brooks. 2018.
14 Rita Ora

Velvet Rope flac

Rita Ora. 2018.
15 Fitz And The Tantrums

HandClap flac

Fitz And The Tantrums. 2017. Writer: Fitz And The Tantrums;Eric Frederic;Sam Hollander.
16 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
17 Cher Lloyd

None Of My Business flac

Cher Lloyd. 2018. Writer: ​iamBADDLUCK;Alexsej Vlasenko;Kate Morgan;Henrik Meinke;Jonas Kalisch;Jeremy Chacon.
18 Billie Eilish

When The Party's Over flac

Billie Eilish. 2018. Writer: Billie Eilish;FINNEAS.
19 Kelly Clarkson

Never Enough flac

Kelly Clarkson. 2018. Writer: Benj Pasek;Justin Paul.
20 Lil Pump

Arms Around You flac

Lil Pump. 2018. Writer: Rio Santana;Lil Pump;Edgar Barrera;Mally Mall;Jon Fx;Skrillex;Maluma;Swae Lee;XXXTENTACION.

Language

Popular Tags