Pass objects from loop to next

Welcome Forums General PowerShell Q&A Pass objects from loop to next

Viewing 4 reply threads
  • Author
    Posts
    • #220059
      Participant
      Topics: 21
      Replies: 41
      Points: 301
      Rank: Contributor

      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.

      
      
      
      
      • This topic was modified 1 month, 2 weeks ago by anelliaf38.
    • #220092
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

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

    • #220098
      Participant
      Topics: 21
      Replies: 41
      Points: 301
      Rank: Contributor

      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.

    • #220113
      Participant
      Topics: 5
      Replies: 322
      Points: 445
      Helping Hand
      Rank: Contributor

      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
          }
      }
      
    • #220677
      Participant
      Topics: 21
      Replies: 41
      Points: 301
      Rank: Contributor

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

      
      {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: (:) [Get-AzSqlDatabase], ParameterBindingValidationException
      
      + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.Azure.Commands.Sql.Database.Cmdlet.GetAzureSqlDatabase
      
      
      • This reply was modified 1 month, 1 week ago by anelliaf38.
Viewing 4 reply threads
  • You must be logged in to reply to this topic.