5

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.

  • 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" – Tony Hinkle Feb 25 '20 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. Feb 25 '20 at 17:45
6

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.

  • I'll create another script using Invoke-DbaQuery, I appreciate it. – Dennis M. Feb 26 '20 at 0:54
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/

  • 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. Feb 25 '20 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. Feb 25 '20 at 0:53
  • 1
    An answer that simply refers to a URL and provides no further explanation should be posted as a comment, not an answer. – Tony Hinkle Feb 25 '20 at 1:19
  • 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' Feb 25 '20 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. Feb 26 '20 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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