Executing a SQL query to multiple servers

This recipe executes a pre-defined SQL query to multiple SQL Server instances specified in a text file.

Getting ready

In this recipe, we will connect to multiple SQL Server instances and execute a SQL command against all of them.

Identify the available instances for you to run your query on. Once you have identified all the instances you want to execute the command to, create a text file in C:\Temp called sqlinstances.txt and put each instance name line by line into that file. For example:

KERRIGAN
KERRIGAN\SQL01 

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $instances = Get-content "C:\Temp\sqlinstances.txt"
   $query = "SELECT @@SERVERNAME 'SERVERNAME', @@VERSION 'VERSION'"
   $databasename = "master"
   $instances |
   ForEach-Object {
      $server = New-Object -TypeName...