CODEWITHSUNDEEP
sql-serverpowershell
RaviLobo
RaviLobo

Reputation: 508

Collecting data from multiple SQL Servers

I'm sure there's a simple answer for this. I just don't know, how to do it. I need a way to collect data from multiple sql servers. I'm using the following code to fetch the data. (Not the real code).

The code gets the VERSION info for a set of SQL Servers. I need a way to collect this information in an array or some sort of collection.

Subsequently, after the FOREACH block, once I have the data at one place, I'll work on it. Thank you. 

$servers = 'srv1','srv2','srv3','srv4','srv5'

foreach($i in $servers) {

$sql = "select @@version"

#I need a way to collect the below result set in a variable  cummulatively
#if i just use some variable $res, data gets overwritten in every iteration 

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

}

Upvotes: 0

Views: 750

Answers (3)

Cookie Monster
Cookie Monster

Reputation: 1791

Looks like you found the answer already. Here's another option if you want to avoid Invoke-Sqlcmd.

Invoke-Sqlcmd2 is an old function from Chad Miller, a former Microsoft MVP for SQL, and a few other contributors. Some benefits include simplified parameterized queries, pipeline input, no dependencies on SQLPS, etc.

#Dot source Invoke-Sqlcmd2
. "\\path\to\Invoke-Sqlcmd2.ps1"

#Example query using pipeline input.
#Append server instance adds the instance as a column.
$Servers |
    Invoke-Sqlcmd2 -Query "select @@version as Version" -AppendServerInstance 

<#
    Version                                                       ServerInstance                                                                                      
    -------                                                       --------------                                                                                      
    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) ...   ServerInstance1                                                                                             
    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) ...   ServerInstance2                                                                                             
    Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) ...       ServerInstance3                                                                                            
    Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) ...       ServerInstance4     
#>

Cheers!

Upvotes: 0

Micky Balladelli
Micky Balladelli

Reputation: 9991

Why not using an array?

The result of the Invoke-SQLcmd will be an object that can be added to an array. Like this:

$servers = 'srv1','srv2','srv3','srv4','srv5'

$result = @()
foreach($i in $servers) {

$sql = "select @@version"

#I need a way to collect the below result set in a variable  cummulatively
#if i just use some variable $res, data gets overwritten in every iteration 

$result += Invoke-Sqlcmd -ServerInstance $i  -query $sql   -ConnectionTimeout 60 -QueryTimeout 99999

}

Upvotes: 1

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200233

Something like this should do what you want:

$servers = 'srv1','srv2','srv3','srv4','srv5'

$sql = "select @@version"
$res = foreach($i in $servers) {
  Invoke-Sqlcmd -ServerInstance $i -Query $sql -ConnectionTimeout 60 -QueryTimeout 99999
}

Upvotes: 1

Related Questions