Pass objects from loop to next

I’m trying to pass object values from one foreach to the next. The first loop, $rgResults, returns multiple rows which need to be passed to the next loop in order to return multiple databases on different servers.

Code:

$rgResults = foreach ($rg in $rgs) {

$servers = Get-AzSqlServer -ResourceGroupName $rg -ErrorAction SilentlyContinue | Select-Object -ExpandProperty ServerName

foreach ($server in $servers) {

[PSCustomObject]@{

 ResourceGroup = $rg

 Server = $server

 } # End object creation

 } # end server loop

 } # end resource group loop

$dbs = $rgResults

$dbResults = @()

foreach($db in $dbs) {

$dbs = Get-AzSqlDatabase -ServerName $dbs.Server -ResourceGroupName $dbs.ResourceGroup

$obj = New-Object -TypeName psobject

Add-Member -InputObject $obj -MemberType NoteProperty -Name ResourceGroup -Value $db.ResourceGroupName

Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $db.ServerName

Add-Member -InputObject $obj -MemberType NoteProperty -Name Database -Value $db.DatabaseName

Add-Member -InputObject $obj -MemberType NoteProperty -Name CreationDate -Value $db.CreationDate

$dbResults += $obj

 } # EndForEach

$dbResults | Format-Table -AutoSize 

Error:

Get-AzSqlDatabase : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'ServerName'. Specified method is not 

supported.

At line:3 char:50

+ $dbs = Get-AzSqlDatabase -ServerName $dbs.Server -Resourc ...

+ ~~~~~~~~~~~

+ CategoryInfo : InvalidArgument: (:) [Get-AzSqlDatabase], ParameterBindingException

+ FullyQualifiedErrorId : CannotConvertArgument,Microsoft.Azure.Commands.Sql.Database.Cmdlet.GetAzureSqlDatabase

By the way, I am indenting the code. Unsure why it’s lining it up.


        

One thing I notice right away is you’re setting $dbs again inside the loop of $dbs items.

Good catch, thx! I made the change but it’s still failing.

Updated code:

$dbResults = @()
foreach($db in $dbs) {
$dbsobj = Get-AzSqlDatabase -ServerName $dbs.Server -ResourceGroupName $dbs.ResourceGroup
$obj = New-Object -TypeName psobject
Add-Member -InputObject $obj -MemberType NoteProperty -Name ResourceGroup -Value $db.ResourceGroupName
Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $db.ServerName
Add-Member -InputObject $obj -MemberType NoteProperty -Name Database -Value $db.DatabaseName
Add-Member -InputObject $obj -MemberType NoteProperty -Name CreationDate -Value $db.CreationDate
$dbResults += $obj
} # EndForEach

$dbResults | Format-Table -AutoSize

Same error as before.

The ‘-ServerName’ parameter for Get-AzSqlDatabase cmdlet only accepts (1) string not multiple strings. You can use a loop.

$server = 
foreach ($r in $rg) {
    $sn = Get-AzSqlServer -ResourceGroupName $r -ErrorAction SilentlyContinue | 
    Select-Object -ExpandProperty ServerName
    [PSCustomObject]@{ResourceGroupName = $r ; ServerName = $sn}
}

foreach ($s in $server){
   $db = Get-AzSqlDatabase -ServerName $s.ServerName -ResourceGroupName $s.ResourceGroupName
   [PSCustomObject]@{
        ResourceGroupName = $db.ResourceGroupName
        ServerName = $db.ServerName
        DatabaseName = $db.DatabaseName
        CreationDate = $db.CreationDate
    }
}

It’s working to a degree, but the output is grouped and mixed in with warning messages:

[pre]

{use2–rg, use2–rg {datalake, datalake} {datalake, master} {3/3/2020 8:20:09 PM, 3/3/20…

{use2-i2b2-rg, use2-i2b2-rg} {i2sqlserver, i2sqlserver} {i2b2DB, master} {3/30/2020 1:53:36 PM, 3/30/…

Get-AzSqlDatabase : Cannot validate argument on parameter ‘ServerName’. The argument is null or empty. Provide an argument that is not null or empty, and then try the

command again.

At line:9 char:49

  • $db = Get-AzSqlDatabase -ServerName $s.ServerName -Resour …

  • 
    
  • CategoryInfo : InvalidData: (:slight_smile: [Get-AzSqlDatabase], ParameterBindingValidationException

  • FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.Azure.Commands.Sql.Database.Cmdlet.GetAzureSqlDatabase

[/pre]