Pass objects from loop to next

PowerShell Help
1

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.


            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              2
              
            

            

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

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              3
              
            

            

              
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.

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              4
              
            

            

              
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
    }
}


            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              5
              
            

            

              
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]