Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

Question

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
FROM
    sys.sysprocesses
WHERE --DB_NAME(dbid) = 'genesys_wfm' and 
    dbid > 5
       --and HostName = 'xxxx'
       and loginame not in ('NT AUTHORITY\SYSTEM','ACE','domain\xxxx')
GROUP BY 
    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.

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" — Tony Hinkle
– Tony Hinkle, 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.
– Dennis M., Commented Feb 25, 2020 at 17:45

alroc · Accepted Answer · 2020-02-25 01:45:12Z

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.

Select 'DBA' · Accepted Answer · 2020-02-24 23:57:21Z

1

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/

edited Feb 24, 2020 at 23:57

answered Feb 24, 2020 at 23:49

Select 'DBA'

1652 silver badges11 bronze badges

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.
– Dennis M.

2020-02-25 00:53:00 +00:00
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.
– Dennis M.

2020-02-25 00:53:56 +00:00
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.

Select 'DBA'
– Select 'DBA'

2020-02-25 01:59:43 +00:00
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.
– Dennis M.

2020-02-26 00:53:39 +00:00
Commented Feb 26, 2020 at 0:53

Add a comment |

Stack Exchange Network

Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

2 Answers 2

Your Answer

Hot Network Questions

Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions