Rather than issuing a query per instance via SQL Server Management Studio, we are trying to create a powershell script to query multiple instances by going through a loop referencing a text file where it has let's say 100 SQL Server instances. Go through each SQL Server instance, issue a query, and export to CSV.

The below is the powershell script we currently have:

$ServerInstance = "C:\Users\<NAME>\Documents\InstanceList.txt"

foreach ($i in $ServerInstance)

$sql = "SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    RTRIM(loginame) as LoginName,
       RTRIM(Hostname) As HostName, Login_Time,Program_name
WHERE --DB_NAME(dbid) = 'genesys_wfm' and 
    dbid > 5
       --and HostName = 'xxxx'
       and loginame not in ('NT AUTHORITY\SYSTEM','ACE','domain\xxxx')
    dbid, loginame,Hostname, Login_Time,Program_name
       order by Login_Time desc;"

Invoke-Sqlcmd -ServerInstance $i -Query $sql -ConnectionTimeout 60 -QueryTimeout 99999

The below is the InstanceList.txt:

servername\instance name1
servername2\instance name2

and so forth.

  • 3
    Hi Dennis--it seems that you are writing an example of how to do some, but not asking a question. If that is the case, you need to rewrite it as a question ("How do you run a SQL command on several servers in a list") and the post your script as an answer. Also, I think the first line needs to be $ServerInstance = get-content "C:\Users\Documents\InstanceList.txt" Commented Feb 25, 2020 at 1:16
  • Thanks alroc, the example I provided is in the making, but not quite working. I was looking for another approach, perhaps what I've used wasn't the most fitting. But yes, it should be a question.
    – Dennis M.
    Commented Feb 25, 2020 at 17:45

2 Answers 2


The easiest way to do this (read: least amount of additional code you need to write) is to use Invoke-DbaQuery from the dbatools PowerShell module.

Remove the foreach loop - this function will handle it for you. Pass the list of instances into Invoke-DbaQuery and include the -AppendServerInstance switch, and all the instances will be queried and the name of each included in the output - just like when you do a multi-instance query in SSMS. You'll get one object back which is a collection of the results of the query against each instance.

$InstanceList = get-content "C:\Users\<NAME>\Documents\InstanceList.txt";
Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -QueryTimeout 99999 -AppendServerInstance

Since you don't mention script performance/elapsed time being an issue, I intentionally did not mention running multiple queries in parallel and then merging the results.


You should be able to use this script here to accomplish what you need: https://sql-box.com/2012/10/03/how-to-run-a-sql-query-on-list-of-servers-and-save-output-to-excel-spreadsheet-using-power-shell/

  • Thank you! I actually saw this post during my research, the difference is that my serverlist.txt contained the servers, e.g. server.domain.com, so I replaced it with the actual instance name e.g. server.domain.com\instance_name, and it worked.
    – Dennis M.
    Commented Feb 25, 2020 at 0:53
  • The only thing I need to include now is the hostname from the instance itself to distinguish where the instance is coming from once it gets exported to csv or excel.
    – Dennis M.
    Commented Feb 25, 2020 at 0:53
  • Hi Dennis, an easy way to accomplish that without needing to adjust your PowerShell query is adding "CONVERT(sysname, SERVERPROPERTY('servername'))" to your select statement. That adds the server and instance from the SQL statement to your results. Commented Feb 25, 2020 at 1:59
  • 1
    Gotcha! it works like a charm, had to clean it up as certain columns are not needed to be a part of the group by
    – Dennis M.
    Commented Feb 26, 2020 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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