Collecting data from multiple SQL Servers

Question

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

}

Micky Balladelli · Accepted Answer · 2014-11-30 18:48:07Z

1

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

}

edited Nov 30, 2014 at 18:48

answered Nov 30, 2014 at 18:41

Micky Balladelli

10k2 gold badges36 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ansgar Wiechers · Accepted Answer · 2014-11-30 18:41:30Z

1

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
}

answered Nov 30, 2014 at 18:41

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

1 Comment

Micky Balladelli Over a year ago

I like this solution even better.

Cookie Monster · Accepted Answer · 2014-11-30 21:00:15Z

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!

Collectives™ on Stack Overflow

Collecting data from multiple SQL Servers

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related