How to run SQL Query on Multiple MSSQL servers using windows authentication and save result to excel

Step 1: Create a notepad with list of SQL servers on which you want to execute the SQL statement and save it as SQL_servers.txt on D:\DBA_Scripts location. 

Server names can be as follows in text file.

server1,port_number,
server2,port_number
server2\instance_name2,port_number
.
.
server_n,port_number

Step 2: I have Created a folder called DBA_Scripts in my D: then Copy the below script into another text file and save it as D:\DBA_Scripts\script.ps1 

=======================================================
# Script Runs on servers listed in SQL_servers.txt
# use sqlps.exe to run this script
# In cmd, sqlps.exe D:\DBA_Scripts\script.ps1

$query = " Your SQL Query"

#Path to the excel file to be saved.

$csvFilePath = "D:\DBA_Scripts\queryresults.csv"
$excelFilePath = "D:\DBA_Scripts\queryresults.xls"


$instanceNameList = get-content D:\DBA_Scripts\SQL_servers.txt
$results=@()

foreach($instanceName in $instanceNameList)
{
write-host "Executing query against server: " $instanceName
$results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName
}

# Output to CSV

write-host "Saving Query Results in CSV format..."
$results | export-csv $csvFilePath -NoTypeInformation
# Convert CSV file to Excel
# Reference : http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa

write-host "Converting CSV output to Excel..."

$excel = New-Object -ComObject excel.application
$excel.visible = $False
$excel.displayalerts=$False
$workbook = $excel.Workbooks.Open($csvFilePath)
$workSheet = $workbook.worksheets.Item(1)
$resize = $workSheet.UsedRange
$resize.EntireColumn.AutoFit() | Out-Null
$xlExcel8 = 56
$workbook.SaveAs($excelFilePath,$xlExcel8)
$workbook.Close()
$excel.quit()
$excel = $null

write-host "Results are saved in Excel file: " $excelFilePath
=========================================================

Step3: Copy and paste the SQL Query into the above script where it says like this 

$query = ” Your SQL Query "  

Step 4: Open CMD and run the script like this

Sqlps.exe D:\DBA_scripts\script.ps1

The results from the SQL Query from each server will be combined and saved as queryresults.xls into your DBA_scripts folder.

References: Pavan's Blog