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
References: Pavan's Blog
