Problem with foreach loop

Apr 26, 2011 at 3:29 PM

I am working on the below script to gather info on my SQL Server's and for some reason the foreach loop is not working. The write-host shows that the variable $sqlserver is loaded with System.Data.DataRow and not a server name so when the Get-Sqlserver references the variable an error is returned "Exception calling "Connect" with "0" argument(s): "Failed to connect to server System.Data.DataRow."  Any pointers would be appreciated.

#Define Server and Database Repository
$ServerRepository = "test01\mgmt01"
$DatabaseRepository = "PerfDatabase"

#import SQLPSX Modules
import-module -name C:\Users\mharrell\Documents\WindowsPowerShell\Modules\SQLPSX
 
# Get list of SQL servers
$sqlservers = Get-Sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select servername from SQLMachines"
write-host "sql servers loaded"

# For each server listed in $sqlservers
foreach($sqlserver in $sqlservers){
 write-host "loaded $sqlserver for current loop"
 $srv = Get-SqlServer $sqlserver| select name, collation, OSversion, EngineEdition, Edition
 
 $servername = $srv.name
 $collation = $srv.collation
 $windowsversion = $srv.osversion
 $sqlengineedition = $srv.EngineEdition
 $edition = $srv.Edition


 # Write the info for the current sql server
 write-host "$servername,$collation,$windowsversion,$sqlengineEdition,$edition";

}

 

Coordinator
Apr 26, 2011 at 3:57 PM
Keep in mind PowerShell returns objects:

If you run
get-member -inputObject $sqlservers

You'll notice the result is an array of DataRow. Because this is an object of type DataRow you need to reference the servername property of your result set:

foreach ($sqlserver in $sqlservers) {
write-host "loaded $(sqlserver.servername) for current loop"
$srv = Get-SqlServer $(sqlserver.servername) | select name, collation, OSversion, EngineEdition, Edition

I would also recommend enclosing the object.property in $() to ensure it returns a primitive data type unless you are assigning it to a variable.

Sent from my iPad

On Apr 26, 2011, at 10:29 AM, "king6080" <notifications@codeplex.com> wrote:

From: king6080

I am working on the below script to gather info on my SQL Server's and for some reason the foreach loop is not working. The write-host shows that the variable $sqlserver is loaded with System.Data.DataRow and not a server name so when the Get-Sqlserver references the variable an error is returned "Exception calling "Connect" with "0" argument(s): "Failed to connect to server System.Data.DataRow." Any pointers would be appreciated.

#Define Server and Database Repository
$ServerRepository = "test01\mgmt01"
$DatabaseRepository = "PerfDatabase"

#import SQLPSX Modules
import-module -name C:\Users\mharrell\Documents\WindowsPowerShell\Modules\SQLPSX

# Get list of SQL servers
$sqlservers = Get-Sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select servername from SQLMachines"
write-host "sql servers loaded"

# For each server listed in $sqlservers
foreach($sqlserver in $sqlservers){
write-host "loaded $sqlserver for current loop"
$srv = Get-SqlServer $sqlserver| select name, collation, OSversion, EngineEdition, Edition

$servername = $srv.name
$collation = $srv.collation
$windowsversion = $srv.osversion
$sqlengineedition = $srv.EngineEdition
$edition = $srv.Edition


# Write the info for the current sql server
write-host "$servername,$collation,$windowsversion,$sqlengineEdition,$edition";

}

Apr 26, 2011 at 4:27 PM
cmille19 - your explanation made perfect sense and my edited script works. Thanks for your help.


From: [email removed]
To: [email removed]
Date: Tue, 26 Apr 2011 07:57:28 -0700
Subject: Re: Problem with foreach loop [SQLPSX:255304]

From: cmille19
Keep in mind PowerShell returns objects:

If you run
get-member -inputObject $sqlservers

You'll notice the result is an array of DataRow. Because this is an object of type DataRow you need to reference the servername property of your result set:

foreach ($sqlserver in $sqlservers) {
write-host "loaded $(sqlserver.servername) for current loop"
$srv = Get-SqlServer $(sqlserver.servername) | select name, collation, OSversion, EngineEdition, Edition

I would also recommend enclosing the object.property in $() to ensure it returns a primitive data type unless you are assigning it to a variable.

Sent from my iPad

On Apr 26, 2011, at 10:29 AM, "king6080" <notifications@codeplex.com> wrote:

From: king6080
I am working on the below script to gather info on my SQL Server's and for some reason the foreach loop is not working. The write-host shows that the variable $sqlserver is loaded with System.Data.DataRow and not a server name so when the Get-Sqlserver references the variable an error is returned "Exception calling "Connect" with "0" argument(s): "Failed to connect to server System.Data.DataRow." Any pointers would be appreciated.
#Define Server and Database Repository
$ServerRepository = "test01\mgmt01"
$DatabaseRepository = "PerfDatabase"
#import SQLPSX Modules
import-module -name C:\Users\mharrell\Documents\WindowsPowerShell\Modules\SQLPSX

# Get list of SQL servers
$sqlservers = Get-Sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select servername from SQLMachines"
write-host "sql servers loaded"
# For each server listed in $sqlservers
foreach($sqlserver in $sqlservers){
write-host "loaded $sqlserver for current loop"
$srv = Get-SqlServer $sqlserver| select name, collation, OSversion, EngineEdition, Edition

$servername = $srv.name
$collation = $srv.collation
$windowsversion = $srv.osversion
$sqlengineedition = $srv.EngineEdition
$edition = $srv.Edition

# Write the info for the current sql server
write-host "$servername,$collation,$windowsversion,$sqlengineEdition,$edition";
}

Read the full discussion online.
To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com